Special Edition Using Microsoft Office Access 2003

One of the reasons for occasional use of the term Access VBA in this book is that Access defines its own set of objects and uses specialized VBA syntax to refer to many Access objects. Although Form objects are common to most Office 2000+ members as well as Visual Basic, a subform (a form embedded in a form) is unique to Access. You find Report objects and subreports only in Access. The syntax for referring to a subform or subreport and for referring to controls contained in a subform or subreport is unique to Access. Even if you're an experienced Visual Basic programmer, you must become acquainted with the object reference syntax to write VBA code and refer to objects that are unique to Access.

Referring to Open Forms or Reports and Their Properties

You can refer to a form or report only if it's open. Access uses the Forms and Reports collections to keep track of which forms and reports are open. The Forms collection is the set of open forms, and the Reports collection is the set of open reports. Because Access lets you use the same name for a form and a report, you must distinguish between the two by specifying the collection. The syntax for the reference is the collection name followed by the exclamation point operator (!), more commonly called the bang operator, and the name of the form or report:

Forms![Form Name] Reports![Report Name]

Use the bang operator (!) to separate the collection name from the name of an object in the collection. You need to use the square brackets ([...]) to enclose object names that include spaces or other punctuation that's illegal in VBA statements or object names that duplicate VBA reserved words.

A Form or Report object has properties that define its characteristics. The general syntax for referring to a property is the object name followed by the dot (.) operator and the name of the property:

Forms![Form Name]. PropertyName Reports![Report Name]. PropertyName

Use the dot operator to separate the object's name from the name of one of its properties. For example, Forms!frmProducts.RecordSource refers to the RecordSource property of the open frmProducts form. You can get or set the value of the RecordSource property with the following two VBA statements:

strSource = Forms!frmProducts.RecordSource Forms!frmProducts.RecordSource = strSource

If you add the .Value qualifier to RecordSource, you receive an "Invalid Qualifier" error.

To get or set the value of a form property in the form's own class module, you use the Me selfidentifier, as in:

strSource = Me.RecordSource Me.RecordSource = strSource

The Me self-reference is valid only for the instance of the form open in Form view. Thus, you can't use the two preceding statements in the Immediate window unless you create a breakpoint in your code, open the form in Form view, and then execute the procedure that contains the breakpoint. Figure 28.27 shows the Immediate window opened by a breakpoint and set at the first active line of code of the ReviewProducts_Click subprocedure of Northwind.mdb's Suppliers form. In Break mode, typing ? Me.RecordSource returns Suppliers the name of the table to which the Suppliers form is bound. If you press F8 repeatedly to step through the code, after you pass the DoCmd.OpenForm... statement for the Product List form, you can use the Forms!... syntax described in the next section to test the property values of control objects on the Product List form.

For detailed instructions on using breakpoints, see "Adding a Breakpoint to the IsLoaded Function," p. 1183.

A form's properties window lists the form properties that you can set in Design view. Forms also have properties that you can't set in Design view and that don't appear in the property window, such as the default Form property. The Form property refers to the collection of controls on a form. Similarly, a report's default Report property refers to the collection of controls in a report.

Referring to Controls and Their Properties

The following is the general syntax for referring to a control on a form or report:

Forms![FormName].Form![ControlName] Reports![ReportName].Report![ControlName]

As before, the bang operator separates the collection name from the object name. The Form property is the default property that Access assumes for a form; therefore, you need not include the Form property explicitly in the reference.

The following expression is the short-form identifier syntax for a form control:

Forms![FormName]![ControlName]

Similarly, the following is the full identifier syntax for a report control:

Reports![ReportName]![ControlName]

For example, Forms!frmProducts!ProductName refers to the ProductName control on the open frmProducts form.

The syntax for referring to a control's property value includes the reference to the control, followed by the dot operator, and then followed by the property name:

Forms![FormName]![ControlName].[PropertyName] Reports![ReportName]![ControlName].[PropertyName]

For example, Forms!frmProducts!ProductName.Visible refers to the value of the ProductName control's Visible property.

A control also has a default property. The default property of a text box is the Text property. To refer to the value in the ProductName text box control in the last example, you could use any of the following equivalent references:

Forms!frmProducts.Form!ProductName.Text Forms!frmProducts!ProductName.Text Forms!frmProducts.Form!ProductName Forms!frmProducts!ProductName

Note

Notice that the last two expressions refer both to the control's text value and to the control itself. The .Text qualifier isn't required, but adding the name of the default property is a good programming practice and complies with VBA.NET programming rules.

When you refer to a control on the active form or report, you can use a shorter version of the reference and refer to the control as follows:

[ControlName]

Likewise, you can refer to the control property as follows:

[ControlName]. PropertyName

Normally, you can use either the short or full syntax to refer to a control on the active form or report. However, in some cases, you must use the short syntax. For example, the GoToControl action's ControlName argument requires the short syntax. You can explicitly refer to a control on the form of the class module with Me !ControlName statements. When you refer to a control on a form or report that's not the active object, you usually must use the full identifier syntax.

Referring to Controls on a Subform or the Main Form

The key to understanding the syntax for referring to a control on a subform is to realize that the subform is a form that's bound to a subform control on the main form. The subform control has the usual attribute properties that control its display behavior, such as size and visibility, as well as linking properties that relate the records in the subform to records in the form, including the SourceObject, LinkChildFields, and LinkMasterFields properties. In addition, the subform control has the Form property. A subform control's Form property refers to the controls contained on the subform.

The following is the syntax for referring to the subform control:

Forms![FormName]![SubformControlName]

The syntax for referring to a control on a subform bound to a subform control is as follows:

Forms![FormName]![SubformControlName]![ControlName]. PropertyName

When the form is active, the following short syntax refers to a control on a subform of the active form:

[SubformControlName]![ControlName]

The Form property of the subform, required in Access 95 and earlier when referring to controls on a subform, now is the subform control's default property, so you don't need to include it in the reference. Normally, you use the subform's name as the name of the subform control. For example, if sbfSuppliers is the name of a form bound to a subform control also named sbfSuppliers on the frmProducts form, the following is the full syntax for referring to the SupplierName control on the subform:

Forms!frmProducts!sbfSuppliers[.Form]!SupplierName

The short syntax is as follows:

sbfSuppliers[.Form]!SupplierName

When the focus is in a subform's control, you can refer to a control on the main form by using the control's Parent property. The Parent property refers to the collection of controls on the main form. In the previous example, to refer to the ProductName control on the main form from VBA code in the class module of a subform, use the following syntax:

Parent!ProductName

All the preceding syntax examples in this section apply to reports and subreports; just change Forms to Reports and Form to Report.

Using Alternative Collection Syntax

An alternative to the CollectionName! ObjectName syntax is to specify CollectionName and supply ObjectName as an argument value:

Forms("frmProducts")!sbfSuppliers!SupplierName

The advantage of the argument method is that you can substitute a String variable for the literal argument value:

Forms(strFormName)!sbfSuppliers!SupplierName

You also can pass a 0-based Long value to specify the ordinal (position) of the object in the collection:

Forms(2)!sbfSuppliers!SupplierName

Passing the ordinal value, however, isn't a safe programming practice because the ordinal position of objects in a collection change as you add or delete members.