Looping through groups of controls on a form

[Seth Schrock]

I'm using Access 2010 and I'm trying to group controls so that I can edit each control's properties in a group as one. I am currently using the control's tag property to specify which group it is apart of and then I loop through call the controls and add it to a collection (through a class module) based on its tag property. However, this means that if I want to add a group to the form, I have to edit my code to reference another instance of my class module and then test for the new group. Is there a way to make it so that my code could be more universal instead of having to duplicate my code for each form and then edit the code to fit the number of groups on that form? I'm thinking it would need a loop, but I don't know what to loop through. Here is what I'm using now.

Private Sub Form_Load()
On Error GoTo Error_Handler
 
Dim ctl As Control
Dim Area8 As clsControl
Dim Area9 As clsControl
Dim Area10 As clsControl
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strCriteria As String
 
 
Set Area8 = New clsControl
Set Area9 = New clsControl
Set Area10 = New clsControl
 
Set db = CurrentDb
Set rst = db.OpenRecordset("qryUserPermissions")
 
For Each ctl In Me.Controls
    Select Case ctl.ControlType
        Case acComboBox, acTextBox, acCommandButton
            If ctl.Properties("Tag") = "Open" Or ctl.Properties("Tag") = "" Then
                ctl.Properties("Locked") = False
                ctl.Properties("Visible") = True
            Else
                Select Case ctl.Properties("Tag")
                    Case 8
                        Area8.AddControl ctl, ctl.Name
 
                    Case 9
                        Area9.AddControl ctl, ctl.Name
 
                    Case 10
                        Area10.AddControl ctl, ctl.Name
 
                End Select
            End If
    End Select
Next ctl
 
With rst
    strCriteria = "AreaID_fk = 8"
    .FindFirst strCriteria
    Area8.Permissions !MaxOfPermissionLevel
 
    strCriteria = "AreaID_fk = 9"
    .FindFirst strCriteria
    Area9.Permissions !MaxOfPermissionLevel
 
    strCriteria = "AreaID_fk = 10"
    .FindFirst strCriteria
    Area10.Permissions !MaxOfPermissionLevel
End With
 
Exit_Procedure:
    Set Area8 = Nothing
    Set Area9 = Nothing
    Set Area10 = Nothing
    Set db = Nothing
    Set rst = Nothing
 
    Exit Sub
 
Error_Handler:
    Call ErrorMessage(Err.Number, Err.Description, "Form_frmAdminForms: Form_Load")
    Resume Exit_Procedure
    Resume
 
End Sub

[Rabbit]

Use a collection of clsControl.

' Setup the collection
Dim colClsControl As Collection
Set colClsControl = New Collection
 
' Loop through all controls to get unique tags and run this
' An alternative is to use the contains method to check
' the existence of the key before attempting to access it.
colClsControl.Add New clsControl, ctl.Properties("Tag")
 
' Access the collection to add controls like this
colClsControl(ctl.Properties("Tag")).AddControl ctl, ctl.Name

[Seth Schrock]

How do I get the unique tag values? Is there a SELECT DISTINCT function available for all of the tag values?

[Rabbit]

No, you just loop through and check whether or not you've already added that one.

[Seth Schrock]

This is the first time I have worked with custom collections, so I would like to make sure that I'm understanding what I need to do.

Dim ctl As Control
Dim colClsControl as Collection
Set colClscontrol = New Collection
 
If Not colClsControl.Exists(ctl.Properties("Tag")) Then
    colClsControl.Add(New clsControl, ctl.Properties("Tag"))
End If

This would then become a collection of collections which I could loop through to control the properties of the controls within the sub-collections. Is that correct?

[Rabbit]

That's right. It should be in a loop though.

[Seth Schrock]

Okay, I have the following

Dim ctl As Control
Dim colClsControl As Collection
Dim strTag As String
 
Set colClsControl = New Collection
 
For Each ctl In Me.Controls
    Select Case ctl.ControlType
        Case acComboBox, acTextBox, acCommandButton
 
            strTag = ctl.Properties("Tag")
 
            If strTag & "" <> "" And strTag <> "Open" Then
                If Not colClsControl.Exists(strTag) Then
                    colClsControl.Add(New clsControl, strTag)
                    'Add control to the new collection
                Else
                    'Add the control to the existing collection
                End If
            End If
 
    End Select
Next ctl
 

However, I'm getting a syntax error on line 15 when I try to compile it.

