Access VBA Referring to Forms and Controls
by FMS Development Team
The cornerstone of any Microsoft Access application is the form.
Forms are used to display and edit data, and to call other forms.
Controls are the building blocks of a form. As an Access user and
developer, you need to have a good understanding of how to reference the
forms and controls you create. This article outlines the methods
used to refer to forms and controls from different parts of Access.
First we cover the general method of referring to forms and
controls, and then we the particulars for macros, queries, and
To refer to objects, you need to understand two concepts: System
Objects and Identifier Operators. Although they have big names, they are
fairly easy to understand. Access has several pre-defined objects that
contain a group of your objects. These are called System Objects. The
Forms system object contains all the forms that are currently open. The
Reports system object contains all the reports that are currently open.
Whenever you refer to a form or control, you use the Forms system object
by placing the word Forms at the beginning of the reference.. This tells
Access that you are referring to a form, or a control on a form. A good
analogy is the Access Database Container. This is the window that
appears every time you start Access. It contains all the objects in your
database. You click on the desired object type and a list of available
objects appears. The Database Container contains each object in your
database. System objects are similar in that they contain objects, but
you won't see a window form them. They are virtual containers that only
exist in memory.
System objects allow
you to refer to an object that may have the same name as an object of a
different type. For example, if you have a form and report that are both
named "Invoice," Access would not be able to tell which one you're are
referring to without using the a system object. If you prefaced your
reference with Forms system object, Access knows you are talking about
the Invoice form. By prefacing the same reference with the Reports
system object, Access knows you are talking about the Invoice report.
Another useful system
object is called "Screen". This object, used in conjunction with the
ActiveForm and ActiveControl properties, allows you to refer to the form
or control that is currently active without knowing the object's name.
are the shorthand of object oriented systems. These are the ! and .
symbols that you may have seen in macros or queries. These operators
show that one element of an expression belongs to another. You use the !
operator (also known as the "bang" operator) when you are referring to
an object that you created, such as a field or form. You use the .
operator (also known as the "dot" operator) to refer to a
control name or something that
Microsoft Access created such as a property. Generally, you'd like to
use a . over ! because a . provides compile time validation which can
catch typos and invalid references. The bang operator ! is only
evaluated when the code is executed and would cause a runtime error.
Note: To ease
migration from Visual Basic to Visual Basic for Applications (VBA), Access allows you to use
the dot operator in place of the bang operator. However, this is not
recommended because Access will always run your application faster if
you use the bang operator to refer to your objects. This is especially
true in your Access Basic programs. You can also use the dot operator in
place of the bang operator when referring to fields in SQL statements.
When you create
objects in Access, it is generally a good idea to use some form of
naming convention. Give your object names a prefix that symbolizes the
type of object. For example, forms should be named "frmCustomer", not
"Customer". Give controls the "ctl" prefix, as in "ctlLastName". Using
naming conventions will make it easier for your maintain your
application because you can tell an object's type from its name.
naming convention is the currently accepted standard for Microsoft
Access. While still in formative stages, it provides useful rules for
naming your objects. In the examples in this article, I will use an
extension of this standard.
Forms and Controls
Now that you have
seen the basic concepts, let's look at some examples. To refer to a
form, the form must be open. To refer to a control on a form, the
control must already exist. Use the bang operator in the following way:
refers to an open
form named "frmCustomer"
refers to the control
named "ctlAddress" on the "frmCustomer" form
If an object name
contains spaces, or does not otherwise conform to the Access naming
conventions, you must enclose the name in square brackets. For example,
if you want to refer to the Zip Code control on your form, you would
Access provides a
shortcut that may save you some time. You can refer to any field in the
table the form is based upon. For example, if you have the frmCustomer
form open, and it is based on the tblCustomer table, you could refer to
the Comments field even if it doesn't appear on the form:
Refers to the
Comments field in the table the form is based on (this assumes that you
don't have a control of the same name. If you had a control named
Comments, this expression would refer to the Comments control, not the
Comments field. Control names take precedence over field names)
You'll recall that
the Screen system object allows you to refer to the form or control that
is currently active. We can use this with the ActiveForm and
ActiveControl properties to refer to the currently active form and its
controls. We use the dot operator because we are referring to
refers to the Zip
Code control on the currently active form
refers to the control
that currently has focus on the currently active form.
You should generally
avoid using this method in your macros unless you are sure the desired
form will be active when the reference is made. It can be easy to lose
track of which form is active! Also, when you are stepping through your
module code with the debugger, the module window will be active,
and the Screen system object will not work. This can make code that uses
the Screen system object difficult to test.
Controls on Subforms
If your form has a
subform placed on it, you can refer to the subform and its controls
using an extension of the SystemObject.FormName syntax. To Access, a
subform is just another control. To refer to it, use the name of the
refers to the
subfrmOrders subform on the frmCustomer form as a control.
To refer to a control
on a subform, use the Form property to tell Access that you are
referring to the subform as a form, not as a control. Using the Form
property gets you into the subform.
Forms("frmCustomer").subfrmOrders.Form.ctlStateTax.Visible = False
refers to the
ctlStateTax control on the subfrmOrders subform or the frmCustomer form.
Objects in Access
have predefined properties that control their behavior and appearance.
Knowing how to refer to these properties opens up many possibilities in
your application. As with controls, you will generally want to read the
value of a property, or write a value to the property. Unfortunately,
many properties in Access are read-only during runtime. This means that
you cannot change the value of a property while viewing the form. You
must open the form in design mode to change most properties. While this
can be fairly restrictive, a few important properties can be changed at
runtime. Of these, the Visible and Locked properties are particularly
Since properties are
created by Access, you refer to them using the dot operator instead of
the bang operator.
refers to the Record
Source property of the frmCustomer form, returning the name of table, or
the query that the form is based on
refers to the Visible
property of the ctlAddress control on the frmCustomer Form, returning
True if the control is visible, or False if it isn't.
refers to the Locked
property of the ctlStateTax control on the subfrmOrders subform of the
frmCustomer Form, returning True if the control disallows additions or
changes to data, or False if additions or changes are allowed.
Macros allow you to
automate many of the complex and repetitive tasks that you perform using
a database. Using macros, you can control the data entry process, find
and filter records, print reports, and customize the overall operation
of your application. Many of the macro commands you use will need to
refer to a form or control that you have placed on a form. For example,
let's say you want to display a dialog box if the value in the
ctlOrderAmount control is greater than 5000. Attach the following macro
to the ctlOrderAmount control's BeforeUpdate property:
You can also refer to
properties of forms and controls from your macro. For example, you could
make the ctlZip Code control disappear if an order was for a foreign
country. Use the IsNull function to see if the value in the ctlCountry
control is blank, and if it is the SetValue action sets the Visible
property of the ctlZip Code control to No. This will cause the control
to "disappear". Attach the following macro to the ctlCountry control's
Notice that you
didn't have to specify the full Forms("frmCustomer")!ctlCountry syntax in
the macro's Condition. In a macro, you can refer to a control name
without using the Forms system object, or the form name. Access knows
that you are referring to the form from which the macro was run. This is
an important shortcut: when a macro is called from a form, references to
controls in that macro will assume that the control being referenced is
on the form the macro was called from.
You can refer to forms and controls in your queries. This is useful if you want to use
the value of a control on a form as criteria for the query. Let's say
you have a button on your form named "Find Customer". When you press
this button, you want Access to run a query that finds all records from
the Orders table where the State is equal to the value of the State
control on the current form. First, create a query on the Orders table
that references the State control on the form. In Query design, enter
the following line in the criteria cell for the State field:
or you could enter it directly into the SQL statement:
SELECT * FROM Orders WHERE [State]=Forms("Customer")!State
This technique can be useful if you want to implement a Query by Form application. The user
could enter search criteria into an easy-to-use form, and your macro or
module would then translate the input into a query and execute it.
Access VBA Modules
Modules are the place where object references really become powerful. You have more
flexibility than in any other part of the Access. When you find that you
can't do what you want in a macro, moving to VBA modules will open up a
whole new world of design.
In Access modules, you can refer to forms and controls using direct references as discussed
above, or by using object variables. To refer to an object directly,
simply follow the same rules you would for a macro. The following
function displays a dialog box if the ctlOrderAmount control contains an
amount greater than 5000.
If Forms("frmCustomer").ctlOrderAmount > 5000 Then
MsgBox "Orders larger than $5000 must be confirmed!"
You can also assign a
value to a control. This function puts the value 12.50 in the
ctlOrderAmount control of the currently active form:
Screen.ActiveForm.ctlOrderAmount = 12.50
While direct reference works, it is best to use object variables to refer to forms
and controls. By using object variables, you assign a reference to a
form or control only once. This can reduce typing errors. You can also
assign shorter names to your objects, making your code easier to write
and maintain. Finally, using object variables makes you program run
faster. If the first two reasons didn't convince you, this one should.
An object variable is
different from a normal (numeric, string, etc.) variable in that it
contains a reference to an object, not the actual object itself. This is
an important concept: if you create multiple object variables and assign
them to a single object, the variables all refer to the same object,
they are not multiple copies of the object.
two object variable types that are useful when referring to forms and
controls. The Form variable type is used to refer to a form. The Control
variable type is used to refer to a control on a form. There are two
steps necessary to use object variables. You first declare the object
variable using the DIM statement. This creates the object variable,
which doesn't yet refer to any specific object. You then point the
variable to a specific object using the SET statement. You must use both
of these statements before an object variable will refer to an object.
The following example
is equivalent to the earlier function that displays a dialog box except,
it uses Object Variables.
Dim frmCust as Form
Dim ctlAmount as Control
Set frmCust = Forms("frmCustomer")
Set ctlAmount = frmCust.ctlAmount
If ctlAmount > 5000 Then
MsgBox "Orders larger than $5000 must be confirmed!"
In this example, the second and third lines use the Dim statement to declare object variables
of the Form and Control types. These variables will hold references to
the form and control. The next two lines assign real objects to the
object variables. Note that when we SET the control variable, we point
it to the frmCust object variable, instead of directly referencing the
Forms system object. For the rest of the function, whenever you want to
refer to the ctlAmount control, use the ctlAmont variable.
You can refer to subforms in much the same manner. The following function sets the value
of the ctlStateTax control on the subfrmOrders subform of the frmCustomer form to 100.
Dim frmCust as Form
Dim subfrmOrders as Control
Dim ctlStateTax as Control
Set frmCust = Forms("frmCustomer")
Set subfrmOrders = Forms("frmCustomer").subfrmOrders
Set ctlStateTax = subfrmOrders.Form!ctlStateTax
ctlStateTax = 100
Referring to Properties Using Object Variables
Once you have a form or control object variable set, you can refer to its properties using
standard dot notation. Remember that not all properties can be changed
while you are viewing the form: you must open the form in design mode to change most properties.
The following function opens the Orders form, and makes the StateTax and FederalTax fields invisible:
Dim frmOrders as Form
Dim ctlFedTax as Control
Set frmOrders = Forms("Orders")
Set ctlStateTax = frmOrders.ctlStateTax
Set ctlFedTax = frmOrders.ctlFederalTax
ctlStateTax.Visible = False
ctlFedTax.Visible = False
Using Variables in References
Another strength of object variables is that you can use normal variables in place of
literal text to refer to a form or control. For example, let's say you
wanted to display the names of all the controls on a form. Without
object variables, you would have to hard-code the name each control in
your module. What if you don't know the name of all controls? This
problem can be easily solved using a integer variable in place of a
control's name. When you use a variable in an object reference, surround
the variable name in parentheses"()". The following function uses this
technique to print the name of each control to the Immediate Window.
Dim frmCust as Form
Dim i as Integer
Set frmCust = Forms("Customer")
For i = 0 To frmCust.Count - 1
In this example, I declared a form variable and an integer variable. The form variable is
assigned to the Customers Form. The function then loops through each
control using the Count property which contains the number of controls
on the form. For each control, the Debug.Print method is used to display
the Control's name in the Immediate window. ControlName is a property of
the control that contains the control's name. Instead of explicitly
naming the control, a variable name is enclosed in parentheses. This has
the effect of referencing control number "i" on the form.
A more entertaining example shows how to use variables in references to produce simple
animation in your forms. Create a form and place an object frame control
on it that contains a graphic. Use the Cut and Paste functions from the
Edit Menu to duplicate that control in different positions on the form.
Set the Visible property for each of the controls to No. Save the form
under the name "Splash", and write the following function:
Dim frmSplash as Form
Dim i as Integer
Set frmSplash = Forms("Splash")
For i = 1 To 20
frmSplash(i).Visible = True
frmSplash(i-1).Visible = False
This function first initializes a form and integer variable. The form is opened and assigned
to the form variable. A loop is created to step through each control
(assuming there are 20 controls on the form), setting the Visible
property of the current control to true, and setting the Visible
property of the previous control to False. The RepaintObject command is
called to update the form display with each step in the loop. Run the
function from the immediate window, or from a macro. You will see a
control that appears to move from one location on the form to another.
You should now feel comfortable referring to forms and controls. If you understand the concepts of System Objects, Identifier Operators, and Object Variables, you have a good start in using these techniques. As you try these examples, you will
certainly find new ways to refer to forms and controls. Don't be afraid to experiment. Good luck!
Copyright © FMS Inc. All rights
reserved. This information may not be republished, reprinted or
retransmitted in any form without the express written permission of FMS
Inc. The information provided in this document is provided "as is"
without warranty of any kind.