7 Ways to Loop Through Controls on a Microsoft Access Form

Let's say you want to show or hide a group of form controls based on some criteria.

There are several reasons you may want to do this:

• Protect data from accidental editing
• Use application-level "security" to adjust the UI based on the user's role
• Show or hide fields based on the kind of data (e.g., customer vs. vendor)
• Obscure sensitive information from "over-the-shoulder" attacks

Whatever the reason, there will inevitably be times that you want to provide some sort of special handling for a group of form controls.  Depending on what those controls have in common, there are several different approaches you might use to loop through them in VBA:

1. Every control
2. By form section
3. By control type
4. By control's Tag property
5. By parsing the Tag property (supports multiple groups)
6. By control's name
7. By control's Name property

Every Control

Each example on this page assumes that you are writing code in the form's "code-behind" module (e.g., Form_Form1).  In this context, the Me variable refers to the current form object.

The first example simply loops through the form's Controls collection, which is a collection of every control on the form.  I'm just printing the control's name to the immediate window for demonstration purposes.  In a real application, you would replace the Debug.Print Ctl.Name line with one or more lines of code that does something interesting with the control object, such as show/hide or enable/disable it.

Dim Ctl As Control
For Each Ctl In Me.Controls
    Debug.Print Ctl.Name
Next Ctl

By Form Section

Forms have three sections you need to be concerned with*:

• Form Header: Me.Section(acHeader)
• Form Detail: Me.Section(acDetail)
• Form Footer: Me.Section(acFooter)

Each of these sections has its own Controls collection that only includes the controls that have been placed within that section.

Dim Ctl As Control
For Each Ctl In Me.Section(acHeader).Controls   ' OR acDetail OR acFooter
    Debug.Print Ctl.Name
Next Ctl

^{* Forms also have a page header and page footer, but you should not use them. They only appear when the form is printed or in print preview view, but your users should not be printing your forms.  That's what reports are for.}

By Control Type

You can use the ControlType property to treat all of your text boxes or all of your combo boxes, etc., the same way:

Dim Ctl As Control
For Each Ctl In Me.Controls
    Select Case Ctl.ControlType
    Case acLabel:    Debug.Print "Label  : "; Ctl.Name
    Case acTextBox:  Debug.Print "Textbox: "; Ctl.Name
    Case acComboBox: Debug.Print "Combo  : "; Ctl.Name
    Case acListBox:  Debug.Print "Listbox: "; Ctl.Name
    End Select
Next Ctl

For a full list of ControlType enumerations, see here: AcControlType enumeration (Access).

By Control's Tag Property

The Tag property is a handy little control property that accepts any string up to 2,048 characters long.  You can use this property to identify groups of controls that you want to treat the same way.

For example, the code below will output the name of every control on our form that has a Tag value of "MyGroup".  

Dim Ctl As Control
For Each Ctl In Me.Controls
    If Ctl.Tag = "MyGroup" Then Debug.Print Ctl.Name
Next Ctl

The Tag property can be set from the "Other" tab of the control's Property Sheet ([F4]) in Form Design View:

One of the biggest advantages to this approach is that you can [Ctrl]-select or click-drag-select multiple controls in Form Design View and set all of their Tag values at one time.  

Unfortunately, the opposite is not possible.  There is no easy way to select every control that has a certain Tag property.  If you want to be able to easily see which controls have been assigned some special handling, you may want to consider selecting controls by name as shown in the final two approaches.

By Parsing the Control's Tag Property

What if you need to use a control's Tag property for multiple purposes?

You can use my Parse() function to embed multiple name-value pairs inside each control's Tag property.  For example, you could have controls with the following Tag values:

• MyTextbox.Tag: MyGroup=True
• MyOtherTextbox.Tag: MyOtherGroup=True
• MyCommonTextbox.Tag: MyGroup=True; MyOtherGroup=True

Dim Ctl As Control
For Each Ctl In Me.Controls
    If Parse(Ctl.Tag, "MyGroup", vbBoolean) Then Debug.Print Ctl.Name
Next Ctl

The above code would output:

MyTextbox
MyCommonTextbox

By Control Name

One downside to using the Tag property to identify which controls to show or hide is that there is no way to see which controls have that Tag property and which ones don't.  By using a Select Case statement, we can visually see the name of every control that is getting special handling in one place within the code.

Dim Ctl As Control
For Each Ctl In Me.Controls
    Select Case Ctl.Name
    Case "MyTextBox", _
         "MyOtherTextBox"
        Debug.Print Ctl.Name
    End Select
Next Ctl

By Control's Name Property

There are a few problems with the previous approach, though:

• You can have a typo when entering the control's name in VBA
• The name of the control could get changed

The advantage of using a control's Name property–instead of using a literal string–is that you can verify at compile-time that each of the controls you have special handling for actually exists.  When using the control's name as a string, you lose the benefit of compile-time checking.  With string names, typos and renamed controls become logic errors.  When using the control's Name property, typos and renamed controls are compile errors.  

And compile errors are WAAAAAY better than logic errors.

Dim Ctl As Control
For Each Ctl In Me.Controls
    Select Case Ctl.Name
    Case Me.MyTextBox.Name, _
         Me.MyOtherTextBox.Name
        Debug.Print Ctl.Name
    End Select
Next Ctl

Image by Paul Brennan from Pixabay