Create event for dynamic control (textbox/combobox)

[AutumnSnow]

hi.. i have been googling for long time, but i couldn't find how to create event for dynamic controls.
i have a form with a frame, when the form load, it will check how many columns they have in database for a particular table and all the fields will created dynamically in the frame. All this work fine.

Now, i want to do client-side validation when client change the value/ when client type in new value in combobox. Anyone can provide a simple code demonstrate how it works(as in how to create event for dynamic created control)?

*the control will name according to the column name in database
eg.: txtName, txtAddress, txtBatchNo, etc


Thank you in advance!

[honeybee]

If you are using only textboxes to dynamically create and show on the form, you could use a control array. This way you can have the first textbox on the form at design time, and add to the array at runtime to create as many textboxes as you want. All of them would share the same event handler. This would be the simplest method.

If you want to create single elements at runtime, take a look at declaring your controls using WithEvents. For e.g. Dim WithEvents txt As Textbox will allow you to associate events with the txt control.

.

[AutumnSnow]

If you are using only textboxes to dynamically create and show on the form, you could use a control array. This way you can have the first textbox on the form at design time, and add to the array at runtime to create as many textboxes as you want. All of them would share the same event handler. This would be the simplest method.

If you want to create single elements at runtime, take a look at declaring your controls using WithEvents. For e.g. Dim WithEvents txt As Textbox will allow you to associate events with the txt control.

.

hi honeybee,
thanks for your reply.
the 2nd option only allow for 1 dynamic control? if i have more than one, can i use this method?

i couldn't use the first one, because the name will be dynamic

[honeybee]

For dynamic names, you could also use the Tag property of the control.

For the WithEvents solution, if you have more than one, I guess you would have to create an array of them, which is something I have never done.

The safest bet here would be to go with control arrays and handle the name issue by using tags.

.

[Doogle]

i couldn't use the first one, because the name will be dynamic

The name doesn't have to be 'dynamic' you can keep track of the column names using the Tag property

Option Explicit
'
' Assumes txtData is drawn on the From with Index property set to 0
' ComboBox named cmbTables is drawn on the Form
' Command Button named Command is drawn on the form
'
Private con As ADODB.Connection
Private rs As ADODB.Recordset

Private Sub Command_Click()
If cmbTables.ListIndex <> 0 Then
    Call UnloadTxt
    Call PopulateTxt(cmbTables.List(cmbTables.ListIndex))
End If
End Sub

Private Sub Form_Load()
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
'
' Replace c:\db7.mdb with the Path and name of your Database
'
con.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db7.mdb;"
Set rs = con.OpenSchema(adSchemaTables)
cmbTables.Clear
cmbTables.AddItem "Tables"
Do While Not rs.EOF
    cmbTables.AddItem rs.Fields("TABLE_NAME")
    rs.MoveNext
Loop
rs.Close
cmbTables.Text = cmbTables.List(0)
End Sub

Private Sub UnloadTxt()
Dim intI As Integer
If txtData.UBound > 0 Then
    For intI = 1 To txtData.UBound
        Unload txtData(intI)
    Next intI
End If
End Sub

Private Sub PopulateTxt(strTable As String)
Dim intI As Integer
Dim strSQL As String
strSQL = "SELECT * FROM " & strTable
On Error GoTo err
rs.Open strSQL, con, adOpenStatic, adLockOptimistic
If Not (rs.BOF And rs.EOF) Then
    For intI = 0 To rs.Fields.Count - 1
        If intI <> 0 Then
            Load txtData(intI)
            txtData(intI).Top = txtData(intI - 1).Top + txtData(intI - 1).Height
            txtData(intI).Left = txtData(intI - 1).Left
            txtData(intI).Visible = True
        End If
        txtData(intI).Tag = rs.Fields(intI).Name
        txtData(intI).Text = rs.Fields(intI).Value
    Next intI
    rs.Close
End If
err:
End Sub

Private Sub txtData_KeyPress(Index As Integer, KeyAscii As Integer)
Debug.Print "User has typed something into the " & txtData(Index).Tag & " TextBox"
End Sub

Run the above having changed the database path and name. Click on one of the tables listed in the ComboBox (except 'Tables') and then click on the Command Button. Textboxes for each of the fields in the table will be displayed showing the first record (assuming the Table is not empty). Typing anything into one of the textboxes will produce a message in the Immediate Windows identifying which column of the Table that field represents.

(Some Access Tables cannot be accessed which is why there's an On Error Goto Err - to avoid the program aborting)

I see that HoneyBee has beaten me to it !