Manage large groups of controls by using the Tag property

Today's guest blogger is Access MVP Garry Robinson, who offers the Smart Access collection of articles at http://www.vb123.com/kb.

In Smart Access Answers, Doug Steele was asked “What if I have a number of different controls–say, text boxes, labels, and check boxes and I want to make them invisible or visible when a form is being used?

In this section of code, you will see how to loop through all the controls on a form and manipulate them using VBA.

Each control in Access has a Tag property that you can use to store any extra information about a form, report, section, or control needed by your application. It's relatively simple to set the Tag property for each of the controls you want to treat the same way and then loop through all controls on the form, using that property to test whether or not to perform the action. Here's a simple version of the code that you'd use:

Dim ctlCurr As Control
Dim booCondition As Boolean

For Each ctlCurr In Me.Controls
    If ctlCurr.Tag = "Group1" Then
        ctlCurr.Visible = booCondition
    End If
Next ctlCurr

Of course, for this to work you need to set the Tag property of all of the controls in the group to the same value ("Group1" in my sample code). To set the Tag properties for a number of controls in one step, select all of the controls you want to group together. (If you're not familiar with selecting multiple controls at once, you can draw a rectangle around all of the controls using your mouse, or you can hold down the Shift key while clicking on each control with your mouse.) Figure 1 shows a screen with a number of controls selected.

Figure 1

Once you've selected all of the controls, the Properties window will contain only those properties that exist for all of the selected controls. Assuming that you're on the Other or All tab in the Properties window, the Tag property should be the last one listed. Type the name you want to use for your group into the Tag property setting, and it will apply to all of the selected controls (see Figure 2 for an example).

Figure 2

What happens if the control doesn’t actually have the property that you are manipulating?

This above approach does have a potential problem. Not all properties exist for every control. For example, you might have added a CheckBox to a group in which you're going to manipulate the Font. CheckBox controls, unfortunately, don't have a FontName property, so if you attempt to set the FontName property on a CheckBox just because it has its Tag property set to the right value, your code will blow up.

Rather than having to be very careful with what properties you can change for what groups, you can add a check in your error trapping to handle this. Depending on how you're referring to a property that doesn't exist for a given control, you'll get either error number 438 ("Object doesn't support this method") or error number 2455 ("You entered an expression that has an invalid reference to the property").

So, to address the problem, you'll need an error checking routine along the lines of this code:

On Error GoTo Err_ChangeGroupProperties
'--- CONTROL HANDLING CODE GOES HERE
End_tglFont_Click: 
Exit Sub

Err_ChangeGroupProperties:

If Err.Number = 438 Or Err.Number = 2455 Then
    Resume Next
Else
    MsgBox Err.Description & " (" & Err.Number & ")"
    Resume End_tglFont_Click
End If

On error, this code returns control to the line of code following the line of code that caused the error if either error 438 or 2455 occurs, thus ignoring the error. If the error is anything other than 438 or 2455, the error gets trapped in the Else part of the If statement.

To read both simpler and more versatile ways to manipulate control properties, see Doug’s full article from the Smart Access collection at
http://www.vb123.com/kb/200405_ds_groupie.htm

Be a guest blogger! Send your favorite Access tips to Mike & Chris at accpower@microsoft.com.