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