Handling Multiple Controls

Janz asked a question recently: I have to set 50 fields (text and color) How do I do this? See original Question and Answer here.

Handling Multiple Controls
Handling multiple controls in MS Access and many programming languages is counter intuitive. You have to think about the problem in a different way, and to help you do this I will give you a bit of background. Your Form is an object that can contain other objects, so your Form is also termed a “Container”, and the objects in the Form are called a "Collection" of objects.

Looping through the Collection
You can access the properties of these objects on your Form, (the objects in the collection) by looping through the collection and making a change to each object in the collection individually, (but the same change to every object)

Put them in a Sack
Imagine your keyboard represents the Form, and on this "keyboard form" there are keys, let's pretend the keys are actually text boxes with the name stamped on them --- A through Z so you have 26 named text boxes. If you collected all these keys together and put them in a sack, you could reach in and pull out a key, however you would never know which key you were getting, if you wanted to find a particular key (let's say "W") you would have to keep picking them out one at a time until you found the "W" key. You would pick the key up to look at it thinking what key is this? And there embossed on the key would be the letter "W" identifying the key. This is sort of how the collection works in MS Access, you have to look at each item (object) ask questions about it, (what are you) (what's your name) and then take action according to the answers you receive.

Typical example of the way you would do this:
Private Function fSetCap(strLblNumb As String, strLblCap As String)
'Name the labels
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acLabel Then
If Right(ctl.Name, 2) = strLblNumb Then ctl.Caption = strLblCap
End If
Next ctl
End Function 'fSetCap

How to Read the above Code:

Read this line of code: "For Each ctl In Me.Controls"

Like this:
For each control in me controls

In other words for each item, (key) in me (the form) controls, (the collection of controls)

The next line "If ctl.ControlType = acLabel Then"
interrogates the selected control asks are you a label? If it's not a label it skips around the loop and checks the next control.

If it is a label, then the code checks the last two digits in the controls name to see if they match the variable past to the code via "strLblNumb" if it finds a control with a match, then it changes the controls caption to the passed in string variable "strLblCap".

Works in many Languages
I know this seems a long winded way about going about it, but not only does it work within MS Access it works in many other programming languages, and it's a very powerful way of working because you can change all of the controls in one go, you can select groups of controls by various methods, by the contents of the tag property, by coding the control name as in the above example, you can change practically anything about the control, it's back color, you can enable or disable it, make it visible or invisible all sorts of things.

More information HERE from beginner level right up to some advanced stuff I hardly understand myself!