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:

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:

 

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

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?

7 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!

Leave a Reply

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

*