Microsoft Access VBA Referring to Forms and Controls

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.

Identifier Operators 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.

The Leszynski/Reddick 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.

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 properties:

Screen.ActiveForm![Zip Code]

refers to the Zip Code control on the currently active form

Screen.ActiveControl

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.

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

Forms("frmCustomer").subfrmOrders

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

Since properties are created by Access, you refer to them using the dot operator instead of the bang operator.

Forms("frmCustomer").RecordSource

refers to the Record Source property of the frmCustomer form, returning the name of table, or the query that the form is based on

Forms("frmCustomer").ctlAddress.Visible

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.

Forms("frmCustomer").subfrmOrders.Form.ctlStateTax.Locked

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 BeforeUpdate property:

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:

Forms("Customer")!State

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.

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.

Function TestAmount()
  If Forms("frmCustomer").ctlOrderAmount > 5000 Then
    MsgBox "Orders larger than $5000 must be confirmed!"
  End If
End Function

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:

Function SetAmount()
  Screen.ActiveForm.ctlOrderAmount = 12.50
End Function

Object Variables

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.

VBA provides 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.

Function TestAmount()
  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!"
  End if
End Function

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.

Function SetStateTax()
  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
End Function

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:

Function SetOrderForm()
  Dim frmOrders as Form
  Dim ctlStateTax
  Dim ctlFedTax as Control
  
  DoCmd.OpenForm "Orders"
  Set frmOrders = Forms("Orders")
  Set ctlStateTax = frmOrders.ctlStateTax
  Set ctlFedTax = frmOrders.ctlFederalTax
  ctlStateTax.Visible = False
  ctlFedTax.Visible = False
End Function

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.

Function ShowControls()
  Dim frmCust as Form
  Dim i as Integer
  
  Set frmCust = Forms("Customer")
  For i = 0 To frmCust.Count - 1
    Debug.Print frmCust(i).ControlName
  Next
End Function

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:

Function Animate()
  Dim frmSplash as Form
  Dim i as Integer
  
  DoCmd.OpenForm "Splash"
  Set frmSplash = Forms("Splash")
  For i = 1 To 20
    frmSplash(i).Visible = True
    frmSplash(i-1).Visible = False
    DoCmd.RepaintObject
  Next
End Function

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!