INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Visual Basic (Microsoft) Versions 5/6 FAQ

Access Version or Conversion

Dynamically Create Controls by CasperTFG
Posted: 8 Apr 02

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.

' 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


Option Explicit

' Record Variables
Public cn As ADODB.Connection
Public rs As ADODB.Recordset

Private Sub cmdExit_Click()
    ' Clean Up any loose ends
    cn.Close
    Set cn = Nothing
    Set rs = Nothing
    
    ' Close the program
    End

End Sub

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

    Form2.FormAsEdit rs
    
End Sub

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

    Form2.FormAsAdd rs
    
End Sub

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

    Set rs = New ADODB.Recordset
    
    ' Here we set cn as a New Connection.  By doing
    ' this we are creating the connection object.

    Set cn = New ADODB.Connection
    ' Now we point the connection object to our database.
    ' App.Path will return the directory from which your
    ' program is running.

    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                          "Data Source=" & App.Path & "\DB1.MDB"
    ' Open the connection to the database
    cn.Open
    
    ' Here we set rs as a New Recordset.  By doing
    ' this we are creating the recordset object.

    Set rs = New ADODB.Recordset
    ' We set the rs parameters, including the cursor
    ' location, and record locking type

    With rs
        .CursorLocation = adUseClient
        .LockType = adLockBatchOptimistic
        ' Here is the statement that will get the actual
        ' records from the connected database

        .Open "SELECT * FROM Contacts;", cn
        .ActiveConnection = cn
    End With
    
    ' We will now display the first record on our form
    ' by re-using the code for moving to the first record

    Call cmdFirst_Click

End Sub

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

    DisplayRecord

End Sub

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

    DisplayRecord

End Sub

Private Sub cmdNext_Click()
    ' Move to the Next record in the database
    rs.MoveNext
    ' If we have Moved past the last record, then
    ' reset to the last record

    If rs.EOF Then
        rs.MoveLast
    End If
    ' Display the Name on the form, this is done in a
    ' sub so that it is re-usable

    DisplayRecord

End Sub

Private Sub cmdPrev_Click()
    ' Move to the previous record in the database
    rs.MovePrevious
    ' If we have Moved past the first record, then
    ' reset to the first record

    If rs.BOF Then
        rs.MoveFirst
    End If
    ' Display the Name on the form, this is done in a
    ' sub so that it is re-usable

    DisplayRecord

End Sub

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

Public Sub EditUser()
    ' ctlControl is now set to be a pointer to a form Control
    Dim ctlControl As Control
    
    ' Using the rs recordset
    With rs
        ' Loop through each control on Form2
        For Each ctlControl In Form2
            ' See if the control is a Text Box
            If TypeOf ctlControl Is TextBox Then
                ' Update the rs with the new data added
                .Fields(ctlControl.Name) = ctlControl.Text
            End If
        Next ctlControl
        .Update
    End With
    
End Sub

Public Sub AddUser()
    ' ctlControl is now set to be a pointer to a form Control
    Dim ctlControl As Control
    
    ' Using the rs recordset
    With rs
        .AddNew
        For Each ctlControl In Form2
        ' Loop through each control on Form2
            If TypeOf ctlControl Is TextBox Then
                If Trim(ctlControl.Text) <> "" Then
                    ' Update the rs with the new data added
                    .Fields(ctlControl.Name) = ctlControl.Text
                End If
            End If
        Next ctlControl
        .Update
    End With

End Sub

' =============================================

' Paste the below into the General Declarations
' section of Form2


Option Explicit

' Controls
Dim ctlText As VB.TextBox
Dim ctlLabel As VB.Label

' With Events will only work for 1 control at a time
' So we need to declare each Command Button

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
    
    ' Load the Form First, you can't add controls until
    ' the form is loaded

    Load Form2
    Form2.Show
    Form2.Caption = "Edit User"
    
    ' Go through each field in the recordset.
    For Each fld In rs.Fields
        ' If there is already an entry for the field then
        ' show it

        If Trim(fld.Value) <> "" Then
            ' Create the Label
            Set ctlLabel = Controls.Add("VB.Label", "lbl" & fld.Name, Form2)
            ' Set the position and caption of the label
            With ctlLabel
                .AutoSize = True
                .Move 120, 120 + (620 * Index), 3255, 255
                .Caption = fld.Name
                .Visible = True
            End With
            
            ' Create the Text Box
            Set ctlText = Controls.Add("VB.TextBox", fld.Name, Form2)
            ' Set the position and text of the textbox
            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
    
    ' Add a command button to save
    Set ctlCommand1 = Controls.Add("VB.CommandButton", "cmdOK", Form2)
    With ctlCommand1
        .Move 3480, 360, 1575, 375
        .Caption = "&Save"
        .Visible = True
    End With
    
    ' Add a command button to cancel
    Set ctlCommand2 = Controls.Add("VB.CommandButton", "cmdCancel", Form2)
    With ctlCommand2
        .Move 3480, 840, 1575, 375
        .Caption = "&Cancel"
        .Visible = True
    End With
    
    'This is needed to show the buttons properly
    If Index < 2 Then Index = 2
    
    'Resize the form
    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
    
    ' Load the Form First, you can't add controls until
    ' the form is loaded

    
    Load Form2
    Form2.Show
    Form2.Caption = "Add User"
    
    ' Go through each field in the recordset.
    For Each fld In rs.Fields
        ' Create the Label
        Set ctlLabel = Controls.Add("VB.Label", "lbl" & fld.Name, Form2)
        ' Set the position and caption of the label
        With ctlLabel
            .AutoSize = True
            .Move 120, 120 + (620 * Index), 3255, 255
            .Caption = fld.Name
            .Visible = True
        End With
        
        ' Create the Text Box
        Set ctlText = Controls.Add("VB.TextBox", fld.Name, Form2)
        ' Set the position and text of the textbox
        With ctlText
            .Width = ctlLabel.Width
            .Move 120, 360 + (620 * Index), 3255, 255
            .Text = ""
            .Visible = True
        End With
        Index = Index + 1
    Next fld
        
    ' Add a command button to save
    Set ctlCommand1 = Controls.Add("VB.CommandButton", "cmdOK", Form2)
    With ctlCommand1
        .Move 3480, 360, 1575, 375
        .Caption = "&OK"
        .Visible = True
    End With
        
    ' Add a command button to cancel
    Set ctlCommand2 = Controls.Add("VB.CommandButton", "cmdCancel", Form2)
    With ctlCommand2
        .Move 3480, 840, 1575, 375
        .Caption = "&Cancel"
        .Visible = True
    End With
    
    'This is needed to show the buttons properly
    If Index < 2 Then Index = 2
    
    'Resize the form
    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"
            ' Edit the user
            Form1.EditUser
        Case "Add User"
            ' Add the user
            Form1.AddUser
    End Select
    Unload Form2
End Sub

Private Sub ctlCommand2_Click()
    Unload Form2
End Sub

Back to Visual Basic (Microsoft) Versions 5/6 FAQ Index
Back to Visual Basic (Microsoft) Versions 5/6 Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close