One of the powerful features of Microsoft Access is the ability to reference controls from other controls, in queries, and in code. And yet, while most beginning developers master referencing controls on forms relatively quickly, referencing controls on subforms and sub-subforms causes a lot of confusion. Most of this confusion is due to the way in which controls are named.
First, however, a word about naming conventions. I generally encourage the use of meaningful names for controls on a form. In this case, however, I am using generic names like Form1, Subform1, Subsubform1, subControl1, subsubControl1, Text1, and so forth. Objects that I create and name will have a number at the end (Text1). Form collections or properties (like Forms, Controls, Value, or SetFocus) will not have a number at the end.
There are three different, yet equally correct, syntaxes. For instance, suppose I want to reference the value of a textbox named Text1 on a form named Form1. All of the following work:
The Bang (!) syntax is the most commonly used syntax in Access, and is generally recommended in Microsoft documentation and articles. The Bang Syntax uses both bangs (!) and dots(.). Bangs are used to separate levels in the DAO hierarchy and dots serve to separate an object from its properties.
So, in the case of our textbox, "Forms" is the Access collection of forms. "Form1" is the name of the form I'm referencing. And "Text1" is the name of the control (textbox).
The bangs go between the object hierarchy levels. Form1 is a member of the Forms collection. Text1 is a member of the Controls collection of Form1. The dots separate the objects from their collections or properties: The Controls collection of the Form1 object, and the Value property of the Text1 object.
However, every object has a default property and it just happens that Controls is the default for a form and Value is the default for control objects. Therefore you don't actually have to reference them, so I can do something like this:
But if I wanted to reference the caption of the textbox, I'd have to explicitly name it.
As a general rule of thumb, Bangs (!) go before objects that you, the developer, name like forms, controls, and such. Dots (.) go before things that Access names, like properties and methods. For more on the difference between Bang and Dot, see my post: Bang Vs. Dot In DAO.
It is worthwhile to note that there are times when Bangs and Dots are interchangeable and at other times, it is not. For instance, in:
the Bang between "Forms" and "Form1" can be replaced with a Dot. However, the Bang between "Form1" and "Text1" can not. Best practice is to use the Bangs and Dots correctly to avoid any problems.
The Paren Syntax simply surrounds each object with parentheses and the name of the object is surrounded in quote marks without bangs or dots:
This syntax is most useful in programming where you want to use a variable in place of the object name:
Dim strControlName as String
Dim strFormName as String
strFormName = "Form1"
strControlName = "Text1"
Forms(strFormName)(strControlName).value = "Hello"
Notice the variables are NOT surrounded by quotes.
The Index Syntax is rarely used. It is most commonly used for programmatically looping through controls on a form. For instance, if I wanted to print out the names of all the controls on a form, I could do something like this:
Dim i As Integer
For i = 0 To Forms(0).Controls.Count – 1
I could list the names of all the controls on all open forms like this:
Dim i As Integer
Dim j As Integer
For i = 0 To Forms.Count – 1
For j = 0 To Forms(i).Controls.Count – 1
Debug.Print Forms(i).Name & ": " & Forms(i)(j).Name
The reality is, however, that there are better ways to do this by directly accessing the Forms Collection and the Controls Collection. Still, it's useful to know about because it can be used in conjunction with other syntax.
Mixing and Matching Syntaxes
The various syntaxes can be mixed and matched. For instance, all of the following work identically:
In general, however, unless you have a good reason for it, it's best to stick with the Bang and Dot syntax.
A Word About ME
The ME object is a special construct used in forms and reports. It refers to the form or report which has the Focus. If you are referencing objects from within the form/report, you can substitute Me. or Me!
It doesn't matter which.
If, however, you are referencing the control from outside the form/report -- say in a query -- then you must use explicitly reference the Forms collection and the Form name. That is: Forms!Form1.
For more on the ME object and using the Bang and the Dot in forms, see my blog post: Bang Vs. Dot in Forms.