Access 2002 VBA Handbook

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

 

Chapter 3: Introducing the Access Object Model

Susann Novalis and Dana Jones

December 2002

Applies to:
    Microsoft® Access® 2002

Buy this book

Summary: This article presents an excerpt from the book Access 2002 VBA Handbook by Susann Novalis and Dana Jones. Learn about the Access 2002 Object Model. (34 printed pages)

Contents

Relating Objects to Each Other
Introducing the Architecture of Access
Understanding the Access Application Object Model
Referring to Objects and Properties by Name
Using the Expression Builder to Create References
Summary

• Relating objects to each other
• Introducing the object hierarchies
• Understanding the Access Application object model
• Referring to objects and properties by name
• Using the Expression Builder to create references

When you automate a database operation, you create instructions that run when an object recognizes an event. You must understand which objects you can write instructions for, the events that an object recognizes, and how to write the instructions. The last chapter introduced you to the concept of an object as a thing you can use or change by running a VBA procedure. This chapter focuses on the specific objects—which objects are available, which properties can be changed, how the objects are related to each other, and how you identify an object when you write a program.

The Access object model is large and complex. The purpose of this chapter is to get you started with the model by introducing you to those objects and properties that are available in VBA programming.

Relating Objects to Each Other

You are familiar with the Database window objects of interactive Access: the tables, queries, forms, data access pages, and reports. We'll continue to use the word object informally and add a few more objects to our list: table fields, query fields, form controls, and report controls. When you work interactively with Access, you don't need to be concerned with how objects are related to each other; the Access user interface takes care of the relationships for you. However, to create programs that manipulate the objects, you need to understand how objects are related so you can use these relationships to refer to an object in a program.

Understanding groups of objects means understanding how they are tied together in relationships. Two kinds of relationships are obvious: some objects are similar to other objects, and some objects contain other objects.

Similar Objects

It is natural to group together objects with similar properties and behaviors. For example, it is natural to group the forms in a database, to group command buttons, or to group text boxes. A group of similar objects is called a collection.

Object collections

In Access, most of the objects are in collections. For example, a database has a single collection of tables that contains all of the tables in the database; each table has a collection of fields that contains all of the fields you have defined for the table; a database has a single collection of open forms; and each open form has a collection of controls that contains all of the controls you have placed on the form. Access begins the name of each type of object with a capital letter, such as Form, Report, Control, and Field objects. Access names a collection by adding the letter s to the name of the object type in the collection; for example, the Controls collection of a specific form contains the Control objects placed on the form. Access treats the collection itself as an object; for example, a Controls collection is an object that contains the Control objects for a specific form or report.

Another example of collections involves the distinction between a form that is open and one that is closed. An open form is a Form object, and the collection of open forms is the Forms collection. By contrast, a closed form is not a Form object and is not a member of the Forms collection—a closed form is just a closed form. Figure 3.1 depicts the Forms collection for the Expenses application when the Switchboard and the Expense Reports By Employee forms are the only open forms.

Figure 3.1. The Forms collection contains the open forms. Each open form has its own Controls collection containing the controls on the form.

Objects that are not in collections are singular objects. For example, the object named Application represents the Access application; the object named Screen represents the form, report, or control that currently has the focus. Each of these is a singular object, because Access has only one Application object and the one Screen object, as only one object has the focus at a time. A collection object is also a singular object; for example, there is only one Forms collection object in the application, and each Form object in the collection has only one Controls collection. Whether an object is a singular object or is in a collection becomes important when you refer to the object.

Classes and instances

Another way to look at groups of similar objects is to separate the definition of a group from the objects in the group. When you think of a group of objects with similar properties and behaviors, there are really two parts: the definition of the group, which is the class, and the objects themselves, which are the instances.

We use the word class to refer to the definition of a group: the class of forms or the class of text boxes, for example. A class is a blueprint for its objects. As a simple example, you can think of table Design view as representing the class of tables. When you create a specific table by defining table fields and setting table properties, you are creating an instance of the class.

As another example, the Text Box tool in the toolbox represents the text box class. When you use the Text Box tool to create a text box with a specific set of properties, you are creating an instance.

Objects Containing Other Objects

The second important relationship among objects is that of objects containing other objects. For example, a form contains its controls, and a table contains its fields. A table also contains its indexes, and each index contains its fields. In general, objects contain objects, which may contain other objects, and so on. The container relationship is a parent and child relationship; an object is the parent of the objects it contains, and an object is the child of the object that contains it. For example, a form is the parent of the controls it contains, and a control on a form is the form's child.

The different levels of container relationships can be shown as tiers in a hierarchy. For example, let's look at the hierarchy of container relationships for tables. In Access, a table object that you define in table Design view is called a Table object and is one of the data access objects managed by the database engine. A table contains four collections:

• A Table object in ADO has a Columns collection containing Column objects. Each Column object has a Properties collection containing a number of Property objects, such as field size, input mask, and default value. In the table's Design view, you will see Columns referred to as Fields, as shown in Figure 3.2a. Many of the Property objects appear as properties listed in the lower pane of Design view, but a Table object has other properties that are available only in code.
• If an index is set on an ADO table, the Table object has an Indexes collection, which contains Index objects. Each Index object in the collection has both a Properties collection, which contains Property objects, and a Columns collection, which contains Column objects. In turn, each of these Column objects has a Properties collection that contains Property objects for that column. In design view, the Indexes collection is represented by the Indexes dialog (see Figure 3.2b).
• A Table object in ADO has a Keys collection containing Key objects. There are three types of keys: foreign, unique, and primary. Foreign keys are those columns in a table that are related to rows in other tables. If a column in a table is assigned as a unique key, each record in the database must have a different entry for that column. Each table can have at most one primary key. Primary keys are the database's way of differentiating one record from another, so they must also be unique. In the Access interface, Key objects are represented within the Indexes dialog (primary keys have a key icon to the left; unique keys are displayed as indexes with the Unique property set to Yes). To see Foreign keys for a database, click Tools . . . Relationships (see Figure 3.2c).
• An ADO Table object has a Properties collection containing Property objects, as listed in the Table Properties dialog (see Figure 3.2d).

Figure 3.2. The Column objects (shown as Fields in Design view) for a table and the Property objects for a table field (a), the Property objects and the Column objects for a table index (b), foreign keys displayed as Relationships (c), and the Property objects for a table (d)

Figure 3.3 shows a partially expanded view of the Tables collection for the Expenses database. In this figure, the Employees table is expanded to show its four collections, which are expanded to show some of their members. In each case, one of the collection members is expanded to show its collections, which are expanded to show their members, and so on. The expanded views of container relationships quickly become large and overwhelming; the important concept here is the structure of the hierarchy.

