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 Next 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
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 Else MsgBox "Please open frmTeachersDataEntry and retry" End If End Sub
Loops – For Each
Dyamically Updating A Combo Box From Another Combo Box
Form Design View Explained
Hyperlinking To A Form
Sorting And Filtering Forms