3.3. Interacting with Forms and Controls: Referencing Form Controls

In Microsoft Access, forms provide a user-friendly interface to display and interact with data. Each form contains various controls like text boxes, combo boxes, buttons, and labels that allow users to enter, display, and manipulate data. In VBA (Visual Basic for Applications), referencing form controls is essential to interact with these elements programmatically, such as reading values, setting values, or responding to user input.

What Are Form Controls?

Form controls are the individual elements on a form that allow users to interact with the data. These controls include:

• Text boxes: Used for displaying and editing data.
• Combo boxes: Dropdown lists for selecting values.
• List boxes: Similar to combo boxes but typically display multiple selections.
• Labels: Display text or captions.
• Buttons: Used to trigger actions like submitting data, opening forms, etc.
• Check boxes: Allow users to select or unselect options.
• Option buttons (radio buttons): Allow users to choose one option from a group.

Referencing Form Controls in VBA

To interact with form controls using VBA, you reference the controls by their name. Each control on a form has a unique name, which is used to identify it when writing VBA code. You can access the control's properties, such as Value, Text, Caption, Enabled, Visible, and more.

Basic Syntax for Referencing Controls:

Forms("FormName").Controls("ControlName")

• FormName: The name of the form containing the control.
• ControlName: The name of the control (e.g., "txtEmployeeName", "btnSave").

Accessing and Modifying Control Properties

You can access and modify the properties of form controls using the Forms object in VBA.

Example of Referencing a Text Box:

Dim employeeName As String
employeeName = Forms("EmployeeForm").Controls("txtEmployeeName").Value

This code gets the value entered into the "txtEmployeeName" text box on the "EmployeeForm" form.

Setting the Value of a Control:

Forms("EmployeeForm").Controls("txtEmployeeName").Value = "John Doe"

This code sets the value of the "txtEmployeeName" text box to "John Doe".

Referencing a Combo Box:

Combo boxes store a value selected from a list of options, so you can reference them using the .Value property.

Dim selectedDepartment As String
selectedDepartment = Forms("EmployeeForm").Controls("cboDepartment").Value

This code stores the selected value from the "cboDepartment" combo box into the selectedDepartment variable.

Referencing Other Control Types:

1. Buttons: A button control typically triggers an event when clicked. You can reference a button, but you usually handle it by creating an event procedure.

   DoCmd.OpenForm "EmployeeForm", , , , , , "HR"
   

   Here, the button might trigger the opening of a form based on a value passed through the OpenArgs parameter.
2. Check Boxes: Checkboxes are referenced using the .Value property, which returns True if checked and False if unchecked.

   If Forms("EmployeeForm").Controls("chkIsActive").Value = True Then
       MsgBox "The employee is active."
   Else
       MsgBox "The employee is not active."
   End If
   

3. Option Buttons: Option buttons (radio buttons) are typically grouped, and only one button in the group can be selected at a time. You reference them using the .Value property.

   If Forms("EmployeeForm").Controls("optMale").Value = True Then
       MsgBox "Male selected"
   End If
   

4. List Boxes: List boxes allow multiple selections and are referenced using the .Value property for single selections and .ItemsSelected for multiple selections.

   Dim selectedValue As String
   selectedValue = Forms("EmployeeForm").Controls("lstDepartments").Value
   

   For multiple selections, you would use ItemsSelected:

   Dim i As Integer
   For i = 0 To Forms("EmployeeForm").Controls("lstDepartments").ItemsSelected.Count - 1
       MsgBox Forms("EmployeeForm").Controls("lstDepartments").ItemData(Forms("EmployeeForm").Controls("lstDepartments").ItemsSelected(i))
   Next i
   

Using Control References in Event Procedures

Often, you reference controls within event procedures, such as a button click, form load, or change event.

Example of Button Click Event:

Let's say you want to update a field when a user clicks a button. Here's how you would reference the control and perform an action.

1. Button Click Event:

   Private Sub btnSave_Click()
       ' Get the value from a text box and update another field
       Forms("EmployeeForm").Controls("txtEmployeeID").Value = Forms("EmployeeForm").Controls("txtEmployeeName").Value
   End Sub
   

2. Change Event (for Text Box): This event triggers whenever the user changes the value in the text box.

   Private Sub txtEmployeeName_Change()
       If Len(Me.txtEmployeeName.Value) > 50 Then
           MsgBox "Employee name is too long!"
       End If
   End Sub
   

Navigating Between Form Controls

You can also set the focus between different controls on a form using the SetFocus method. This is helpful in validating data or directing users through a form.

Setting Focus to a Control:

Forms("EmployeeForm").Controls("txtEmployeeName").SetFocus

This sets the focus to the "txtEmployeeName" control on the "EmployeeForm" form.

Moving Focus to the Next Control:

If you have a series of controls (e.g., text boxes for employee details), you can set focus to the next control after completing the current one.

Private Sub txtEmployeeName_AfterUpdate()
    Forms("EmployeeForm").Controls("txtEmployeeID").SetFocus
End Sub

Referencing Controls Using Control Arrays

In some cases, you may have multiple controls of the same type (e.g., a series of option buttons). Access does not support control arrays directly, but you can use a loop to reference each control programmatically.

Example Using a Loop:

If you have a set of option buttons for selecting employee status, you can loop through the controls collection and check which one is selected.

Dim ctl As Control
For Each ctl In Forms("EmployeeForm").Controls
    If TypeName(ctl) = "OptionButton" Then
        If ctl.Value = True Then
            MsgBox ctl.Name & " is selected"
        End If
    End If
Next ctl

Conclusion:

Referencing and interacting with form controls in Microsoft Access is a fundamental skill when working with VBA to build interactive and dynamic forms. By referencing controls by their name, you can easily access and modify their properties, trigger actions, and automate tasks based on user input. Understanding how to reference controls, set focus, and handle user events will significantly enhance the functionality and user experience of your Access applications.