Dynamically Create Controls

This FAQ is mean to help as a reference. Many people seem to be asking the same questions over and over. 2 of them being How do I dynamically creat controls, and how do I use ADO. I created this FAQ to reference back to them.

This Code sample covers both of those questions, at least as far as getting started. I therefore left out any error handling to help keep it simple and easy to read.

If you have any questions please reply to me so that I can update the FAQ.
[tt]
[color green]' I created an Access DataBase called DB1.MDB.
' These are the tables and columns

' Table - Contacts
' Address adVarWChar
' City adVarWChar
' ContactID adInteger
' Country adVarWChar
' EmailName adVarWChar
' FaxNumber adVarWChar
' FirstName adVarWChar
' HomePhone adVarWChar
' LastName adVarWChar
' MobilePhone adVarWChar
' PostalCode adVarWChar
' StateOrProvince adVarWChar
' WorkExtension adVarWChar
' WorkPhone adVarWChar

' Create a New EXE Project and add to it
'
' Reference the Project to:
' Microsoft ActiveX Data Objects 2.x
'
' ControlType Name Caption
' --------------- --------------- ---------------
' Form Form1 Empty
' Form Form2 Empty
' Label lblFullName Empty
' Label lblRecNumber Empty
' Command Button cmdAdd &Add Record
' Command Button cmdExit E&xit
' Command Button cmdFirst <<
' Command Button cmdLast >>
' Command Button cmdNext >
' Command Button cmdPrev <
' Command Button cmdShowDetails &Show Details
'
' This is what my form Looks like.
' I have no way to post the image picture.
' _________________________________________
' |________________________________________X|
' | _____________________________________ |
' | [ lblFullName ] |
' | [_____________________________________] |
' | |
' | |============| |
' | |Show Details| |
' | |============| |
' | |
' | |============| |
' | | Add Record | |
' | |============| |
' | |
' | |============| |
' | | Exit | |
' | |============| |
' | |
' | |
' | |====| |====| _________ |====| |====| |
' | | << | | < | [lblRecNum] | > | | >> | |
' | |====| |====| [_________] |====| |====| |
' |_________________________________________|

' Paste the below into the General Declarations
' section of Form1[/color]

Option Explicit

[color green]' Record Variables[/color]
Public cn As ADODB.Connection
Public rs As ADODB.Recordset

Private Sub cmdExit_Click()
[color green]' Clean Up any loose ends[/color]
cn.Close
Set cn = Nothing
Set rs = Nothing

[color green]' Close the program[/color]
End

End Sub

Private Sub cmdShowDetails_Click()
[color green]' Call the FormAsEdit function in Form2
' We pass it rs, as it will not be able to get
' access to variables declared in Form1[/color]
Form2.FormAsEdit rs

End Sub

Private Sub cmdAdd_Click()
[color green]' Call the FormAsAdd function in Form2
' We pass it rs, as it will not be able to get
' access to variables declared in Form1[/color]
Form2.FormAsAdd rs

End Sub

Private Sub Form_Load()
[color green]' cn is our ADODB database connection.[/color]
Set cn = New ADODB.Connection
[color green]' rs is the actual Recordset object, it will use
' the cn database connection[/color]
Set rs = New ADODB.Recordset

[color green]' Here we set cn as a New Connection. By doing
' this we are creating the connection object.[/color]
Set cn = New ADODB.Connection
[color green]' Now we point the connection object to our database.
' App.Path will return the directory from which your
' program is running.[/color]
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & "\DB1.MDB"
[color green]' Open the connection to the database[/color]
cn.Open

[color green]' Here we set rs as a New Recordset. By doing
' this we are creating the recordset object.[/color]
Set rs = New ADODB.Recordset
[color green]' We set the rs parameters, including the cursor
' location, and record locking type[/color]
With rs
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
[color green]' Here is the statement that will get the actual
' records from the connected database[/color]
.Open "SELECT * FROM Contacts;", cn
.ActiveConnection = cn
End With

