Grouping controls together on an Access form

Question

I have an Access2003 form where I wanted to group several controls together and change visibility programatically, though VBA code.

Is this possible? I do know that I can group items through Format -> Group, but if I do that, how do I refer the the entire group in my code?

Thank you

Solution 1

You could place all the controls in a group box control then change the visibility of the group box itself.

You could also add a value in the tag property of each control you want to group, then in VBA loop through the control and check for that value and change the visibility there.

Set the tag property of all the controls you want to group to something like groupABC or whatever you wish.

Then somewhere in your code use this to loop through the form controls and check for it.

Dim ctrl As Control
For Each ctrl In Me.Controls
    If ctrl.Tag = "groupABC" Then
        ctrl.Visible = False
    End If
Next

Solution 2

To elaborate on my comment on using custom collections, you'd do something like this in your form's module:

  Private mcolGroupABC As New Collection

  Private Sub IntializeCollections()
    Dim ctl As Control

    If mcolGroupABC.Count = 0 Then
       For Each ctl in Me.Controls
         If ctl.Tag = "GroupABC" Then
            mcolGroupABC.Add ctl, ctl.Name
         End If
       Next ctl
       Set ctl = Nothing
    End If
  End Sub

  Private Sub Form_Load()
    Call InitializeCollections
  End Sub

  Private Sub ShowControls(mcol As Collection, bolShow As Boolean)
    Dim ctl As Control

    For Each ctl In mcol
      ctl.Visible = bolShow
    Next ctl
    Set ctl = Nothing
  End Sub

To hide the controls, you'd do this:

  Call ShowControls(mcolGroupABC, False)

And to show them:

  Call ShowControls(mcolGroupABC, True)

That's pretty simple, no?

This is the kind of code I use in my apps all the time, and I've used it ever since the first time I implemented it, about 10 years ago, and noticed that it was clearly noticeably faster to show/hide controls with the custom collection than it was with walking the entire Controls collection.

The only caveat is that if one of the controls has the focus, it will error out if you try to hide it. That's easily enough addressed, since if you're hiding a group of controls, you surely have an appropriate place to set the focus before you do so.

Solution 3

I like the tag property suggested by Joel Gauvreau. Other possibilities include a tab control and / or subforms.

Solution 4

A more efficient approach is to add all related controls to a Tab Control. You create a Tab control with a single Page, and then set the Tab control Style to None and Back Style to Transparent. This way the visual appearance of a Tab control fades away, but its grouping semantics remain. Then add controls to the Page, either creating them inside or Cut+Paste existing controls into the page (remember to reenable event procedures after pasting).

If it’s just a matter of visibility, now you can just set the Visible property of the Page control and all controls that belong to it will show or hide.

However, if you need to do something more elaborate to each grouped control, you can iterate through them with:

  Dim ctl As control
  
  For Each ctl In page_control.Controls
     ctl.Visible = False
  Next

You can also use the .Tag or .Type properties to do different things to these controls.

View more solutions