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.