[color green]' We will now display the first record on our form
' by re-using the code for moving to the first record[/color]
Call cmdFirst_Click

End Sub

Private Sub cmdFirst_Click()
[color green]' Move to the first record in the database[/color]
rs.MoveFirst
[color green]' Display the Name on the form, this is done in a
' sub so that it is re-usable[/color]
DisplayRecord

End Sub

Private Sub cmdLast_Click()
[color green]' Move to the Last record in the database[/color]
rs.MoveLast
[color green]' Display the Name on the form, this is done in a
' sub so that it is re-usable[/color]
DisplayRecord

End Sub

Private Sub cmdNext_Click()
[color green]' Move to the Next record in the database[/color]
rs.MoveNext
[color green]' If we have Moved past the last record, then
' reset to the last record[/color]
If rs.EOF Then
rs.MoveLast
End If
[color green]' Display the Name on the form, this is done in a
' sub so that it is re-usable[/color]
DisplayRecord

End Sub

Private Sub cmdPrev_Click()
[color green]' Move to the previous record in the database[/color]
rs.MovePrevious
[color green]' If we have Moved past the first record, then
' reset to the first record[/color]
If rs.BOF Then
rs.MoveFirst
End If
[color green]' Display the Name on the form, this is done in a
' sub so that it is re-usable[/color]
DisplayRecord

End Sub

Private Sub DisplayRecord()
[color green]' Display the record on the form[/color]
If rs.RecordCount = 0 Then
[color green]' If there are no records, show it blank[/color]
lblFullName = ""
lblRecNumber = "0 of 0"
Else
[color green]' Combine the first and last name for display[/color]
lblFullName = rs!FirstName & " " & rs!LastName
[color green]' Show the current record, and the total records[/color]
lblRecNumber = rs.Bookmark & " of " & rs.RecordCount
End If
End Sub

Public Sub EditUser()
[color green]' ctlControl is now set to be a pointer to a form Control[/color]
Dim ctlControl As Control

[color green]' Using the rs recordset[/color]
With rs
[color green]' Loop through each control on Form2[/color]
For Each ctlControl In Form2
[color green]' See if the control is a Text Box[/color]
If TypeOf ctlControl Is TextBox Then
[color green]' Update the rs with the new data added[/color]
.Fields(ctlControl.Name) = ctlControl.Text
End If
Next ctlControl
.Update
End With

End Sub

Public Sub AddUser()
[color green]' ctlControl is now set to be a pointer to a form Control[/color]
Dim ctlControl As Control

[color green]' Using the rs recordset[/color]
With rs
.AddNew
For Each ctlControl In Form2
[color green]' Loop through each control on Form2[/color]
If TypeOf ctlControl Is TextBox Then
If Trim(ctlControl.Text) <> "" Then
[color green]' Update the rs with the new data added[/color]
.Fields(ctlControl.Name) = ctlControl.Text
End If
End If
Next ctlControl
.Update
End With

End Sub

[color green]' =============================================

' Paste the below into the General Declarations
' section of Form2[/color]

Option Explicit

[color green]' Controls[/color]
Dim ctlText As VB.TextBox
Dim ctlLabel As VB.Label

[color green]' With Events will only work for 1 control at a time
' So we need to declare each Command Button[/color]
Dim WithEvents ctlCommand1 As VB.CommandButton
Dim WithEvents ctlCommand2 As VB.CommandButton
Dim colCommand As Collection

Public Sub FormAsEdit(rs As ADODB.Recordset)
Dim fld As ADODB.Field
Dim Index As Integer

[color green]' Load the Form First, you can't add controls until
' the form is loaded[/color]
Load Form2
Form2.Show
Form2.Caption = "Edit User"