You need to know the container relationships for all of the objects in Access, because when you write programs to manipulate the properties and behaviors of an object, you may need to refer to all of the objects that lie along a hierarchical path to the object.

Figure 3.3. A partially expanded view of the Tables collection for the Expenses database

Tip:   When working with ADOX library objects, you will encounter exceptions to the container relationships concept. Notably, some objects have properties that are not part of the Properties collection for those objects. For our purposes, we will refer to these properties as "intrinsic" properties, as they are part of the definition of the object. You will learn more about ADOX in Chapter 6, "Understanding the ADO Object Model."

Introducing the Architecture of Access

When you install Access, you actually install two major components: the Access Application layer and the Jet database engine. To work with Access projects on Microsoft SQL Server, you will also need to install the Microsoft SQL Server 2000 Desktop Engine.

The Application Layer

The Application layer consists of all the files necessary to control the user interface and all the files needed for writing and running VBA procedures. The Application layer contains the menu bars, toolbars, and windows for creating and viewing Database window objects.

When you create a database interactively, you work directly in the Application layer using the Design windows to create the individual tables, queries, forms, data access pages, and reports, as well as the macros and modules that fuse the objects into an application. Although you use the Application layer's interface to create all seven Database window objects, only data access pages, forms, reports, and modules are defined as Application objects. The tables and queries you create in the Access interface are data access objects.

When you create an Access project, you are essentially creating a client/server application that works with a database, such as Microsoft SQL Server. Instead of the Jet database engine, a project uses the Microsoft SQL Server 2000 Desktop Engine. Projects use 10 objects: tables, views, stored procedures, database diagrams, forms, reports, pages, data access pages, macros, and modules. A major difference between a project and a database is in the location of the actual data stored in tables. In a project, tables are stored on a server; in a database, tables are stored locally within Access itself. Once you connect to the server database, you can view, create, modify, and delete data, so working with an Access project is very similar to working with an Access database.

Note:   You must install the MS SQL Server 2000 Desktop Engine using a different setup file. Insert the Access installation CD and double-click SETUP.EXE in the MSDE2000 folder to install the engine.

Beginning with Access 2000, new databases are created using the ADO data access object model. You were introduced to ADO in Chapter 2, "Getting Started with Objects and Events," and the concept will be explained in more depth in Chapter 6. For now, what's important to understand is that ADO uses OLE DB "interpreters" (technically known as "providers") to communicate with programs and databases. OLE DB providers exist for a wide range of database/application combinations. There is an Access-Jet OLE DB provider, which enables communication between Microsoft Access 2002 and the Jet database engine. There is also a Microsoft SQL Server OLE DB provider, which enables communication between Microsoft Access 2002 (or any other OLE DB client program) and SQL Server database engines, including the Microsoft SQL Server 2000 Desktop Engine.

The Jet Database Engine

When working with an Access database, the Jet database engine consists of the files necessary to manage data, to control access to the data in the database file, and to store objects that belong to the Application layer. Jet includes the internal programs for six basic database management functions:

Data definition and integrity

With Jet, you can create and modify the objects that hold the data. You can use both the interface and VBA programming to create and modify databases, tables, fields, indexes, relationships, and queries. Jet enforces the entity and referential integrity rules that you specify when you design tables and create relationships.

Data storage

Jet uses a method called the Indexed Sequential Access Method (ISAM) to store data in the file system. With ISAM, data is stored in pages 2KB in size containing one or more records; records have variable length and can be ordered using an index.

Data retrieval

Jet provides two ways to retrieve data. One way is to use Jet's powerful query engine, which uses SQL to retrieve data. The second way is to access the data programmatically using the data access objects in VBA procedures.

Data manipulation

With Jet, you can add new data and modify or delete existing data. You can manipulate data using either the Jet query engine with SQL action queries or the data access objects in VBA procedures.

Security

Jet has two security models, including a database password model for simple password security to the entire database and a workgroup security model in which individual users and groups have permissions to individual database objects.

Data sharing

Jet enables multiple users to access and modify data in the same database. Jet locks the data on a given page when a record is being modified by a user. It does so as soon as one user starts editing (pessimistic locking) and unlocks the page when the editing is completed, or it allows multiple users to edit a record and locks the page only when a user tries to save or commit the changes (optimistic locking). Because a page contains 2KB of data, locking an entire page may lock multiple records—Access 2002 includes a single-record-locking mode that solves this problem. You can select this mode from the Advanced tab of the Tools . . . Options dialog box.

Warning:   Don't confuse data access pages with table pages. Data access pages are part of the object model and can be used to show report-type data using the Internet or an intranet. Table pages are the 2KB sections of data used by tables for storage. Data access pages are often referred to simply as pages.

The Microsoft SQL Server 2000 Desktop Engine

When working with an Access project, the Microsoft SQL Server 2000 Desktop Engine allows the Application layer to connect to a variety of servers and manage data. A project is so named because it does not contain any data or data definition objects; it does not have tables, views, database diagrams, or stored procedures. These database objects are stored in the server database, but they are affected by a project's other objects: forms, reports, data access pages, macros, and modules.

The advantage of the MS SQL Server 2000 Desktop Engine is that it provides local data storage that is compatible with the host server, or it serves as a remote data-storage solution. It performs the same functions (data storage, data retrieval, data manipulation, security, and data sharing) by using the functions of the host server, such as Microsoft SQL Server Design Tools. The Design Tools are conveniently integrated into Microsoft Access and are called into service whenever you create a new table, view, database diagram, or stored procedure in an Access project.

Object Hierarchies

