Introduction.
Beginner VBA learners are often confused about how to correctly reference controls on a Form or on Subforms. The challenge becomes even greater when a Subform contains another Subform. In such cases, specifying the full address of the control on the innermost Subform correctly is essential if you want to read the control’s contents or set its value directly through VBA.
How do we build the complete object address? Should each element be joined with a dot (.), with an exclamation mark (!), or with a combination of both?
To answer these questions, let’s start with some simple examples of using dots and exclamation marks (or bang ! symbol) in object address specifications.
For demonstration purposes, I’ve created a sample Main Form with two Subforms. The second subform is nested inside the first subform. Below, you can see both the Design View and the Form View images for reference.
Sample Form in Normal View
Dots and Exclamation Symbols.
General Usages of the dot (.) and bang symbol (!) in object references.
- A dot (.) - after an object name to access its methods or properties.
- The exclamation mark (!) - after an object name, refers to the sub-object or control of the Top-level Object.
Sample Forms:
Main Form: frmMain Sub-Form of Main Form: frmSubForm Inner Sub-Form on frmSubForm: frmInnerSubForm Text Control on frmInnerSubForm: Text7 Another Unbound Text Control on frmInnerSubForm: Text3
All three forms are designed without linking to any Table (i.e., The Form's Record Source property is empty). All the Text controls on the Form are unbound.
You may design three sample forms with a few unbound text boxes and set them up one inside the other, and open them in the normal view. Open the VBA Editing Window and open the Debug Window (Ctrl+G). Now you are ready to try out the following examples after typing the code directly in the Debug Window and pressing the Enter Key.
Practical Exercises.
First, we will try to read the contents of the Text7 textbox (i.e., 'Innermost Form') from frmInnerSubForm and display the result in the Debug Window.
? Forms!frmMain!frmSubForm.Form!frmInsideSubForm.Form!Text7
Printed Result is: Innermost Form
There was an expression ="Innermost Form" inside the Text7 Text box, which is displayed in the Debug Window.
Forms!frmMain!frmSubForm.Form!frmInsideSubForm.Form!Text3 = "MS Access"
Result: MS Access
The Text3 textbox is in the Innermost Form and its Conttrol Source Property is loaded with the text: MS Access.
Check the opened Form after executing the above statement in the Debug Window.
Let us examine each part of the above command joined by an exclamation mark or a dot. I call this a command because we directly execute this program statement in the Debug Window, with the? (Print VBA command).
The Forms collection contains all forms currently loaded in memory. Each form opened is indexed in the order it was loaded into memory—the first form opened has index 0, the second has index 1, and so on.
If your main form contains subforms, those subforms are treated as controls of the main form, just like combo boxes, labels, or text boxes. They do not appear as separate entries in the Forms collection.
To display the name of the first form loaded in memory, use the following command in the Immediate (Debug) Window:
This prints the value of the form’s Name property for the form at index 0 in the collection.
? Forms(0).Name
Result: frmMain - Form's Name property value is printed.
? Forms(0).Width
Result: 21225 (You may get a similar number.)
This value represents the width of the form measured in Twips.
📏 Conversion note:
-
1 Inch = 1440 Twips.
When you set the Width property of a form (whether in Inches, Centimeters, or any other regional measurement unit), VBA automatically converts that value internally into Twips.
If you know the Form's name, then the above command can be given as below:
? Forms("frmMain").Width In this form of the command, the Form's name can be given as a string in parentheses.
OR
? Forms!frmMain.Width
In this command, immediately after the Forms Collection name, we have used the symbol (!) to give the name of the next level of the object of the Forms collection object, not a property or method. The next item, Width, is a property of the frmMain object, so a dot is required, not the other symbol. The bang (!) symbol is given in parentheses for legibility only.
Note: You cannot use the symbol (!) in place of the dot (.) to address the Width or Name, or any other property of the Form. There are over 200 properties for a form. You can display the names of all the properties by typing the following Code, on one line, in the Debug Window and pressing the Enter Key:
For j=0 to Forms(0).Properties.count-1:? j,Forms(0).Properties(j).Name:Next
In the above statement, the Count() Method of the Forms Property is called, takes a Count of the first Form Properties, and prints the Name of each one.
Take a Listing of the Property Names.
OR
For j=0 to Forms("frmMain").Properties.count-1:? j,Forms("frmMain").Properties(j).Name:NextOR
For j=0 to Forms!frmMain.Properties.count-1:? j,Forms!frmMain.Properties(j).Name:Next
Note the usage of Forms("frmMain") and !frmMain, two different ways to refer to the frmMain object in the open Forms Collection. In both cases, the form's name is given immediately after the open Forms collection name. If the frmMain form is not open at the time of executing this statement, then it ends up in an Error. Forms(0) refer to any form that is open first in memory. It will fail only when no form is open at the time of executing this statement.
? Forms.Count
The Count() method of the open Forms Collection Object will print the count of open Forms
We will explore this topic further in the next Post.
No comments:
Post a Comment
Comments subject to moderation before publishing.