How to use the Controls collection in Access 2003 and VBA

Question

I have a hell of a time trying to figure it out.

I want to pass a collection of Controls 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 is what I call it:

 Call DoStuffToCollection(myPage.Controls, isLocked) 

myPage is a page control from TabControl. I went through the code to find out if there is anything in myPage.Controls.

For fun, I passed in Me.Controls (which would be a Form control assembly) instead of myPage.Controls and a type mismatch. Is there a difference between a form control collection and a version control collection? It drives me crazy.

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

[Edit] Just add some information. doStuffToCollection is a function that recurses that it is only supposed to lock related fields and disable any buttons in a tab control. Previously, I just managed to block it at the page level, but then I added a page with a button to it. The button has not been disabled on the page. I know about this http://allenbrowne.com/ser-56.html . I was not able to achieve its adaptation to my needs.

Answer 1

@Tim Lentine offer is very good and directly answers your question, it seems to me.

But I will probably never write a sub that works with the entire collection of form controls. The reason is that in fact it is completely ineffective. But whether it depends on how often and when you go through this collection.

If you pass it once in the form of an OnLoad event (you do not want to do this in OnOpen, because the binding properties of these controls are not guaranteed to be fully initialized at this point - you can still work in the format properties, but - but you're done to the fact that the OnLoad event is triggered), which does not really matter, and passing this collection to an external routine would be appropriate.

But if you run it for each record (say, to hide / open controls or initialize criteria fields in an unrelated query interface on a form), then you will noticeably improve the performance of your form using one or more user collections that have much fewer elements for cyclic passing than in any normal form control collection. You can then rewrite the Tim code to use the user collection, or for that matter, you can still use the Object variable above and pass it also to the user collection (and you can still pass the collection of form controls to it).

Basically, you initialize the collection in the form of an OnLoad event. I usually write a private routine to do this, so I can reinitialize if the reset code occurs:

  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 of determining which controls are being added to the collection is to set the Tag property for these controls. You can 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 can, for example, create a collection of Nullable controls, for example:

  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 parameter groups or other controls with 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 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 collection of collections, you'll need something like this to populate them initially:

  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 want 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 can set everything in your form. OnLoad event:

  Call SetupCollections() Call InitializeControls() 

Now, of course, there are less confusing ways to do this. You might want your initialization procedure to go through a collection of controls only 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 procedure would be to use custom properties to return collections using internal static variables that would be reinitialized as needed. However, this would mean multiple walks through a collection of controls, so it is not so effective:

  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 a static variable while avoiding module-level variables well means that your initialization routine becomes less efficient, since there is no way to use these static variables to fill out just one pass through the collection controls for an external initialization routine.

So, I do not use custom properties for these collections, although I would like to. On the other hand, if I have only one collection of user controls, I would do that.

In any case, I am too long and with an excess of convolution, and probably all this air code is filled with errors ...

Answer 2

It seems strange that you cannot directly pass the page management collection. This may be related to the fact that the Pages collection itself is a special type of management collection .

Another alternative would be to declare the topCtlList parameter as Object instead of controls. This 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

One option is to convey the form as a form object using "I". Obviously, there is a difference in what I'm not sure.

Also pull out the page object in the help. You can pass the page object.

Answer 4

Now I am going a different way. In the tab control, I only have subforms. Therefore, I take the game from OOP and execute a function for each subform called EnableForm. Now the subordinate form can now handle everything that it should do on its own. In the form containing the tab control, I simply iterate over the pages of the tab control, see if the page contains a subform, and then calls the EnableForm function.

It is dirty, like everyone else, but it works, and I will write it in code. Something that was missing (and most other access databases here) from receiving.