You create VBA procedures to manipulate objects. The objects available for manipulation are the built-in objects that the developers of Access have defined. These built-in objects are grouped into their own collections and arranged into separate hierarchies. In each case, the top of the hierarchy is occupied by a singular object: the Application object heads the Access Application hierarchy and the Connection object heads the ADO object hierarchy. The upper portion of Figure 3.4 shows the Application hierarchy that you use for programming in VBA. (The figure includes the DBEngine object, which is used in DAO programming, a topic discussed in Appendix B, "The Data Access Object (DAO) Model," on this book's CD.)

Figure 3.5 shows the data access objects in the ADO object hierarchy. ADO allows for manipulation of data using VBA programming. You'll learn more about ADO in Chapter 6.

Figure 3.4. The Access Application hierarchy

Figure 3.5. The ADO object hierarchy (click picture for larger image)

Understanding the Access Application Object Model

Let's take a quick tour of the Access Application object model, starting at the top of the hierarchy. The Access Application object model includes several objects that are available only in VBA programming. These objects include the DoCmd object, the Module object, the Modules collection, the Reference object, and the References collection. You'll learn more about these objects in Chapter 5, "VBA Programming Essentials."

The Application Object

The Application object represents Access itself. The Application object also represents the environment in which VBA procedures and macros run. The Application object properties affect the entire Access environment. Table 3.1 lists some of the Application object properties.

Table 3.1. Application Object Properties

Property	Access/Data Type	Description
Build	Read-only/Long	Represents the build number of the currently installed build of Access.
BrokenReference	Read-only/Boolean	Indicates whether the current database has broken references to other databases or type libraries.
CurrentObjectName	Read-only/String	Access sets this to a string expression containing the name of the active object. Use to determine the name of the Database window object that has the focus.
CurrentObjectType	Read-only/Enum	Access sets this to one of the intrinsic constants in VBA. Use to constant determine the type of the active Database window object.
FeatureInstall	Read-write/Enum	Determines what action to take if a user tries to run a feature that constant isn't yet installed.
MenuBar	Read-write/String	Set in VBA. Set the menu bar name to display a custom menu bar throughout your application.
ShortcutMenuBar	Read-write/String	Set in VBA. Use to display a global custom shortcut menu bar when a form, report, or control is right-clicked.
UserControl	Read-only/Boolean	Access sets this property to True/False in VBA. Use to determine whether the current Access application was started by the user (True) or by another application using automation (False).
Version	Read-only/String	Represents the version number of the currently installed version of Access.

Any custom menu bar or shortcut menu bar you set for a form or control will override a custom menu bar or shortcut menu bar you set through the Application object's properties. If you have set a form or report's MenuBar property to a different menu bar, when that form or report has the focus, its custom menu bar is displayed instead of the menu bar set as the Application object's MenuBar property. If you have set a form control, form, or report's ShortcutMenuBar property to a different menu bar and the mouse pointer is over the object when you right-click the object, its custom shortcut menu is displayed instead of the one you set for the Application object.

Note:   When you set the Menu Bar and the Shortcut Menu Bar options in the Startup dialog, you are setting the Database object's StartupMenuBar and StartupShortcutMenuBar properties, not the Application's MenuBar and ShortcutMenuBar properties. The difference is that Access uses the properties you set in the Startup dialog when starting up the database. You can set the corresponding Application properties in a VBA procedure that runs after the database starts up and overrides the Startup dialog settings.

The Forms, Reports, DataAccessPages, and Controls Collection Objects

When you first open a database, Access creates three collections: Forms is the collection of all open forms, Reports is the collection of all open reports, and DataAccessPages is the collection of all open data access pages. Access updates each collection as you open and close individual forms, reports, and pages. Each form and each report has a Controls collection object that contains all of the controls on the form or report. Each page has a WebOptions object that contains attributes used by Access when you save a data access page as a Web page or open a Web page. The Forms, Reports, DataAccessPages, and Controls collections have the properties shown in Table 3.2.

Table 3.2. Forms, Reports, DataAccessPages, and Controls Collection Object Properties

Property	Access/Data Type	Description
Application	Read-only/Object	Use to access the active Application object.
Count	Read-only/Long	Use to determine the number of open forms, open reports, open pages or controls on a form or report.
Item	Read-only/Object	Use to return a specific member of a collection.
Parent	Read-only/Object	Refers to the parent of a control or section.

The Form, Report, and DataAccessPage Objects

The Form object refers to a specific open form. Form objects are members of the Forms collection. You can't add or delete a Form object from the Forms collection (except by opening or closing a form). There are almost 200 Form object properties that describe a form's appearance and behavior; you can set about 100 of the properties in the form's property sheet. The property sheet includes more than 50 event properties that a form recognizes.

The Report object refers to a specific open report. Report objects are members of the Reports collection. You can't add or delete a Report object from the Reports collection (except by opening or closing a report). More than 100 Report object properties describe a report's appearance and behavior; you can set more than 40 of the properties in the report's property sheet. The list includes the seven event properties that a report recognizes.

The DataAccessPage object refers to a specific open data access page. DataAccessPage objects are members of the DataAccessPages collection. You can't add or delete a Data-AccessPage object from the DataAccessPages collection (except by opening or closing a page). Fourteen object properties describe a page's appearance and behavior; you can set three of them (ConnectionString, RemovePersonalInformation, and Visible). Because a data access page has no module, it does not recognize any events.

Table 3.3 lists some of the Form, Report, and DataAccessPage object properties that are particularly useful in VBA programming; many of these properties are available only in VBA programming and are not listed in the property sheet of the form, report, or data access page.

Table 3.3. Selected Form, Report, and DataAccessPage Object Properties

Property	Access/Data Type	Available In	Description
ActiveControl	Read-only/Object	VBA, macro	Use to determine the active control on an open form or report.
Application	Read-only/Object	VBA	Use to access the active Application object.
Count	Read-write/Integer	VBA, macro	Use to determine the number of items in a specified collection.
CurrentRecord	Read-write/Long	VBA, macro	Use to identify the current record.
CurrentView	Read-write/Integer	VBA, macro	For forms and data access pages only, use to determine how a form or page is currently displayed. The property has the value 0 for Design view, 1 for Form view (called Page view when referring to a data access page), and 2 for Datasheet view.
Cycle	Read-write/Byte	VBA, macro,	For forms only, use to specify what happens when property sheetyou tab out of the last control on a form. Set to All Records, Current Record, or Current Page.
Dirty	Read-write/Boolean	VBA, macro	For forms and reports only, use to determine whether the current record has been modified since it was last saved (True) or not (False).
Filter	Read-write/String	VBA, macro,	Use to specify a subset of records to be displayed property sheet when the filter is applied to the form or report. This property is a WHERE clause of a SQL statement without the WHERE keyword. A new form or report inherits the Filter property of the data source it was created from. Filters are saved with the form or report but aren't automatically applied when the form is opened.
FilterOn	Read-write/Boolean	VBA, macro	Use to determine or specify whether the Filter property of the form or report is applied (True) or not (False).
Form	Read-only/Object	VBA, macro	For forms only, use to refer to the Form object or to the active form.
HasData	Read-write/Long	VBA, macro	Use to determine if a report is bound to an empty recordset. Value is –1 if the object has data, 0 if there is no data, or 1 if the report or form is unbound.
HasModule	Read-write/Boolean	VBA, macro,	By default, this property is set to No until the first property sheet time you view the form's or report's module. A form or report without a module is called a lightweight form or report. You can set the property only in form or report Design view.
Hnd	Read-write/Long	VBA, macro	For forms or reports only, use to determine the unique Long Integer (called the window's handle) that Windows assigned to the current window.
KeyPreview	Read-write/Boolean	VBA, macro,	For forms only, use to specify whether the form property sheet receives keyboard events before the active control receives them (True) or not (False).
MenuBar	Read-write/String	VBA, macro,	Set the MenuBar property to the name of the menu property sheet bar you want to display. To display the built-in menu bar using a macro or VBA, set this property to the zero-length string (""). To display a form without a menu bar, set the property to a value that is not the name of an existing menu bar or menu bar macro.
Modal	Read-write/Boolean	VBA, macro,	For forms and reports only, set the Modal property property sheet to True to specify that all other Access windows are disabled when you open the form/report in Form/Report view from the Database window, a macro, or VBA or by switching from Design view.
NewRecord	Read-only/Integer	VBA, macro	For forms only, use to determine whether the current record is a new record.
OrderBy	Read-write/String	VBA, macro,	Use to specify how you want the records sorted in a property sheet form or report. Separate fields with a comma; to sort a field in descending order, type DESC after the string.
OrderByOn	Read-write/Boolean	VBA, macro	Use to specify whether the OrderBy property is applied (True) or not (False).
Painting	Read-write/Boolean	VBA, macro	Use to specify whether a form or report is repainted (True) or not (False).
Picture	Read-write/String	VBA, macro,	Use to specify the path of a bitmap to be displayed property sheet as a background picture on a form or report. Can also be used to display a bitmap on a command button, image control, toggle button, or tab control.
PopUp	Read-write/Boolean	VBA, macro,	Set this property to True to specify that the window property sheet remains on top of other Access windows when you open the form or report in Form or Report view from the Database window, a macro, or VBA or by switching from Design view.
RecordsetClone	Read-only/Object	VBA, macro	For forms only, use to gain access to some properties of the form's recordset as specified by the RecordSource property, such as RecordCount.
RecordSource	Read-write/String	VBA, macro,	Use to specify the data source for a form or report. property sheet The data source may be a table, query, or SQL statement.
Report	Read-only/Object	VBA, macro	For reports only, use to refer to the Report object.
Section	Read-only/Object	VBA, macro	Use to identify a section or controls in a section of a form or report and provide access to the properties of the section.
ShortcutMenuBar	Read-write/String	VBA, macro,	Set to the name of the shortcut menu that is property sheet displayed when a form, form control, or report is right-clicked. To display the built-in shortcut menu, set to the zero-length string ("").
Tag	Read-write/String	VBA, macro,	Use to hold any additional information you want to property sheet store about forms, reports, or sections.
Toolbar	Read-write/String	VBA, macro,	Set to the name of the toolbar you want to display property sheet on a form or report. To display the built-in toolbar, leave the Toolbar property setting blank.
Visible	Read-write/Boolean	VBA, macro	Use to show or hide a form, report, section, data access page, or control.

Note:   With the Modal property set to Yes, the form is called a modal form. The Access menus and toolbars continue to be active on a modal form, and you can still activate a window in another application. To disable the Access menus and toolbars and the windows of other applications, set both the Modal and PopUp properties to Yes. With the PopUp property set to Yes, the form is called a popup form, and the form's toolbar is not active.

The Control Object

The Control object represents a control on a form or report. The controls on a form or report belong to the Controls collection for that form or report. You are familiar with the built-in controls that appear in the toolbox (see Figure 3.6). You can also use custom controls, called ActiveX controls, to provide your application with additional features (see Chapter 15, "Expanding Access," for more information about custom controls). The Control object represents both built-in and custom controls.

Figure 3.6. The built-in controls in the toolbox

Each type of control has its own set of properties, including event properties for the events that the control recognizes. For some types of controls, there is a special property, called the default property, which is the most commonly used property for that type of control. The default property is the property that Access assumes when you refer to a control without specifying a property name. For example, when you refer to a text box, Access assumes you are referring to the Value property.

Data controls are controls that can hold data and can be bound to table fields. A data control has a ControlSource property to indicate the source of the data. The seven built-in data controls include text boxes, combo boxes, list boxes, check boxes, option buttons, toggle buttons, and option groups. Other controls may be associated with another object, such as a form or report; such controls have a SourceObject property to indicate the object or a SourceDoc property to indicate the source of an embedded or linked file. As an example, the subform control has a SourceObject property that you use to specify the form that you want to display in the control. Controls that can display a picture have a Picture property to indicate the source of the image: command buttons and toggle buttons may display images, for example. Table 3.4 lists the built-in controls, their default properties, and whether each control is associated with data, another object or file, or an image.

Table 3.4. Built-in Controls

Control	Default Property	Associated With
Bound object frame	Value	SourceDoc
Check box	Value	ControlSource
Combo box	Value	ControlSource
Command button		Picture
Image		Picture
Label
Line
List box	Value	ControlSource
Option button	Value	ControlSource
Option group	Value	ControlSource
Page break
Rectangle
Subform	Controls	SourceObject
Tab control	Value
Text box	Value	ControlSource
Toggle button	Value	ControlSource, Picture
Object frame		SourceObject, SourceDoc

Each individual type of control has its own set of properties, but all control types share a set of core properties. Table 3.5 lists the core Control object properties.

The Parent property returns a control object if the parent is a control and returns an AccessObject object if the parent is a Microsoft Access object. An AccessObject is a particular Access object within the following collections: AllForms, AllReports, AllMacros, AllModules, AllDataAccessPages, AllTables, AllQueries, AllViews, AllStoredProcedures, and AllDatabaseDiagrams. The Parent property of a label control returns the control that the label is linked to. The Parent property of an option button, check box, or toggle button in an option group returns the option group control.

Most controls have numerous additional properties; at the extreme, the Combo Box control has more than 90 properties. Table 3.6 lists some of the properties for controls that are particularly important when you create programs to automate an application.

Table 3.5. Core Control Object Properties

Property	Access/Data Type	Available In	Description
Application	Read-only/Object	VBA	Use to access the active Access Application object.
EventProcPrefix	Read-write/String	VBA, macro	Use to determine the prefix part of an event procedure name.
InSelection	Read-write/Boolean	VBA, macro	Use to determine or specify whether a control on a form in Design view is selected (True) or not (False).
Left	Read-write/Integer	VBA, macro,	Use to specify the control's location on a form or property sheet report. The value of this property is the distance from the control's left border to the left edge of the section containing the control.
Name	Read-write/String	VBA, macro,	Use to specify a string expression that identifies the property sheet name of the control.
Parent	Read-only/Object	VBA, macro	Use to refer to the parent of the control or section.
Section	Read-write/Integer	VBA, macro	Use to identify the section of the form or report where the control appears. This property is an integer corresponding to a particular section.
Tag	Read-write/String	VBA, macro, property sheet	Use to store additional information about the control. You can use this property to create your own user-defined properties.
Top	Read-write/Integer	VBA, macro, property sheet	Use to specify the control's location on a form or report. The value of this property is the distance from the control's top border to the top edge of the section containing the control.
Visible	Read-write/Boolean	VBA, macro, property sheet	Use to show or hide a control. (This property for the Page-Break control cannot be set through the property sheet.) When you set a control's Visible property to No, the effect is to disable the control.

A few of the control properties listed in Table 3.6 are worth a bit more explanation. The ControlSource property specifies the source of the data in a control. There are three possibilities:

• Leave the property blank. The control is unbound and you can set its value in a macro or VBA, or by having the user type in a value.
• Enter an expression. The control is called a calculated control. It is unbound, and the data in the control cannot be changed.
• Specify the name of a field in the form's underlying recordset. The control is called a bound control. When you edit the data in the control and save the changes, you change the value stored in the field.

Table 3.6. Selected Additional Properties of Control Objects

Property	Control	Access/Data	Available In	Description Type
BackColor	Label, text box, combo list box, image, option group, rectangle, section	Read- write/Long Integer	VBA, macro, property sheet	Use to specify the color for the interior of a control or section. To use the box, BackColor property, the BackStyle property must be set to Normal (if available).
BackStyle	Label, text box, combo box, image, option group, rectangle	Read- write/Byte	VBA, macro, property sheet	Use to specify whether a control will be transparent. Set to 1 in VBA (or Normal in the property sheet) to indicate the interior color is set by the BackColor property, or set to 0 (or Transparent in the property sheet) to indicate the control is transparent.
Column	Combo box, list box	Read- only/Variant	VBA, macro	Use to refer to a specific column or column/row combination, in a multi- column box: 0 refers to the first column or row, 1 to the second column or row, and so on; e.g., Column(0,2) refers to the first column and the third row.
ControlSource	Text box, combo box, list box, check box, toggle button, option button, option group	Read- write/String	VBA, macro, property sheet	Use to specify the source of the data in a control. The ControlSource property doesn't apply to check box, option button, or toggle button controls in an option group; it applies only to the option group itself.
Form	Subform control	Read- only/Object	VBA, macro	Use to refer to the form associated with a subform control.
Height	Form section, report section, check box, combo box, command button, image, label, line, list box, option button, option group, rectangle, text box, toggle button	Read- write/Integer	VBA, macro, property sheet	Use to size an object to a specific height.
HyperlinkAddress	Command button, image, label	Read- write/String	VBA, macro, property sheet	Use to specify or determine the path to an object, document, e-mail address, or Web page that is the target of a hyperlink.
HyperlinkSubAddress	Command button, image, label	Read- write/String	VBA, macro, property sheet	Use to specify or determine a location within the target file specified in the HyperlinkAddress property. To specify an object in the current database, leave the HyperlinkAddress property blank and specify the Hyperlink- SubAddress using the syntax objecttype objectname.
LimitToList	Combo box	Read- write/Boolean	VBA, macro, property sheet	Use to limit the value of a combo box to the listed values (True) or not (False).
ListRows	Combo box	Read- write/Integer	VBA, macro, property sheet	Use to set the maximum number of rows to display in the list box portion of the combo box.
MultiSelect	List box	Read- write/Byte	VBA, macro, property sheet	Use to specify whether a user can make multiple selections in a list box.
OldValue	Text box, combo box, list box, check box, toggle button, option button, option group, image	Read- only/Same data type as the field to which the control is bound	VBA, macro	Use to determine the unedited value in a bound control. (When you save the change, the current value and the OldValue are the same.)
Report	Subreport	Read- only/Object	VBA, macro	Use to refer to the report associated with a subreport control.
RowSource	Combo box, list box	Read- write/String	VBA, macro, property sheet	Use to specify the source of the data for the control. Used in conjunction with the RowSourceType property.
RowSourceType	Combo box, list box	Read- write/String	VBA, macro, property sheet	Use to specify the type of data that fills the list.
SpecialEffect	Label, text box, option button, check box, combo box, list box, image, line, option group, rectangle subform	Read- write/Byte	VBA, macro, property sheet, Special Effects button	Use to specify whether special formatting will apply to a section or control. SpecialEffect options are Flat, Raised, Sunken, Etched, Shadowed, or Chiseled. In VBA, these are set to 0, 1, 2, 3, 4, and 5, respectively.
Text	Text box, combo box	Read- write/String	VBA, macro	Use to determine or specify the data displayed in the control when the control has the focus.
Transparent	Command button	Read- write/Boolean	VBA, macro, property sheet	Use to specify whether a command button is transparent (True) or not (False). With this property, the button is not displayed, but it is still enabled. Use this property to create a control or bitmap section that responds to clicking.
Value	Check box, combo box, list box, option button, option group, text box, toggle button	Read- write/Variant	VBA, macro	Use to determine or specify the saved value of the control.
Width	Form section, report section, check box, combo box, command button, image, label, line, list box, option button option group, rectangle, text box, toggle button	Read- write/Integer	VBA, macro,	Use to size an object to a specific width.

The Text property is available for setting or reading only when the control has the focus. The value of the Text property is the data currently displayed in the control when the control has the focus. The Text property may be different from the control's Value property, which is the last saved data for the control. When you move to another control, the control's Value property is set to the current data. If you save the record without moving to another control, the Text property and the Value property settings are the same. For a combo box, the value can be either a selected list item or a string that you type in. You use the Text property to return or set the text.

The Value property has different settings depending on the type of control:

• For a check box, option button, or toggle button, use the Value property to determine or specify if the control is selected. The setting is True/False.
• For a text box, use the Value property to determine the saved value of the control and to set the value to the control's Text property. The data is a string.
• For a combo box, list box, or option group, use the Value property to determine which value or option is selected. You use the Value property to set a combo box to the control's Text property, a list box to the value in the bound column for the selected list item, and an option group to the OptionValue setting for the selected control within the group.

The Screen Object

The Screen object refers to the particular form, report, data access page, or control that currently has the focus. By using the Screen object in a VBA procedure, you can refer to the active object without knowing the object's name. Referring to the Screen object does not, however, make the form, report, data access page, or control the active object. Table 3.7 lists the properties of the Screen object; all except MousePointer are read-only and return a reference to the object.

Chapter 2 explains the selection-centric approach to programming, which requires that a VBA procedure establish a connection with an object before performing an action on it. You can use the properties of the Screen object to make the connection to the active object.

The Access Visual Basic Object Model

The Access Visual Basic model provides three objects: the Debug, Err, and Collection objects. Only the Debug object is used in VBA programming.

Table 3.7. Screen Object Properties

Property	Description
ActiveControl	Use to refer to the control that currently has the focus.
ActiveDataAccessPage	Use to refer to the data access page that has the focus.
ActiveDatasheet	Use to refer to the datasheet that has the focus.
ActiveForm	Use to refer to the form that has the focus. If a subform has the focus, this property refers to the main form.
ActiveReport	Use to refer to the report that has the focus.
Application	Use to refer to Access.
MousePointer	Use to specify or determine the type of mouse pointer currently displayed.
Parent	Use to refer to the current object's parent object.
PreviousControl	Use to refer to the control that previously had the focus.

You use the Debug object to send a command to a special window called the Immediate window. You can display the Immediate window when any Access window is active by pressing Ctrl+G (see Figure 3.7). You can use the Immediate window like a scratch pad to evaluate expressions, view and set the values of properties, and run procedures.

The Debug object has no properties. However, it has two methods. The Assert method pauses execution at the current line. The Print method prints text in the Immediate window. Here is the syntax:

Debug.Print outputlist

where outputlist is a numeric or string expression or a list of numeric or string expressions separated by either spaces or semicolons. If you omit the outputlist argument, a blank line is printed. When you are working in the Immediate window, you don't need to refer to the Debug object explicitly. You can use the following syntax:

Print outputlist

Alternatively, you can use the question mark (?) as the shortcut abbreviation for Print, as follows:

? outputlist

We'll use the Immediate window to test some of the object and property references discussed in the next section.

Figure 3.7. The Immediate window (click picture for larger image)

Referring to Objects and Properties by Name

Before manipulating an object in a VBA procedure, you must identify the object using the Access rules for referring to objects. Although you may use a naming standard to identify and document the objects you create, Access has its own rules for referring to objects that you must follow when you write programs.

Note:   We'll be using the Immediate window to test and evaluate some of the references, so you'll want to be at your computer to work through this section using the Expenses application we created in Chapter 1, "Automating a Database without Programming." If you didn't work through Chapter 1, follow the steps in that chapter to create the Expenses database now. Remember to press the Shift key as you open the Expenses database so you have access to the full menus and shortcut keys.

Referring to an Object by Name

One way to refer to an object is to start with the top object in the hierarchy and traverse along the hierarchical path to the object, recording the names of the specific objects and collection objects you encounter as you move along the path. You use the exclamation point (!), or bang, operator and the dot (.) operator to distinguish between steps and between objects and collections, as follows:

• Use the bang operator when you are stepping from a collection to one of its members, in the format collectionname!objectname.
• Use the dot operator when you are stepping from an object to one of its collections, in the format objectname.collectionname.

Figure 3.8 illustrates how the bang and dot operators work.

Figure 3.8. Using the bang and dot operators

Referring to a Form or Report

To refer to an open form, say the Switchboard form in the Expenses application, start with the Application object and traverse to the Forms collection and then to the Switchboard form in the collection, as follows:

Application.Forms!Switchboard

To refer to an open report, say the Expense Report report, start with the Application object and traverse to the Reports collection and then to the Expense Report report in the following collection, as shown:

Application.Reports![Expense Report]

When an object's name contains spaces, you must enclose the name in square brackets; otherwise, you can omit the square brackets. (Access may enter the square brackets for you.)

You can decrease the length of a reference by using defaults. For example, Access assumes that you are in Access when you refer to objects; this means you don't need to refer explicitly to the Application object, and the references become

Forms!Switchboard
Reports![Expense Report]

These references are still full-path references that refer to the specific form or report by name.

Referring to Form and Report Properties

You use the dot operator to separate an object from a property of the object, in the form objectname.propertyname. For example, to refer to the RecordSource property of the Expense Categories form, use this reference:

Forms![Expense Categories].RecordSource

Note:   When the name of a property contains more than one word, the property sheet displays spaces between the words; for example, the property sheet for a form displays the label for the RecordSource property as Record Source. You must omit the spaces when you create a reference to a property.

Using the Immediate Window to Evaluate an Object Property

You can use the Immediate window to determine the setting of an object property. Simply press Ctrl+G to open the Immediate window. With the form or report open, type Print or ?, followed by the property reference you want to evaluate, and then press Enter. Access evaluates the property reference immediately and displays the value of the property setting in the next line of the Immediate window.

The Immediate window executes a single line each time you press Enter. You can use many of the familiar text-editing commands in the Immediate window, including the Cut, Copy, Paste, Delete, and Select All commands in the Edit menu. You can edit a line that you have already executed and then press Enter to execute the edited line (Access inserts a new line below the edited line and displays the result of the execution).

Figure 3.9 shows two examples for the Expenses application. Note that when the value is a Yes/No value, such as the value for the NavigationButtons property, Access converts Yes to True and No to False. Also, if no value has been set for the property, Access displays a blank line.

If the property is one that you can set in Design view (a design-time property), then the form or report can be open in any view when you evaluate the property. If the property has a value that is determined only when the form or report is in run mode, such as the Dirty property, then the form or report must be in its run mode; otherwise, Access displays an error message.

The Immediate window can display only text values. If you type a reference to an open form, such as ?Forms![Expense Reports by Employee], and then press Enter, Access displays an error message (see Figure 3.10). If the form or report is closed when you try to evaluate a

Figure 3.9. Using the Immediate window to evaluate the setting of a property

Figure 3.10. The error message generated when you try to evaluate a reference to an object

property, such as ?Forms![Expense Categories].RecordSource, Access displays a different error message (see Figure 3.11).

Referring to a Control

To refer to a control on an open form, you start at the top of the hierarchy with the Application object, traverse to the Forms collection, step to the specific form, traverse to the Controls collection, and finally, step to the control, as follows:

Forms!formname.Controls!controlname

Figure 3.11. The error message generated when you try to evaluate a reference to a property of a closed form or report (click picture for larger image)

For example, to refer to the command button named Option1 on the Switchboard, step along the path first to the Controls collection and then to the specific control as follows:

Forms!Switchboard.Controls!Option1

However, you can shorten the reference by using defaults. An object can have a default collection that Access assumes when you specify a member of a default collection without specifying the collection. The Form and Report objects have the Controls collection as the default collection, so you can omit the reference to Controls along with the dot, and the reference becomes

Forms!Switchboard!Option1

Referring to a Control's Properties

To refer to a property of a control, append a dot and the property name to the end of the control reference, as follows:

Forms!formname!controlname.propertyname

For example, to refer to the saved data in the ExpenseCategory text box control on the Expense Categories form, use the Value property, as follows:

Forms![Expense Categories]!ExpenseCategory.Value

An object may have a default property that Access assumes when you don't explicitly specify a property name. The default property for a text box control is the Value property. Using the defaults, the reference to the saved data in the text box is as follows:

Forms![Expense Categories]!ExpenseCategory

The Value and Text properties of Control objects were discussed earlier in the chapter. To explore the Value and Text properties,

1. Open the Expense Categories form in Form view.

2. Enter Meals in the Expense Category field and save the Record.

3. Change the Expense Category field for the first record to Meal. Press Ctrl+G to bring up the Immediate window.

4. Without saving the record, type each of the following lines and press Enter to evaluate the expression (see Figure 3.12).

   ?Forms![Expense Categories]!ExpenseCategory
   ?Forms![Expense Categories]!ExpenseCategory.Text
   ?Forms![Expense Categories]!ExpenseCategory.Value
   

Figure 3.12. Exploring the Text and Value properties for a text box control (click picture for larger image)

Properties That Represent Other Objects

Most properties have a text value as their setting. You can display this value in the Immediate window. Some objects have special properties that you can use to refer to another object. For example, a control's Parent property refers to the control's parent object. A label's Parent property refers to the control the label is linked to, and a text box's Parent property refers to the form that contains the text box. Table 3.8 lists some of the objects that have properties that refer to other objects.

Because these properties refer to an object and not a value, you can't test these references in the Immediate window. For example, you can use the Parent property of a text box to refer to the form itself, but if you type the expression

?Forms![Expense Categories]!ExpenseCategory.Parent 

and press Enter, Access displays an error message.

Referring to a Subform

A common way to display data from two tables is to create forms based on each of the tables, place a subform control on one of the forms, and display the second form within the subform control. In this arrangement, the form containing the subform control is called the main form and the form displayed within the subform control is called the subform.

Table 3.8. Object Properties That Refer to Other Objects

Object	Object Property	Refers To
Screen	ActiveForm	The form that contains the control that has the focus or the form that has the focus
Screen	ActiveDataAccessPage	The data access page that has the focus
Screen	ActiveDatasheet	The datasheet that has the focus
Screen	ActiveReport	The report that has the focus
Screen	ActiveControl	The control that has the focus
Screen	Parent	The parent object of the current object
Screen	PreviousControl	The control that had the focus just before the control on the same form that currently has the focus
Control	Parent	The object that is the parent of the specified control
Subform control	Form	The form displayed within the subform control
Subreport control	Report	The report displayed within the subreport control
Form	ActiveControl	The control within the form that has the focus
Form	DefaultControl	The default properties for a particular type of control
Form	Form	The form itself
Form	Me	The form itself
Form	Module	The form's module
Form	Printer	A printer available on the system
Form	Recordset	The record source for the form
Form	RecordsetClone	The recordset underlying the form, as specified by the RecordSource property
Form	Section	A section of the form
Report	ActiveControl	The control within the report that has the focus
Report	DefaultControl	The default properties for a particular type of control
Report	Me	The report itself
Report	Module	The report's module
Report	Printer	A printer available on the system
Report	Recordset	The record source for the report
Report	Report	The report itself
Report	Section	A section of the report

To explore the references for a subform, do the following:

1. Open the Expense Reports By Employee form in Form view. The Name property of the subform control is Employees Subform, so you can refer to the subform control using the reference Forms![Expense Reports by Employee]![Employees Subform].
2. You can evaluate properties of the subform control in the Immediate window. As an example, we'll use the SourceObject property to determine the name of the form displayed in the subform control. Type ?Forms![Expense Reports by Employee]![Employees Subform].SourceObject and press Enter.
3. You can refer to the form displayed within the subform control using the Form property of the subform control as follows: Forms![Expense Reports by Employee]![Employees Subform].Form. You can evaluate properties of this form in the Immediate window. We'll evaluate the DefaultView property. Type ?Forms![Expense Reports by Employee]![Employees Subform].Form .DefaultView and press Enter. Access displays the integer 2, which represents Datasheet view.
4. You can refer to a control on a form displayed in a subform control by first referring to the form then traversing to the Controls collection and stepping to the specific control. We'll refer to the value in the Total Expenses control on the subform. Type ?Forms![Expense Reports by Employee]![Employees Subform].Form .Controls![Total Expenses] and press Enter.
5. Fortunately, you can use defaults to simplify the reference to a control on a subform. The default collection for the subform is the Controls collection, so the first simplification is to omit the reference to the Controls collection. Type ?Forms![Expense Reports by Employee]![Employees Subform].Form![Total Expenses] and press Enter.
6. In addition, Access treats the Form property as the default property for the subform control when you are referring to a control on the subform, so you can omit the reference to the Form property. Type ?Forms![Expense Reports by Employee]![Employees Subform]![Total Expenses] and press Enter.

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

Forms!formname!subformcontrolname!controlname

Figure 3.13 shows the results of testing these references in the Immediate window.

Figure 3.13. Testing references to a subform control and to the form displayed in the subform control in the Immediate window (click picture for larger image)

Referring to Controls on the Active Form or Report

The references, or identifiers, we've been exploring are full-path references obtained by starting at the top of the object hierarchy and traversing the path to the object. We've shortened the references by referring to the Application object implicitly and using default collections and default properties. You can also shorten the reference when you want to refer to a control on the active form or the active report. Because Access knows which form is the active form, you can reference the active form implicitly; in other words, you can omit the reference to the active form or report. For example, if Expense Reports By Employee is the active form, you can identify the FirstName text box control using simply FirstName; you can identify the Total Expenses control displayed in the Employees Subform control using [Employees Subform]![Total Expenses].

An identifier that refers to the active form or report implicitly is called the short syntax or unqualified reference. An identifier that includes the full hierarchical path (and uses defaults and an implicit reference to the Application object) is called the fully qualified reference. Normally, you can use the fully qualified reference without problems, but there are exceptions when you must use the short syntax instead. These are discussed in detail in later chapters.

You can't test identifiers that refer to the active object when you are working in the Immediate window because the Immediate window is the active window. If you try to test an unqualified reference in the Immediate window, Access displays a "Compile error: External name not defined" message.

Using the Screen Object to Refer to the Active Object

Access has a way to uniquely identify the active form, report, or control without using the specific names you've given the object. Avoiding specific names is necessary when you create objects that you want to reuse in your application. You can use the properties of the Screen object to identify the active object. For example, to refer to the RecordSource property of the active form, use the reference Screen.ActiveForm.RecordSource. To refer to the Locked property of a control named LastName on the active form, use the reference Screen .ActiveForm!LastName.Locked.

Additionally, you can refer to the active control on the active form using the Screen object. For example, to refer to the name of the active control, you use the reference Screen.ActiveControl.Name. As another example, you can refer to the TabIndex property of the control on the active form that previously had the focus using the reference Screen.PreviousControl.TabIndex. You can use the Screen object in calculated controls on forms and in VBA procedures to refer to an object without naming it explicitly.

Referring to a Field

Often, you need to refer to a field in a table or query. The syntax for the reference depends on whether the table or query is in the underlying record source of an open form.

Referring to a field in the form's record source

You can refer to a field in the table or the query that is the record source for the form, whether the field is bound to a control on the form, using the following reference:

Forms!formname!fieldname

As an example, add a DateHired field to the Employees table and enter sample hire dates for each employee. (Do not add a control to the Expense Reports By Employee form.) Open the Expense Reports By Employee form in Form view. Then type ?Forms![Expense Reports by Employee]!DateHired and press Enter. The Immediate window displays the sample hire date for the employee.

Note:   By default, when you create a form using a form wizard, the Name property of each control created is the same as the name of the field the control is bound to. When you add a control to a form by dragging a field from the field list, the control inherits the field name. However, the control name and the field name need not be the same.

Referring to a field in a table or query

When you are designing a query or a SQL statement, you often need to refer to a column or field in a table or query. The Column object belongs to the Columns collection of the table. The Columns collection is the default collection for tables, so you can use either of the following syntax forms for a table field:

tablename.Columns!columnnametablename!columnname

If you wish to refer to a field in a query (a Command in the ADO model), you have to first have a query return its results to a recordset. The Field object belongs to the Fields collection of the Recordset object. The Fields collection is the default collection for the recordset, so you can use either of the following syntax forms:

recordsetname.Fields!fieldnamerecordsetname!fieldname

However, tables and commands are managed by the database engine. The engine uses either the dot operator or the bang operator when you step from a collection to one of its members. For example, you can use either Employees!LastName or Employees.LastName to refer to the LastName column in the Employees table.

You can't test table and query references directly in the Immediate window. When you work in the Immediate window, you can use the DLookup() function to test a reference to a column or field in a table or a query. For example, to look up the first value in the LastName field in the Employees table, type ?DLookup("LastName", "Employees") in the Immediate window and press Enter. Access displays Smith (or whatever name you used for your first employee). See Chapter 4, "Communicating with Forms," for more information about the DLookup() function. In Chapter 6, you'll learn how to use the data access objects to retrieve data from tables and queries.

Using the Expression Builder to Create References

The expressions for referring to properties and controls on forms and subforms can be very complex. Fortunately, Access provides the Expression Builder to help you create expressions of any kind, including expressions for query criteria and property settings, as well as references for VBA programming. Unfortunately, the Expression Builder is not available in the Immediate window.

You can start the Expression Builder in several ways:

• Right-click the location where you want the expression, choose the Build command from the shortcut menu, and then click Expression Builder.
• Click the location where you want the expression, then click the Build button in the toolbar.
• When you are creating an expression in a property edit box or in an argument edit box, you can summon the Expression Builder by clicking the Build button that appears to the right of the edit box.

In the Expression Builder dialog, the list box on the left contains folders for all of the tables, queries, forms, and reports in your database, as well as folders for built-in functions, constants, operators, common expressions, and custom Visual Basic functions (see Figure 3.14a). The Expression Builder is context-sensitive. The set of folders that appears in the first list box depends on where you started the builder. In Figure 3.14, the builder was started from the property sheet of the Expense Categories form. Folders that contain other folders have a plus sign. When you click to expand the folder, the plus sign changes to a minus sign.

The Forms and Reports folders contain folders for each of your forms and reports and separate folders for the open forms (in the Loaded Forms subfolder) and for the open reports (in the Loaded Reports subfolder). If a form with a subform is open when you start the Expression Builder, Access recognizes the relationship between the form and the subform and shows a folder for the subform within the folder for the form. In Figure 3.14b, the Forms folder is fully expanded. The figure shows that the Expense Categories form is the only open form.

Figure 3.14. Use the Expression Builder to create object references (a). Click the plus sign to expand the Forms folder (b).

When you select a specific object in the list box on the left, the list box in the center changes to show the objects contained in the selected object. If you select a form, the first item in the center list is <Form>, representing the form itself; the second item is <Field List>, representing the field list for the table or query that underlies the form; and the remaining items are the controls and sections on the form. When you select an item in the center list box, the list box on the right changes to display the properties of the item you selected. Figure 3.15 shows the choices for the Total Expenses text box on the Employees Subform form.

Figure 3.15. The qualified reference for a control on a subform (click picture for larger image)

After you have made your choices, click the Paste button. The Expression Builder creates the reference based on your choices and the context where you started the builder and pastes the reference in the edit box at the top of the dialog. Figure 3.15 shows the qualified reference for the Total Expenses text box (note that the Expression Builder includes the default Forms reference). You can edit the reference in the edit box by using the keyboard and the operator buttons in the Builder dialog. In this example, you can edit the reference to delete the Forms reference, or you can shorten the reference to the unqualified reference.

To show how the starting location affects the Expression Builder, let's start the builder with the Expense Reports By Employee form as the active object:

1. With the Expense Reports By Employee form in Design view, select the form and click the Build button in the toolbar. Access displays the Choose Builder dialog (see Figure 3.16).
2. Choose the Expression Builder and click OK. The Expression Builder displays a folder for the form as the first folder in the list box on the left and fills the list box in the center with the controls on the form (see Figure 3.17a).
3. Expand the Expense Reports By Employees folder and choose the Employees Subform. Select Total Expenses in the center list box and <Value> in the right list box. Then click the Paste button. The Expression Builder pastes the short reference for the control (see Figure 3.17b).

Figure 3.16. The Choose Builder dialog

Figure 3.17. Opening the Expression Builder with a form as the active object (a) allows the Expression Builder to create an unqualified reference (b).

You can also use the Expression Builder to create expressions involving custom functions. Double-click the Functions folder in the list box on the left to display folders for the built-in functions and for the current database. When you select the current database, the list box in the center displays the standard modules in the current database. When you select a standard module, the list box on the right displays the custom functions stored in the module. When you select a custom function and click the Paste button, the syntax for the function is displayed in the edit box. Figure 3.18 shows the syntax for theIsLoadedfunction in the GlobalCode standard module in the Expenses database.

Figure 3.18. Using the Expression Builder to display the syntax for a custom function (click picture for larger image)

Summary

This chapter has taken you on an introductory tour of the Access object model that focuses on the objects and properties that are common to VBA programming. The important points are as follows:

• Access has two major components, each with its own object hierarchy: the Access Application and ADO.
• In general, to refer to an object in a VBA procedure, you must use a fully qualified reference. To obtain the fully qualified reference, you start at the top of the object hierarchy and traverse down to the object, recording the names of all the collections and objects you step through on the way. In the Access object hierarchy, you use the dot operator to indicate stepping from an object to one of its children and the exclamation point (or bang) operator to indicate stepping from a collection to one of its members.
• You can use default collections and properties to shorten references.
• To refer to a property of an object, you include the reference to the object and the name of the property, separating the two with the dot operator.
• The properties of the Screen object let you refer to an active object without using its name.
• You can refer to a field in the form's underlying record source even if there is no control on the form that is bound to the field.
• You can use the Expression Builder to create both fully qualified and unqualified references.