[color green]' Go through each field in the recordset.[/color]
For Each fld In rs.Fields
[color green]' If there is already an entry for the field then
' show it[/color]
If Trim(fld.Value) <> "" Then
[color green]' Create the Label[/color]
Set ctlLabel = Controls.Add("VB.Label", "lbl" & fld.Name, Form2)
[color green]' Set the position and caption of the label[/color]
With ctlLabel
.AutoSize = True
.Move 120, 120 + (620 * Index), 3255, 255
.Caption = fld.Name
.Visible = True
End With

[color green]' Create the Text Box[/color]
Set ctlText = Controls.Add("VB.TextBox", fld.Name, Form2)
[color green]' Set the position and text of the textbox[/color]
With ctlText
.Width = ctlLabel.Width
.Move 120, 360 + (620 * Index), 3255, 255
.Text = fld.Value
.Visible = True
End With
Index = Index + 1
End If
Next fld

[color green]' Add a command button to save[/color]
Set ctlCommand1 = Controls.Add("VB.CommandButton", "cmdOK", Form2)
With ctlCommand1
.Move 3480, 360, 1575, 375
.Caption = "&Save"
.Visible = True
End With

[color green]' Add a command button to cancel[/color]
Set ctlCommand2 = Controls.Add("VB.CommandButton", "cmdCancel", Form2)
With ctlCommand2
.Move 3480, 840, 1575, 375
.Caption = "&Cancel"
.Visible = True
End With

[color green]'This is needed to show the buttons properly[/color]
If Index < 2 Then Index = 2

[color green]'Resize the form[/color]
With Form2
.Width = 5280
.Height = 550 + (620 * Index)
.Left = (Screen.Width - Me.ScaleWidth) \ 2
.Top = (Screen.Height - Me.ScaleHeight) \ 2
End With
End Sub

Public Sub FormAsAdd(rs As ADODB.Recordset)
Dim fld As ADODB.Field
Dim Index As Integer

[color green]' Load the Form First, you can't add controls until
' the form is loaded[/color]

Load Form2
Form2.Show
Form2.Caption = "Add User"

[color green]' Go through each field in the recordset.[/color]
For Each fld In rs.Fields
[color green]' Create the Label[/color]
Set ctlLabel = Controls.Add("VB.Label", "lbl" & fld.Name, Form2)
[color green]' Set the position and caption of the label[/color]
With ctlLabel
.AutoSize = True
.Move 120, 120 + (620 * Index), 3255, 255
.Caption = fld.Name
.Visible = True
End With

[color green]' Create the Text Box[/color]
Set ctlText = Controls.Add("VB.TextBox", fld.Name, Form2)
[color green]' Set the position and text of the textbox[/color]
With ctlText
.Width = ctlLabel.Width
.Move 120, 360 + (620 * Index), 3255, 255
.Text = ""
.Visible = True
End With
Index = Index + 1
Next fld

[color green]' Add a command button to save[/color]
Set ctlCommand1 = Controls.Add("VB.CommandButton", "cmdOK", Form2)
With ctlCommand1
.Move 3480, 360, 1575, 375
.Caption = "&OK"
.Visible = True
End With

[color green]' Add a command button to cancel[/color]
Set ctlCommand2 = Controls.Add("VB.CommandButton", "cmdCancel", Form2)
With ctlCommand2
.Move 3480, 840, 1575, 375
.Caption = "&Cancel"
.Visible = True
End With

[color green]'This is needed to show the buttons properly[/color]
If Index < 2 Then Index = 2

[color green]'Resize the form[/color]
With Form2
.Width = 5280
.Height = 550 + (620 * Index)
.Left = (Screen.Width - Me.ScaleWidth) \ 2
.Top = (Screen.Height - Me.ScaleHeight) \ 2
End With
End Sub

Private Sub ctlCommand1_Click()
Select Case Form2.Caption
Case "Edit User"
[color green]' Edit the user[/color]
Form1.EditUser
Case "Add User"
[color green]' Add the user[/color]
Form1.AddUser
End Select
Unload Form2
End Sub

Private Sub ctlCommand2_Click()
Unload Form2
End Sub
[/tt]