Also, since the name of the new clsControl (or the existing one) is stored as a variable (or the equivalent of a variable if I just used ctl.Properties("Tag"), I'm not sure how to reference it to add a control to the collection (where I have the comments).

[zmbd]

(Post#2)colClsControl(ctl.Properties("Tag")).AddControl ctl, ctl.Name 
(Post#5)colClsControl.Add(New clsControl, ctl.Properties("Tag")) 
(Post#7)colClsControl.Add(New clsControl, strTag)

Did the line from post #5 work? If so, then I suspect your strTag isn't resolving correctly.
However, neither line from post 5 or 7 match Rabbit's in #2.

Now I'm not really sure what you are trying to do here; thus, I really can't provide much insight - just hoping that I might jog the ole braincells here.

[Rabbit]

Sorry, there is a slight error in my sample code. When calling a sub by itself, you have to leave off the parentheses. I will modify the original post.

As for how to add a control to the collection, that's in the last line of code in my original post.

[Seth Schrock]

The line from post #5 did not work. However, you pulled the wrong line from post #2.

Post #2  Line 8 colClsControl.Add(New clsControl, ctl.Properties("Tag"))
Post #7 Line 15 colClsControl.Add(New clsControl, strTag)

I actually ran the test with the ctl.Properties("Tag") instead of the variable and it still didn't work.

But you did point out the answer to my other question.

Edit: I will give that a try Rabbit. I have been pulled away to another project at the moment, but I will test that and let you know. And thanks to Z, I found that last line of code.

[zmbd]

You migh take a look at the following too for some insight on accessing the class module:
Class Module to Handle Opening Forms Hierarchically
...(there are a couple of other class module articles)
Simple Tutorial on Class Modules

[Seth Schrock]

@Z I think that those links will be helpful in the future for my understanding of class modules, but I think that my problem in this case is more with collections than class modules.

@Rabbit I'm trying to loop through the colClsControl collection, but I'm not sure what each item would be. For example, looping through controls is For Each control in Me.Controls... What is the equivalent to "control"?

Also, when I try to run the code, I'm getting an error on line 14 of post #7 saying Object doesn't support this property or method. I'm assuming the .Exists since it wasn't in the intellisense. Not sure what to try now.

[Rabbit]

I thought you were using .Exists because that's what Intellisense was showing you was available. Collection objects have a Contains method, use that instead.

I don't know what you mean by this:

What is the equivalent to "control"?

[Seth Schrock]

Same error using the Contains method. Intellisense only gives Add, Count, Item, and Remove options. I used the .Exists because the Help on collections had it in its example.

Dim col As Collection
For Each col In colClsControl
    Debug.Print col.Count
 
Next col

What I meant was, am I correct in declaring col as a collection?

[Rabbit]

After further research, it looks like the VBA implementation of the collection class doesn't have a method to check whether or not a key exists within the collection. Instead, you will have to trap for the error that gets thrown when attempting to access a nonexistent key. For example, with a function such as this:

Private Function Contains(col As Collection, key As String) As Boolean
    On Error GoTo NotFound
    Dim itm As Object
    Set itm = col(key)
    Contains = True
MyExit:
    Exit Function
NotFound:
    Contains = False
    Resume MyExit
End Function

No, you wouldn't declare the objects stored within the collection as a collection. Unless you are indeed storing a collection of collections. In your case, that is not what you're doing. You're storing a collection of your class clsControl so that's what you would declare it as.

[zmbd]

for... each... itteration?

[Seth Schrock]

Okay. I've got everything working so far. I'm now working on lines 42 - 54 of my OP. I've got the loop to loop through each clsControl in colClsControl. I just need to be able to get the key of the clsControl so that I can use it in the criteria for the recordset so that I can determine what the enabled, visible, and locked properties need to be set to. If I'm understanding the code correctly, each new instance of clsControl is named as the value of the control's tag property. However, since I'm outside of the loop that goes through the controls, I don't have access to the control's tag property (at least in a simple list of unique values). Is there a way to pull the key value for the current clsControl? I'm looking online right now for that, but haven't found anything yet.

Dim ctl As Control
Dim colClsControl As Collection
Dim strTag As String
Dim col As Collection
Dim cls As clsControl
 
Set colClsControl = New Collection
 
For Each ctl In Me.Controls
    Select Case ctl.ControlType
        Case acComboBox, acTextBox, acCommandButton
 
            strTag = ctl.Properties("Tag")
 
            If strTag & "" <> "" And strTag <> "Open" Then
 
                If Not Contains(colClsControl, strTag) Then
                    colClsControl.Add New clsControl, strTag
                End If
 
                colClsControl(strTag).AddControl ctl, ctl.Name
 
            End If
 
    End Select
Next ctl
 
Set db = CurrentDb
 
For Each cls In colClsControl
    'Get key value for the current clsControl
 
Next cls

[Seth Schrock]

I found a way! I added a Key property to my class module and I set it right after line 18 using the following code:

If Not Contains(colClsControl, strTag) Then
    colClsControl.Add New clsControl, strTag
    colClsControl(strTag).Key = strTag
End If

Then in my loop at the end, I can pull this value to be part of the criteria in my recordset.

If you think that there is a cleaner/better way of doing this, please let me know, but since I have it working I will move forward with my project.

Thanks Rabbit for all of your help!

[Rabbit]

No problem, good luck on the rest of your project.