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 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:
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:
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?