Working With Form Objects

In this blog post we will be taking a look at how you can work with form objects in MS Access.

Using the collection Controls of a form, all controls can be cycled through and only those of a particular type can be targeted.  In the following example all controls of the form are checked for 1) their type and 2) the section in which they appear.  If a control is a text box and is in the Detail Section of the form its value is outputted in the Immediate Window.

Private Sub Form_Open(Cancel As Integer)
    Dim c As Variant
    For Each c In Me.Form.Controls
    'Me.Form.Controls is collection native to Access
        If c.ControlType = acTextBox And c.Section = acDetail Then
        'if the control is a text box and is in the detail section
            Debug.Print c.Name & " = '" & c & "'"
            'print out the name of the control and its value
            'e.g. MobilePhone = '7654321'
        End If    
End Sub

Checking if a Form is loaded

To check whether a form is currently open or not use the CurrentProject.AllForms collection which has an IsLoaded function which returns true if the form is loaded.  CurrentProject also contains all the other All* collections.

To run this function, enter ? isMyFormOpen(“frmStudentsDataEntry”) in the immediate window and click the return key (we are assuming that you are using the provided database).

Function isMyFormOpen(frmName As String) As Boolean
'This a very useful function. Save it and use it!
  isMyFormOpen = CurrentProject.AllForms(frmName).IsLoaded
End Function

Referencing Controls

Me keyword

When in a form module, the Me keyword refers to the form itself. It is, therefore, a quick way of referring to the form.

Private Sub cmdMyName_Click()
    MsgBox Me.Form.Name ' msgbox opens with the form’s name
End Sub

Private Sub cmdLastNameField_Click()
MsgBox Me.Form!LastName ' msgbox opens display content of LastName
'Move between records and click this button
End Sub

Full Form Reference

Referencing the form itself can be performed by writing:

Private Sub cmdFullFormReference_Click()
MsgBox Forms("frmStudentsDataEntry").Name ' msgbox opens with the form's name
End Sub

You may also reference another form if it is open.

Private Sub cmdReferenceOtherForm_Click()
If CurrentProject.AllForms("frmTeachersDataEntry").IsLoaded Then
'Check whether the form is loaded
    Forms("frmTeachersDataEntry").Filter = "[TeacherID]=3"
    'Set the filter
    Forms("frmTeachersDataEntry").FilterOn = True
    'Turn filter on
    Forms("frmTeachersDataEntry").Controls("FirstName") = "We have reference another form!"
    'Set value of FirstName
    MsgBox "Please open frmTeachersDataEntry and retry"
End If
End Sub


Related Posts

Loops – For Each
Dyamically Updating A Combo Box From Another Combo Box
Form Design View Explained
Hyperlinking To A Form
Sorting And Filtering Forms

As always, a site wouldn't be anything without its users so please feel free to comment! We welcome any comments you have on the material and any suggestions you may have for future content.

In our humble opinion, it is worth commenting just so you can play with Fun Captcha below!