Custom control collection – Creating and passing collections

If you have worked with Access for a while, you have probably at one point or another tried to loop through the controls collection on a form, like the below example:

Dim ctrl As Control
For Each ctrl In Me.Controls
   If ctrl.ControlType = acTextBox Then
      'do something
   End If

And as such, you are already aware that a form has a collection, that contains all the controls on said form. Now the code above works fine as is. But say we end up needing that kind of code several places in our application, and we end up writing the same piece of code over and over again?

Now VBA functions can pass more than simple strings and integers around, it can pass objects, and return objects. So we can pass our form object to a function, and have that function return our pre-filtered collection, like the below example:

Public Function controls_by_type(frm As Form, ctrl_type As AcControlType) As Collection
   'Returns a collection of all textboxes on form
   Dim return_collection As New Collection
   Dim ctrl As Control
   For Each ctrl In frm.Controls
      If ctrl.ControlType = ctrl_type Then
         return_collection.Add ctrl, ctrl.Name
      End If
   Set controls_by_type = return_collection
End Function


This means that our original code can now be boiled down to:

Dim txt As textbox
For Each txt In controls_by_type(me,acTextbox)
  'do something

Also note how in the original code, I had to use “dim ctrl as control” to accomodate for all the potential types of controls that might have been in the controls collection. But knowing that my function only returns textboxes, I can be more specific in my code, which in turns gives me better intellisense while developing.


Why don’t you try yourself, if you can create a function “controls_by_tag”, to return all controls on the form with a specific tag?

9 comments on “Custom control collection – Creating and passing collections
  1. Ben Sacheri says:

    I knew that functions can pass and return objects. I never considered using my own function as part of a For Each … In … statement. Very clever. Thanks for the tip!

  2. Giorgio says:

    Hi Anders, either way, when the control is a textbox you do something, so what’s the advantage of using a collection?

    • Alan Cossey says:

      Giorgio, the bit in the code which says, “Returns a collection of all textboxes on form” is a bit misleading. It ought to say “Returns a collection of all controls of the specified type” or something similar.

    • TheSmileyCoder says:

      I needed an example that was simple enough to explain in a few words. In reality I use code similar to this, to loop through a form, and set some properties for all textboxes, in the detail section, but I think I would have lost the reader, if I tried to explain that bit as well.

  3. Alan Cossey says:

    Nice one, Anders. I’ve added this to my library of useful routines (though I’ve dimmed and set my collection separately).

  4. Ray says:

    Thanks Anders – useful. Not something I’d expect to use very often, but nevertheless a handy snippet to store in the library. Cheers

  5. JohnWong says:

    Hi thesmilecoder, would like to say thanks for tips posted!

  6. Khaled says:

    When I typing aletter in combox for serch items I won’t auto select the characters in list items and highlight it by different color
    How ican do it?

    • TheSmileyCoder says:

      Sorry, I don’t have the time to go into the details. I suggest you visit a support forum like UtterAccess or ExpertsExchange.

Leave a Reply

Your email address will not be published. Required fields are marked *


This site uses Akismet to reduce spam. Learn how your comment data is processed.