Referencing Forms, Subforms, and Sub-subforms

 

Introduction

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.

 

Syntax

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:

Forms!Form1!Text1.Value
Forms("form1")("Text1").Value
Forms(0)(0).Value

 

Bang Syntax

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).

Forms!Form1.Controls!Text1.Value

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:

Forms!Form1!Text1

But if I wanted to reference the caption of the textbox, I'd have to explicitly name it.

Forms!Form1!Text1.Caption

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:

Forms!Form1!Text1
Forms.Form1!Text1

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.

 

Paren Syntax

The Paren Syntax simply surrounds each object with parentheses and the name of the object is surrounded in quote marks without bangs or dots:

Forms("form1")("Text1").value

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.

 

Index Syntax

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
   Debug.Print Forms(0)(i).Name
Next i

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
   Next j
Next i

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:

Forms!Form1!Text1
Forms!Form1("Text1")
Forms!Form1(0)
Forms.Form1!Text1
Forms("Form1")!Text1
Forms!Form1.Controls(0).Value
Forms(0)(0).Value
Forms(0)("Text1").Value
Forms!Form1.Controls!Text1.Value

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!

for Forms!Form1.

Me!Text1

Me.Text1
Me!Text1.value
Me.Text1.value
Me.Controls!Text1
Me(Controls)("Text1").value

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.

To see specific examples of referencing subforms and subsubforms, download my sample: SubformReference.mdb from my website (www.rogersaccesslibrary.com).