Handling groups of controls

This month in Access Answers, Doug Steele gathers together several questions around handling groups of controls to provide alternative solutions to a common problem.

I have a number of controls on my form that I need to be able to make visible or not depending on what’s happening in the program logic. Is there an easier way than having a whole series of VBA statements, one per control?

This is one area where, in my opinion, Visual Basic has it over Access. In Visual Basic, an array of controls can be used successfully to handle this sort of requirement. Unfortunately, Access doesn’t support control arrays, so you’re going to be forced to improvise.

To control the visibility of a given control, you set its Visible property to True or False:

The same thing can be done a bit more concisely like this:

If you need to display three text boxes under some conditions, and not display those same three text boxes under other conditions, you could end up writing code like this:

Naming the controls and treating them as a control array works, but it’s a bit limited. What if I have a number of different controls–say, text boxes, labels, and check boxes?

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:

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

This 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, 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.

The Tag property may work in some cases, but I want to be able to use the Tag property for something else. As well, I need some controls to be in more than one group.

Once your requirements get more sophisticated, the solutions start becoming a little more complicated! A reasonable approach is to add three new tables into the application: one that lists all of the controls on all of the forms, one that lists all of the groups you’ve created, and an intersection table that resolves the many-to-many relationship between those two tables (see Figure 3).

Figure 3

There are some nuisances associated with this solution. Populating the table listing all of the controls on each of the forms can be a little tricky, since you can’t refer to the controls on a form unless the form is open. Just finding all of the forms can be interesting: The Forms collection only contains those forms that are open, so those of you using Access 97 (like me) need to use the Forms.Container collection to get a list of all of the forms in the project. (In newer versions of Access, you can use the AllForms collection, which is part of the CurrentProject object.) What I do is determine each form in the application, open it Hidden, loop through all of the controls on the form I just opened, writing the details of each control to the table, and then close the form. (The function CtlTypeDesc translates the numeric ControlType property to a text description. See the accompanying Download if you need the code.) Here’s the code:

Once the FormControl table is populated, it’s fairly straightforward to design a form that will let you group controls (see the Download for an example).

Now, when I loop through the controls on a form, I need to determine whether the specific control is a member of the group in question. One way to do this is to use the DCount function:

Another approach is to build a delimited string that contains all of the controls in the group and search for the individual control in that string.

What this code does is open a recordset that contains all of the controls for the given form that belong to the given group. As I read each control name, I add it to a string and put a semicolon after it. Once I’ve come to the end of the recordset, if I find any controls, I put a semicolon at the beginning of the string. In that way, each control has a semicolon before and after its name. I can call the function with this code:

After this code runs, the variable strControlToToggle will contain “;chkFirst;lblFirst;lblFirstCheck;”. Now, given the name of a control, I can simply do an InStr against that strControlList to determine whether or not any specific control exists in the group:

Is this second method any better than my previous version? I don’t know. This version only requires a single call to the database, but it brings back a recordset, as opposed to a single value, the way that the DCount statement does. This method does have the advantage that you can tell in advance whether a particular group is used for the controls on the form, so that you don’t have to loop through all of the controls unnecessarily. You decide which way you prefer!

Now that I know how to group controls, is there an easy way of changing more than one property at a time? In other words, I want to change both the font and the color for all of the controls in a given group.

The obvious way to handle this problem is just to have multiple statements. Assuming that you’ve got some way of determining what value to assign to variables strFontName and lngForeColor, your code would look like this:

However, another option is to create a function, and pass arrays of properties to that function. The simplest implementation is to create a type that can contain two values: the name of the property to change, and the value to which that property should be changed.

You can now create an array of this type and pass a number of different properties to be changed at once.

To use this routine to make the same changes to the font and color that I made before, I have to declare an array that holds those values, and then call the routine:

As you can see, I’m passing a reference to the form, the name of a group to use, and an array of properties and values to my ChangeGroupProperties routine. As before, I loop through each control on the form to determine whether or not it’s in the group of interest. For each control that’s in the group, I loop through all of the entries in the array of properties and values and attempt to set the property to the value. As explained earlier, the error handler simply tells the code to skip to the next property if a specific property doesn’t exist for a given control.

Your download file is called   Steele_ControlTags.accdb


Other Pages on This Site You Might Want To Read

Access Answers: Excelling Automatically
From Zoom Box to Custom Dialog
Access Subquery Techniques
Logging Access Startup Details and Version Control

About Doug Steele

Doug Steele has worked with databases-both mainframe and PC-for many years with a major international oil company. In a previous life, he taught introductory computer programming at the University of Waterloo. Microsoft has recognized him as an Access MVP for his contributions to the user community over the years.

Doug is the author of Access Solutions with Arvin Meyer.

This entry was posted in Access Controls. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


This site uses Akismet to reduce spam. Learn how your comment data is processed.