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:
|
1 2 3 4 5 6 |
Dim ctrl As Control For Each ctrl In Me.Controls If ctrl.ControlType = acTextBox Then 'do something End If Next |
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:
|
1 2 3 4 5 6 7 8 9 10 11 |
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 Next Set controls_by_type = return_collection End Function |
This means that our original code can now be boiled down to:
|
1 2 3 4 |
Dim txt As textbox For Each txt In controls_by_type(me,acTextbox) 'do something Next |
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?
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!
Hi Anders, either way, when the control is a textbox you do something, so what’s the advantage of using a collection?
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.
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.
Nice one, Anders. I’ve added this to my library of useful routines (though I’ve dimmed and set my collection separately).
Thanks Anders – useful. Not something I’d expect to use very often, but nevertheless a handy snippet to store in the library. Cheers
Hi thesmilecoder, would like to say thanks for tips posted!
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?
Sorry, I don’t have the time to go into the details. I suggest you visit a support forum like UtterAccess or ExpertsExchange.