How to use Controls collection in Access 2003 and VBA

Question

I'm having a heck of the time trying to figure this one out.

I want to pass a Controls collection to a function but I get a type mismatch. Here's the function declaration:

Public Function DoStuffToCollection(topCtlList As Controls, isLocked As Boolean)

And here's how I call it:

Call DoStuffToCollection(myPage.Controls, isLocked)

myPage is a Page control from the TabControl. I've stepped through the code to see if there was something in myPage.Controls and there is.

For fun, I passed in Me.Controls (which would be the Form's control collection) instead of the myPage.Controls and no type mismatch. Is there a difference between a Form control collection and a Page control collection? This is driving me crazy.

A bit more digging, the debugger is calling myPage.Controls Children/Children as the type and the Me.Controls as Controls/Controls as the type. Why is this?

[Edit] Just adding a bit of info. doStuffToCollection is a function that recurses that's just supposed to locked bound fields and disable any kind of buttons within a tab control. Previously I just was able to lock it at the page level but then I added a page with a button in it. The button did not get disabled with the page. I'm aware of this http://allenbrowne.com/ser-56.html. I haven't been successful in getting it adapted to my need though.

Answer 1

One alternative would be to pass in the form as a form object using "Me". Obviously there is a difference exactly what I'm not sure.

Also poke about on the Page Object in the help. You can pass a Page object.

Answer 2

It does seem odd that you can't pass the Page's control collection directly. It might have something to do with the fact that the Pages collection itself is a special type of control collection.

Another alternative would be to declare the "topCtlList" parameter as Object instead of controls. It makes the code less readable and potentially more error prone, but it should remove the type-mismatch error.

Public Function DoStuffToCollection(topCtlList As Object, isLocked As Boolean)
'Debug.Print TypeName(topCtlList)
Dim ctl As Control
For Each ctl In topCtlList
   Debug.Print ctl.Name
Next ctl

End Function

Answer 3

@Tim Lentine's suggestion is very good and directly answers your question, seems to me.

But I would probably never write a sub that operates on the entire controls collection of a form. The reason is that it's actually quite inefficient to do so. But whether or not it's appropriate depends on how often and when you're walking that collection.

If you're walking it once in the form's OnLoad event (you wouldn't want to do it in the OnOpen because the data-bound properties of the controls aren't guaranteed to be fully initialized at that point -- you can still operate on format properties, though -- but everything's ready to go by the time the OnLoad event fires), that's no big deal, and passing that collection to an outside subroutine would be appropriate.

But if you're walking it for every record (say to hide/reveal controls, or to initialize criteria fields in an unbound query-by-form interface), then you will noticeably improve your form's performance by using one or more custom collections having much smaller numbers of items to loop through than are in any normal form's controls collection. Then you could rewrite Tim's code to use a custom collection, or, for that matter, one could still use the Object variable above and pass it a custom collection as well (and still remain able to pass it a form's controls collection).

Basically, what you do is initialize the collection in the form's OnLoad event. I normally write a private subroutine to do that so I can re-initialize should a code reset happen:

  Private Sub SetupCollections()
    If mcolCriteria.Count = 0 Then
       Call PopulateCollections(Me, mcolCriteria, "Criteria")
    End If
  End Sub

  Public Sub PopulateCollections(frm As Form, pcol As Collection, strTag As String)
    Dim ctl As Control

    For Each ctl In frm.Controls
      If ctl.Tag = strTag Then
         pcol.Add ctl, ctl.Name
      End If
    Next ctl
    Set ctl = Nothing
  End Sub

In this case, my method for determining which controls get added to the collection is to set the Tag property of those controls. You could also do something like:

  Public Sub PopulateCollections(frm As Form, pcol As Collection, intControlType As AcControlType)
    Dim ctl As Control

    For Each ctl In frm.Controls
      If ctl.ControlType = intControlType
         pcol.Add ctl, ctl.Name
      End If
    Next ctl
    Set ctl = Nothing
  End Sub

To use this, you could, for instance, create a collection of Nullable controls like this:

  If mcolControlsNullable.Count = 0 Then
     Call PopulateCollections(Me, mcolControlsNullable, acTextBox)
     Call PopulateCollections(Me, mcolControlsNullable, acComboBox)
     Call PopulateCollections(Me, mcolControlsNullable, acListBox)
   End If

For Boolean controls:

  If mcolControlsBoolean.Count = 0 Then
     Call PopulateCollections(Me, mcolControlsBoolean, acCheckBox)
  End If

For option groups or other controls having a default value:

  If mcolControlsWithDefaults.Count = 0 Then
     Call PopulateCollectionsWithDefaults(Me, mcolControlsWithDefaults, acTextBox)
     Call PopulateCollectionsWithDefaults(Me, mcolControlsWithDefaults, acComboBox)
     Call PopulateCollectionsWithDefaults(Me, mcolControlsWithDefaults, acListBox)
     Call PopulateCollectionsWithDefaults(Me, mcolControlsWithDefaults, acCheckBox)
     Call PopulateCollectionsWithDefaults(Me, mcolControlsWithDefaults, acOptionGroup)
  End If

  Public Sub PopulateCollectionsWithDefaults(frm As Form, pcol As Collection)
    Dim ctl As Control

    For Each ctl In frm.Controls
      If Len(ctl.DefaultValue) > 0 Then
         pcol.Add ctl, ctl.Name
      End If
    Next ctl
    Set ctl = Nothing
  End Sub

  Private Sub SetControlValuesFromDefaults(pcol As Collection)
    For Each ctl in pcol 
      ctl = ctl.DefaultValue
    Next ctl 
  End Sub

And for the other collections:

  Public Sub SetControlValues(pcol As Collection, varValue As Variant)
    For Each ctl in pcol
      ctl = varValue
    Next ctl
  End Sub

With this more complex set of collections, you would need something like this to initially populate them them:

  Private Sub SetupCollections()
    If mcolControlsNullable.Count = 0 Then
       Call PopulateCollections(Me, mcolControlsNullable, acTextBox)
       Call PopulateCollections(Me, mcolControlsNullable, acComboBox)
       Call PopulateCollections(Me, mcolControlsNullable, acListBox)
     End If
    If mcolControlsBoolean.Count = 0 Then
       Call PopulateCollections(Me, mcolControlsBoolean, acCheckBox)
    End If
    If mcolControlsWithDefaults.Count = 0 Then
       Call PopulateCollectionsWithDefaults(Me, mcolControlsWithDefaults, acTextBox)
       Call PopulateCollectionsWithDefaults(Me, mcolControlsWithDefaults, acComboBox)
       Call PopulateCollectionsWithDefaults(Me, mcolControlsWithDefaults, acListBox)
       Call PopulateCollectionsWithDefaults(Me, mcolControlsWithDefaults, acCheckBox)
       Call PopulateCollectionsWithDefaults(Me, mcolControlsWithDefaults, acOptionGroup)
    End If
  End Sub

...then you'd want a sub to initialize the control values:

  Private Sub InitializeControls()
    Call SetControlValues(mcolControlsNullable, Null)
    Call SetControlValues(mcolControlsBoolean, False)
    Call SetControlValuesFromDefaults(mcolControlsWithDefaults)
  End Sub

...so that you could then set everything up in your form's OnLoad event:

  Call SetupCollections()
  Call InitializeControls()

Now, of course, there are less convoluted ways to do this. You might want to have your initialization routine walk the controls collection just once:

  Private Sub SetupCollections()
    Dim ctl As Control

    For Each ctl in Me.Controls
      If Len(ctl.DefaultValue) > 0 then
         mcolControlsWithDefaults.Add ctl, ctl.Name
      Else
         Select Case ctl.ControlType
           Case acTextBox, acComboBox, acListBox
             mcolControlsNullable.Add ctl, ctl.Name
           Case acCheckBox
             mcolControlsBoolean.Add ctl, ctl.Name
         End Select
      End If
    Next ctl
    Set ctl = Nothing
  End Sub

A way to eliminate the initialization routine would be to use custom properties to return the collections, using internal static variables that would be re-initialized as needed. However, this would mean multiple walks through the controls collection, so it's not as efficient:

  Private Property Get colControlsNullable() As Collection
    Static colNullable As Collection

    If colNullable.Count = 0 Then
       Call PopulateCollections(Me, mcolControlsNullable, acTextBox)
       Call PopulateCollections(Me, mcolControlsNullable, acComboBox)
       Call PopulateCollections(Me, mcolControlsNullable, acListBox)       
    End If
    Set colControlsNullable = colNullable
  End Property

Unfortunately, using the static variable, while nicely avoiding module-level variables, means that your inialization routine gets less efficient, since there's no way for an outside initialization routine to utilize these static variables to populate everything with one walk through the controls collection.

So, I don't use custom properties for these collections, even though I wish I could. On the other hand, if I have only one custom controls collection, I would do that.

Anyway, I've rambled on far to long and with way too much convolution, and probably all that air code is filled with errors...

Answer 4

I'm taking a different path now. In my tab control, I only have subforms. So I'm taking a play from OOP and made a function for each subform called EnableForm. The subform can now handle whatever it needs to do on itself. In the form that contains the tab control, I just iterate over the pages of the tab control, see if the page contains a subform and then call the EnableForm function.

It's dirty as all heck but it works and I will document it in the code. Something this thing lacked (and most of the other access DBs here) from the get go.