Mengeset properties MaxLength TextBox secara otomatis

Salah satu cara untuk memvalidasi inputan berupa TextBox dari user adalah dengan membatasi jumlah input sesuai dengan lebar field/kolom yang kita definisikan pada saat pembuatan struktur tabel.

Tentunya akan sedikit membosankan dan melelahkan jika kita akan membuat tampilan seperti form-form berikut :

kemudian mengeset properties MaxLength secara manual tentu pekerjaan yang berisiko tinggi ha ha ha , belum lagi jika terjadi perubahan lebar field/kolom pada struktur tabel.

Untuk mengimplementasikan teori diatas, maka kita membutuhkan 2 buah kelas/class yang saya beri nama clsAttribut dan clsAutoMaxLength.

Saya juga pernah menulis artikel sederhana bagai mana membuat kelas/class di Visual Basic 6.

Berikut adalah kode lengkap untuk clsAttribut

Option Explicit

Private mFieldName  As String
Private mObjTextBox As TextBox

Public Property Let fieldName(ByVal vData As String)
    mFieldName = vData
End Property
Public Property Get fieldName() As String
    fieldName = mFieldName
End Property

Public Property Let objTextBox(ByVal vData As TextBox)
    Set mObjTextBox = vData
End Property
Public Property Get objTextBox() As TextBox
    Set objTextBox = mObjTextBox
End Property

dan dibawah ini adalah kode clsAutoMaxLength

Option Explicit

Private mFormName   As Form
Private mCol        As Collection
Private mTableName  As String

Public Property Let tableName(ByVal vData As String)
    mTableName = vData
End Property
Public Property Get tableName() As String
    tableName = mTableName
End Property

Public Property Let formName(ByVal vData As Form)
    Set mFormName = vData
End Property
Public Property Get formName() As Form
    Set formName = mFormName
End Property

Private Property Get getColumns(ByVal indexKey As Long) As clsAttribut
    Set getColumns = mCol(indexKey)
End Property

Private Function isFieldExists(ByVal fieldName As String, ByRef textBoxName As String) As Boolean
    Dim objAttributs    As clsAttribut
    Dim i               As Integer

    Set objAttributs = New clsAttribut
    For i = 1 To mCol.Count
        Set objAttributs = getColumns(i)
        If LCase(objAttributs.fieldName) = LCase(fieldName) Then
            textBoxName = objAttributs.objTextBox.Name
            isFieldExists = True

            Exit For
        End If
    Next i
    Set objAttributs = Nothing
End Function

Private Function isTableExists() As Boolean
    Dim rs      As ADODB.Recordset
    Dim strSql  As String

    On Error GoTo errHandle

    strSql = "SELECT * FROM " & tableName & ""
    Set rs = New ADODB.Recordset
    rs.Open strSql, conn, adOpenForwardOnly, adLockReadOnly
    rs.Close
    Set rs = Nothing

    isTableExists = True
    Exit Function
errHandle:
    isTableExists = False
End Function

Public Sub addAttributs(ByVal fieldName As String, ByVal objTextBox As TextBox)
    Dim objNewMember As clsAttribut

    Set objNewMember = New clsAttribut
    objNewMember.fieldName = fieldName
    objNewMember.objTextBox = objTextBox

    mCol.Add objNewMember
    Set objNewMember = Nothing
End Sub

Public Sub autoMaxLength()
    Dim rs              As ADODB.Recordset
    Dim ctl             As Object

    Dim strSql          As String
    Dim textBoxName     As String

    Dim i               As Integer

    If isTableExists Then
        strSql = "SELECT * FROM " & tableName & ""
        Set rs = New ADODB.Recordset
        rs.Open strSql, conn, adOpenForwardOnly, adLockReadOnly
        For i = 0 To rs.Fields.Count - 1 'perulangan sejumlah field
            'bandingkan field yg diinput manual dg yang di table
            If isFieldExists(rs.Fields(i).Name, textBoxName) Then
                'ulang sebanyak komponen yg ada di form
                For Each ctl In formName.Controls
                    'bandingkan textbox yg ada di form dg yg diinput manual
                    If TypeName(ctl) = "TextBox" And ctl.Name = textBoxName Then
                        ctl.MaxLength = rs.Fields(i).DefinedSize 'set MaxLength
                        Exit For
                    End If
                Next
            End If
        Next i
        rs.Close
        Set rs = Nothing

    Else
        MsgBox "Nama tabel salah", vbExclamation, "Peringatan"
    End If
End Sub

Private Sub Class_Initialize()
    Set mCol = New Collection
End Sub

Private Sub Class_Terminate()
    Set mCol = Nothing
End Sub

Contoh penggunaan ke dua kelas diatas sangatlah gampang cukup kita tambahkan 1 Form dan beberapa komponen TextBox, selanjutnya pada event Form_Load ketikkan kode berikut :

Private Sub Form_Load()
    Dim obj As clsAutoMaxLength

    Set obj = New clsAutoMaxLength
    With obj
        .formName = Me
        .tableName = "siswa"

        .addAttributs "nis", txtNIS
        .addAttributs "nama", txtNama
        .addAttributs "alamat", txtAlamat
        .addAttributs "telepon", txtTelepon

        Call .autoMaxLength
    End With
    Set obj = Nothing
End Sub

Jalankan program dan properties MaxLength objek TextBox otomatis menyesuaikan dengan lebar field pada tabel.

Dan jangan lupa untuk mencoba source diatas program kita harus sudah terhubung ke database.

Selamat MENCOBA