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:

Dim booCondition As Boolean

If booCondition = True Then

  Me!txtText1.Visible = True

Else

  Me!txtText1.Visible = False

End If

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

Me!txtText1.Visible = booCondition

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:

Me!txtText1.Visible = booCondition

Me!txtText2.Visible = booCondition

Me!txtText2.Visible = booCondition

You can simulate a control array with code like this:

Dim booCondition As Boolean

Dim lngLoop As Long

Dim strControlName As String

For lngLoop = 1 to 3

  strControlName = "txtText" & lngLoop

  Me.Controls(strControlName).Visible = _

    booCondition

Next lngLoop

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:

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

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:

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.

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:

Sub PopulateFormControlTable()

Dim dbCurr As DAO.Database

Dim conForms As DAO.Container

Dim docForm As DAO.Document

Dim frmCurr As Access.Form

Dim ctlCurr As Access.Control

Dim strSQL As String

  Set dbCurr = CurrentDb()

  Set conForms = dbCurr.Containers("Forms")

  For Each docForm In conForms.Documents

    DoCmd.OpenForm docForm.Name, acDesign, _

            , , acFormReadOnly, acHidden

    Set frmCurr = Forms(docForm.Name)

    For Each ctlCurr In frmCurr.Controls

      strSQL = "INSERT INTO FormControl " & _

         (FormNm, ControlNm, ControlTypeDs) " & _

         "VALUES ('" & frmCurr.Name & "', '" & _

         ctlCurr.Name & "', '" & _

         CtlTypeDesc(ctlCurr.ControlType) & "')"

      dbCurr.Execute strSQL, dbFailOnError

    Next ctlCurr

    DoCmd.Close acForm, docForm.Name, acSaveNo

  Next docCurr

End Sub

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:

For Each ctlCurr In Me.Controls

 strWhere = "GroupNm = '" & GroupNm & "' " & _

           "AND FormNm = '" & Me.Name & "' : & _

           "AND ControlNm = '" & ctlCurr.Name & "'"

 If DCount("*", "FormControlGroup", strWhere) > 0 Then

'   The given control is in the group

  Else

'   The given control is not in the group

  End If

Next ctlCurr

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.

Function ListFormControlsInGroup( _

  FormName As String, _

  GroupName As String) As String

Dim dbCurr As DAO.Database

Dim rsCurr As DAO.Recordset

Dim strControls As String

Dim strSQL As String

  strSQL = "SELECT ControlNm " & _

    "FROM FormControlGroup " & _

    "WHERE FormNm = '" & FormName & "' " & _

    "AND GroupNm = '" & GroupName & "'"

  strControls = vbNullString

  Set dbCurr = CurrentDb()

  Set rsCurr = dbCurr.OpenRecordset(strSQL)

  With rsCurr

    Do While Not .EOF

      strControls = strControls & !ControlNm & ";"

      .MoveNext

    Loop

  End With

  If Len(strControls) > 0 Then

    strControls = ";" & strControls

  End If

End Function

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:

strControlsToToggle = ListFormControlsInGroup( _

     "frmGroupingControlsUsingTables", "Group2")

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:

If Len(strControlsToToggle) > 0 Then

  For Each ctlCurr In Me.Controls

    strCurrControlName = ";" & ctlCurr.Name & ";"

    If InStr(1, strControlsToToggle, _

      strCurrControlName, vbTextCompare) > 0 Then

' The given control is in the group

    Else

' The given control is not in the group

    End If

  Next ctlCurr

End If

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:

For Each ctlCurr In Me.Controls

 strWhere = "GroupNm = '" & GroupNm & "' " & _

           "AND FormNm = '" & Me.Name & "' : & _

           "AND ControlNm = '" & ctlCurr.Name & "'"

 If DCount("*", "FormControlGroup", strWhere) > 0 Then

    ctlCurr.FontName = strFontName

    ctlCurr.ForeColor = lngForeColor

  End If

Next ctlCurr

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.

Type PropNameAndValue

  PropName As String

  PropValue As Variant

End Type

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

Sub ChangeGroupProperties( _

  CurrentForm As Form, _

  GroupNm As String, _

  WhatToChange() As PropNameAndValue _

)

On Error GoTo Err_ChangeGroupProperties

Dim ctlCurr As Control

Dim lngLoop As Long

Dim strFormNm As String

Dim strWhere As String

  strFormNm = CurrentForm.Name

  For Each ctlCurr In CurrentForm.Controls

    strWhere = "GroupNm = '" & GroupNm & _

               "' AND FormNm = '" & strFormNm & _

               "' AND ControlNm = '" & _

               ctlCurr.Name & "'"

    If DCount( _

       "*", "FormControlGroup", strWhere) > 0 Then

      For lngLoop = LBound(WhatToChange) To _

                               UBound(WhatToChange)

        ctlCurr.Properties( _

         WhatToChange(lngLoop).PropName) = _

             WhatToChange(lngLoop).PropValue

      Next lngLoop

    End If

  Next ctlCurr

End_ChangeGroupProperties:

  Exit Sub

Err_ChangeGroupProperties:

  If Err.Number = 2455 Then

    Resume Next

  Else

    booStatus = False

    Err.Raise Err.Number, _

       "ChangeGroupProperties", Err.Description

    Resume End_ChangeGroupProperties

  End If

End Sub

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:

Dim typValues(1 To 2) As PropNameAndValue

  typValues(1).PropName = "FontName"

  typValues(1).PropValue = strFontName

  typValues(2).PropName = "ForeColor"

  typValues(2).PropValue = lngForeColor

  Call ChangeGroupProperties(Me, "Group1", typValues)

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.