Chapter 9. Objects, Properties, Methods, and Events Explained

IN THIS CHAPTER

• Why This Chapter Is Important
• Understanding Objects, Properties, Events, and Methods
• Using the Object Browser to Learn About Access’s Objects
• Referring to Objects
• Working with Properties and Methods
• Declaring and Assigning Object Variables
• Understanding the Differences Between Objects and Collections
• Passing Objects to Subroutines and Functions
• Determining the Type of a Control
• Using Special Properties That Refer to Objects
• Understanding Access’s Object Model
• Taking Advantage of Additional Useful Properties
• Practical Examples: Working with Objects

Why This Chapter Is Important

Objects, properties, methods, and events are at the heart of all programming that you do within Microsoft Access. Without a strong foundation in objects, properties, methods, and events and how you should use them, your efforts at Access and Visual Basic for Applications (VBA) programming will fail. This chapter introduces you to Access’s object model. You will not only become familiar with Access’s objects, properties, methods, and events and how to manipulate them, but you will also learn concepts that will carry throughout the book and throughout your Access and VBA programming career.

Understanding Objects, Properties, Events, and Methods

Many people, especially those accustomed to a procedural language, don’t understand the concept of objects, properties, methods, and events. As mentioned earlier, you need a thorough knowledge of Access’s objects, their properties, the methods associated with them, and the events that each object can respond to if you want to be a productive and successful Access programmer.

What Exactly Are Objects?

Objects are all the things that make up your database. They include tables, queries, forms, reports, macros, and modules, as well as the components of those objects. For example, a Table object contains Field and Index objects. A Form object contains various controls (text boxes, combo boxes, list boxes, and so on). Each object in the database has specific properties that determine its appearance or behavior. Each object also has specific methods, which are actions that it can take.

What Exactly Are Properties?

A property is an attribute of an object, and each object has many properties. Often, different types of objects share the same properties; at other times, an object’s properties are specific to that particular object. Forms, combo boxes, and text boxes all have Width properties, for example, but a form has a RecordSource property that the combo box and text box don’t have.

You can set most properties at design time and modify them at runtime; however, you can’t modify some properties at runtime, and you can’t access others at design time. (You can only modify them at runtime.) Access’s built-in Help for each property tells you one of the following:

• You can set this property in the object’s property sheet, a macro, or Visual Basic.
• You can set this property only in Design view.
• You can access this property by using Visual Basic or a macro.

Each of these descriptions indicates when you can modify the property.

As a developer, you set the values of many objects’ properties at design time; the ones you set at design time are the starting values at runtime. Much of the VBA code you write modifies the values of these properties at runtime in response to different situations. For example, suppose that a text box has a Visible property. Let’s take a look at an example. If a client is paying for something by cash, you might not want the text box for the credit card number to be visible. If he’s paying by credit card, you might want to set the Visible property of the text box with the credit card number to True. This is just one of the many things you can do to modify the value of an object’s property at runtime in response to an event or action that has occurred.

You might wonder how you can determine all the properties associated with a particular object (both those that can be modified at design time and those that can be modified at runtime). Of course, to view the properties that can be set at design time, you can select the object and then view its property sheet. Viewing all the properties available in Access 2007 is actually quite easy to do; just invoke Help by clicking the Help button (?). Click Macros and programmability in the Table of Contents, and then click Properties. Your screen will appear as in Figure 9.1. Scroll down and notice that one of the available properties is the OnNotInList property. If you click the link, help appears on the OnNotInList property (see Figure 9.2). You can also use the Object Browser to quickly and easily view all properties associated with an object.

Figure 9.1. Viewing properties in the Table of Contents.

Figure 9.2. Help on the OnNotInList property.

What Exactly Are Events?

Windows is an event-driven operating system; in other words, the operating system responds to many events that are triggered by actions that the user takes and by the operating system itself. Access exposes many of these events through its Object Model. An event in an Access application is something your application can respond to. Events include mouse movements, changes to data, a form opening, a record being added, and much more. Users initiate events, as does your application code. It’s up to you to determine what happens in response to the events that are occurring. You respond to events by using macros or VBA code. Each Access object responds to different events. If you want to find out all the events associated with a particular object, take the following steps:

1. Select the object (for example, a text box).
2. Open the property sheet.
3. Click the Event tab, as shown in Figure 9.3.

   Figure 9.3. The list of events associated with a text box.

   

4. Scroll through the available list of events.

What Exactly Are Methods?

Methods are actions that an object takes on itself. As with properties and events, different objects have different methods associated with them. A method is like a function or subroutine, except that it’s specific to the object it applies to. For example, a form has a GoToPage method that doesn’t apply to a text box or most other objects.

Using the Object Browser to Learn About Access’s Objects

The Object Browser is a powerful tool that can help you learn about and work with the objects that are part of both Access 2007 and the Microsoft Windows environment. The Object Browser displays information about Microsoft Access and other objects and can help you with coding by showing you all the properties and methods associated with a particular object.

Access objects are complex; they have many properties and methods. The Object Browser helps you to understand and use objects, properties, and methods by doing the following:

• Displaying the types of objects available
• Allowing you to quickly navigate between application procedures
• Displaying the properties and methods associated with a particular object
• Finding and pasting code into your application

How to Use the Object Browser

The Object Browser can easily be invoked from the Visual Basic Editor. You can click the Object Browser button on the toolbar, press F2, or choose View, Object Browser. The window shown in Figure 9.4 appears.

Figure 9.4. The Object Browser window with the database object selected.

The Object Browser displays two levels of information about the selected library or database. With the Chap9Ex database open, select Chap9Ex.accdb from the Project/Library drop-down (the top drop-down), and your screen will look similar to Figure 9.4. The Classes list box displays all modules, including Form and Report modules, in the database. The Members Of list box displays any procedures that have been defined in the selected module. Notice the basUtils module, which is part of the Chap9Ex.accdb database. Looking at the list box on the right, you can see the procedures (subroutines and functions) included in the basUtils module. You can click to select each Form and Report module in the list box on the left and view the associated methods and properties in the list box on the right.

You can use the Project/Library drop-down list to select a different object library (provided you have set a reference to it). The Classes list box displays the types of objects available in the selected library or database. Just as with the Access object library, the Members Of list box displays the methods, properties, and data elements defined for the selected object (see Figure 9.5). You can even add other libraries to the Library drop-down list by referring to other type libraries. This method is covered in Chapter 24, “Automation: Communicating with Other Applications.”

Figure 9.5. The Object Browser window with the application object selected.

Pasting Code Templates into a Procedure

After you have located the method or property you’re interested in, you have the option of pasting it into your application. With the method or property selected, simply click the Copy to Clipboard button in the Object Browser; then paste it in the appropriate module. If you want to get more information about a particular method or property, click the Help button in the Object Browser or press F1.

Referring to Objects

Access objects are categorized into collections, which are groupings of objects of the same type. The Forms collection, for example, is a grouping of all the open forms in a database. Each form has a Controls collection that includes all the controls on that form. Each control is an object, and you must refer to an object through the collection to which it belongs. For example, you refer to a form through the Forms collection. VBA offers three ways to refer to an object; if you want to refer to the frmProjects form, for example, you can choose from the following options:

• Forms.frmProjects (or Forms!frmProjects)
• Forms("frmProjects")
• Forms(0)

Referring to the form as Forms(0) assumes that frmProjects was the first form opened. However, you need to understand that although Access assigns an element number as it loads each form, this element number changes as Access loads and unloads forms at runtime. For example, the third form that’s loaded can initially be referred to as element two, but if the second form is unloaded, that third form becomes element one. In other words, you can’t rely on the element number assigned to a form; that number is a moving target.

You must refer to a control on a form first through the Forms collection and then through the specific form. The reference looks like this:

Forms.frmProjects.txtClientID

In this example, Forms is the name of the collection, frmProjects is the name of the specific form, and txtClientID is the name of a control on the frmProjects form. If this code is found in the Code module of frmProjects, it could be rewritten like this:

Me.txtClientID

Me refers to the current form or report. It’s generic because the code could be copied to any form having a txtClientID control, and it would still run properly. Referring to a control on a report is similar to referring to a control on a form. Here’s an example:

Reports.rptTimeSheet.txtHoursBilled

This example refers to the txtHoursBilled text box on the rptTimeSheet report, part of the Reports collection. After you know how to refer to an object, you’re ready to write code that modifies its properties and executes its methods.

Working with Properties and Methods

To modify an object’s properties and execute its methods, you must refer to the object and then supply an appropriate property or method, as shown in this example:

Forms.frmHello.cmdHello.Visible = False

This line of code refers to the Visible property of cmdHello, found in the frmHello form, which is in the Forms collection. Notice that you must identify the object name frmHello as being associated with the Forms collection. If you want to change the Caption property of frmHello to say "Hello World", you would use the following code:

Forms.frmHello.Caption = "Hello World"

Telling the Difference Between Properties and Methods

You might be confused about whether you’re looking at an object’s property or method, but there are a couple of quick ways to tell. You will always use a property in some type of an expression. For example, you might be setting a property equal to some value:

Forms.frmClients.txtAddress.Visible = False

Here, you’re changing the Visible property of the txtAddress text box on the frmClients form from True to False. You also might retrieve the value of a property and place it in a variable:

strFirstName = Forms.frmClients.txtFirstName.Value

You also might use the value of a property in an expression, as in the following example:

MsgBox Forms.frmClients.txtFirstName.Value

The pattern here is that you will always use a property somewhere in an expression. You can set it equal to something, or something can be set equal to its value, or it’s otherwise used in an expression.

A method, however, is an action that an object takes on itself. The syntax for a method is Object.Method. A method isn’t set equal to something; however, you frequently create an object variable and then set it by invoking a method. A method looks like this:

Forms.frmHello.txtHelloWorld.SetFocus

In this example, the text box called txtHelloWorld executes its SetFocus method.

A method that returns an object variable looks like this:

Dim cbr As CommandBar
Set cbr = CommandBars.Add("MyNewCommandBar")

In this example, the CommandBars collection’s Add method is used to set the value of the CommandBar object variable named cbr. For more information, see the section “Declaring and Assigning Object Variables,” later in this chapter.

Using a Bang Versus a Period

Many people are confused about when to use a bang (!) and when to use a period. You can use a bang whenever you’re separating an object from its collection, as shown in these two examples:

Forms!frmClients

Forms!frmClients!txtClientID

In the first example, frmClients is part of the Forms collection. In the second example, txtClientID is part of the Controls collection of the frmClients form.

In most cases, you can also use a period to separate an object from its collection. The reason is that the expression Me!txtClientID is actually a shortcut to the complete reference Me.Controls!txtClientID. Because Controls is the default collection for a form, you can omit Controls from the statement. You can abbreviate the expression to Me.txtClientID. The advantage of using the period over the bang is that the period provides you with IntelliSense. To test this, create a form and add a control called txtFirstName. Go to the code behind the form, and try typing Me!. Notice that IntelliSense is not invoked. Next, type Me. and watch as IntelliSense is invoked. IntelliSense facilitates the development process by providing a list box containing valid properties, methods, constants, and so on, as appropriate.

In addition to separating an object from its collection, the period is also used to separate an object from a property or method. The code looks like this:

Forms.frmClients.RecordSource = "tblClients"

Forms.frmClients.txtClientID.Visible = False

The first example sets the RecordSource property of frmClients to tblClients, and the second example sets the Visible property of the txtClientID on the frmClients form to False.

Default Properties

Each object has a default property, and if you’re working with an object’s default property, you don’t have to explicitly refer to it in code. Take a look at the following two code samples:

Forms.frmHello.txtHello.Value = "Hello World"

Forms.frmHello.txtHello = "Hello World"

The Value property is the default property of a text box, so you don’t need to explicitly refer to it in code. However, I prefer to explicitly state the property; it is a practice that contributes to the code’s readability and keeps novice Access programmers who work with my code from having to guess which property I’m changing.

Declaring and Assigning Object Variables

Object variables are variables that reference an object of a specific type, such as databases, recordsets, forms, controls, and even objects created in other applications. They allow you to create shortcut references to objects and pass objects to subroutines and functions. You can use them to streamline code by using short names to refer to objects with long names and to optimize code by supplying a direct pointer to a particular object.

First, you must declare an object variable; then you assign—or point—the object variable to a particular object, as shown in the following code:

This code creates an object variable called cmdAny of the type CommandButton. You then use the Set statement to point your CommandButton object variable toward the cmdHello object on the current form, using the Me keyword. Finally, you modify the caption of the cmdAny object variable. Because an object variable is a reference to the original object, you’re actually changing the caption of the cmdHello command button.

Object Variables Versus Regular Variables

The difference between object variables and regular variables is illustrated by the following code:

This code uses ordinary variables. When you dimension these variables, each one is assigned a separate memory location. Although intVar2 is initially assigned the value of intVar1, changing the value of intVar1 has no effect on intVar2. This differs from the following code, which uses an object variable:

This routine creates an object variable called ctlText of type TextBox. It then associates the object variable with Forms.frmSales.txtProductID. Next, it modifies the Text property of the object variable. Because the object variable is actually pointing to the text box on the form, the Debug.Print statement prints the new text value.

Generic Versus Specific Object Variables

Access supports the use of generic object variables, including Application, Control, Form, and Report. Generic object variables can be used to refer to any object of that generic type:

In this example, ctlAny can be used to point to any control. Compare that to the following code:

Here, your object variable can be used only to point to a text box.

Cleaning Up After Yourself

When you’re finished working with an object variable, you should set its value to Nothing. As used in the following example, this statement frees up all memory and system resources associated with the object:

Set frmNew = Nothing

Understanding the Differences Between Objects and Collections

Many people get confused about the differences between an object and a collection. Think of an object as a member of a collection. For example, frmHello is a form that’s a member of the Forms collection; cmdHello, a command button on frmHello, is a member of the Controls collection of frmHello. Sometimes you want to manipulate a specific object, but other times you want to manipulate a collection of objects.

Manipulating a Single Object

You have already learned quite a bit about manipulating a single object, such as setting the Enabled property of a text box:

Me.txtCustomerID.Enabled = False

This line of code affects only one text box and only one of its properties. However, when you’re manipulating a single object, you might want to affect several properties at the same time. In that case, it’s most efficient to use the With...End With construct, explained in the following section.

One method you can use to modify several properties of an object is to modify the value of each property, one at a time:

Me.txtCustomerID.Enabled = False
Me.txtCustomerID.SpecialEffect = 1
Me.txtCustomerID.FontSize = 16
Me.txtCustomerID.FontWeight = 700

Contrast this with the following code:

This code uses the With...End With statement to assign multiple properties to an object. In addition to improving the readability of your code, the With...End With construct results in a slight increase in performance.

Manipulating a Collection of Objects

A collection is like an array of objects. What makes the array special is that it’s defined and maintained by Access. Every collection in Microsoft Access is an object, each with its own properties and methods. The VBA language makes it easy for you to manipulate Access’s collections of objects; you simply use the For Each...Next construct, which performs the same command on multiple objects.

In the “Determining the Type of a Control” section later in this chapter, you learn how to loop through the collection of controls on a form, performing actions on all the command buttons. This illustrates a practical use of a collection. In the following example, you loop through all the open forms, changing the caption of each form:

This routine uses the For Each...Next construct to loop through each form in the Forms collection, setting the caption of each form to the form’s caption concatenated with the current username. As you travel through the loop, the code frm.Caption refers to each member of the Forms collection.

Passing Objects to Subroutines and Functions

Just as you can pass a string or a number to a subroutine or function, you can also pass an object to a subroutine or function. The code, found in the basExamples module in the Chap9Ex database, looks like this:

The ChangeCaption routine receives a reference to a form as a parameter. The caption of the form referenced by the procedure is modified to include the name of the current user. The ChangeCaption routine is called like this:

In this example, the click event of the cmdChangeCaption command button calls the ChangeCaption routine, sending a reference to the form that the command button is contained within. You will find this code in the frmChangeCaption form.

Determining the Type of a Control

When writing generic code, you might need to determine the type of a control. For example, you might want to loop through all the controls on a form and flip the Enabled property of all the command buttons. To do this, use the ControlType property of a control. Here’s an example of how it’s used. (You can find this in Chap9Ex.accdb in the module called basExamples.)

The FlipEnabled procedure is called from the frmTypeOf form. Each command button on the form (Add, Edit, Delete, and so on) sends the form and the name of a control to the FlipEnabled routine. The control that it sends is the one that you want to receive the focus after the routine executes. In the example that follows, the code sends the cmdSave command button to the FlipEnabled routine. The FlipEnabled routine sets focus to the Save button:

The FlipEnabled routine receives the form and control as parameters. It begins by enabling the command button that was passed to it and setting focus to it. The FlipEnabled routine then uses the VBA construct For...Each to loop through all the controls on a form. The For...Each construct repeats a group of statements for each object in an array or collection—in this case, the Controls collection. The code evaluates each control on the form to determine whether it’s a command button. If it is, and it isn’t the command button that was passed to the routine, the routine flips the control’s Enabled property. The following VBA intrinsic controls are used when evaluating the ControlType property of a control:

Using Special Properties That Refer to Objects

VBA offers the convenience of performing actions on the active control, the active form, and other specially recognized objects. The following is a list of special properties that refer to objects in the Access Object Model:

• The ActiveControl property refers to the control that has focus on a screen object, form, or report.
• The ActiveForm property refers to the form that has focus.
• The ActiveReport property refers to the report that has focus.
• The Form property refers to the form that a subform is contained in or to the form itself.
• Me refers to the form or report where code is currently executing.
• Module refers to the module of a form or report.
• The Parent property refers to the form, report, or control that contains a control.
• PreviousControl refers to the control that had focus immediately before the ActiveControl.
• RecordsetClone refers to a clone of the form’s underlying recordset.
• The Report property refers to the report that a subform is contained in or to the report itself.
• The Section property refers to the section in a form or report where a particular control is located.

The following example using the Screen.ActiveForm property shows how a subroutine can change the caption of the active form:

This subroutine modifies the caption of the active form, appending the value of the CurrentUser property onto the end of the existing caption.

Understanding Access’s Object Model

Now that I’ve discussed the concept of objects, properties, methods, and events in a general sense, I’m going to switch the discussion to the objects that are natively part of Microsoft Access. Databases are composed of objects, such as the tables, queries, forms, reports, macros, and modules that appear in the Navigation Pane. They also include the controls (text boxes, list boxes, and so on) on a form or report. The key to successful programming lies in your ability to manipulate the database objects using VBA code at runtime. It’s also very useful to be able to add, modify, and remove application objects at runtime.

The Application Object

At the top of the Access Object Model, you will find the Application object, which refers to the active Access application. It contains all of Access’s other objects and collections, including the Forms collection, the Reports collection, the DataAccessPages collection, the Modules collection, the CurrentData object, the CurrentProject object, the CodeProject object, the CodeData object, the Screen object, and the DoCmd object. You can use the Application object to modify the properties of, or execute commands on, the Access application itself, such as specifying whether Access’s built-in toolbars are available while the application is running.

Application Object Properties

The Application object has a rich list of properties. An important property introduced with Access 2002 is the BrokenReference property. You use this property to determine whether any broken references exist within the current project. The property is equal to True if broken references exist, and False if no broken references are identified. The property eliminates the need to iterate through each reference, determining whether any references are broken. The following code returns the value of the BrokenReference property:

Application Object Methods

Just as the Application object has a rich list of properties, it also has a rich list of methods. Another important method introduced with Access 2002 is the CompactRepair method, which allows you to programmatically compact and repair a database, without declaring ActiveX Data Objects (ADO) objects. The code looks like this:

This code uses the Path property of the CurrentProject object to extract the path of the current project and place it into a string variable. Covered later in this chapter, the CurrentProject object returns a reference to the current database project. The code uses the Dir function to evaluate whether the database called Chap9Small.accdb exists. If it does, the code uses the Kill command to delete the file. Finally, the code uses the CompactRepair method to compact the Chap9Big.accdb file into Chap9Small.accdb.

Another important method introduced with Access 2002 is the ConvertAccessProject method. This method allows you to programmatically convert an Access database from one version of Access to another. Here’s an example:

This code first places the path associated with the current project into a variable called strFilePath. Next, it determines whether a file called Chap9V2007.mdb exists. If such a file does exist, it deletes the file. Finally, the code uses the ConvertAccessProject method of the Application object to convert an Access 2003 database called Chap9Ex.mdb to the Access 2007 file format. The destination file is called Chap9V2007.accdb. Different constants are used for the DestinationFileFormat parameter to designate conversion of the source file to different versions of Access.

The Forms Collection

The Forms collection contains all the currently open forms in the database. Using the Forms collection, you can perform an action, such as changing the color, on each open form.

The code that follows iterates through the Forms collection, printing the name of each form. It is found in the basApplicationObject module within the Chap9Ex database. It begins by establishing a form object variable. It then uses the For Each...Next construct to loop through each form in the Forms collection (the collection of open forms), printing its name. Before running the code, open a few forms. Run the code and then take a look in the Immediate window. Close a couple of the forms and rerun the code. The list of forms displayed in the Immediate window should change.

The Reports Collection

Just as the Forms collection contains all the currently open forms, the Reports collection contains all the currently open reports. Using the Reports collection, you can perform an action on each open report.

The code that follows iterates through the Reports collection, printing the name of each open report. It is found in basApplicationObject. It begins by establishing a report object variable. It then uses the For Each...Next construct to loop through each report in the Reports collection (the collection of reports open in print preview), printing its name.

The Modules Collection

The Modules collection contains all the standard and class modules that are open. All open modules are included in the Modules collection, regardless of whether they’re compiled and whether they contain code that’s currently running.

The CurrentProject Object

The CurrentProject object returns a reference to the current project. The CurrentProject object contains properties such as Name, Path, and Connection. It contains the following collections: AllDataAccessPages, AllForms, AllMacros, AllModules, and AllReports. You can use these collections to iterate through all the data access pages, forms, macros, modules, and reports stored in the database. These collections differ from the DataAccessPages, Forms, Macros, Modules, and Reports collections in that they refer to all objects stored in the current project, rather than to just the open objects.

The following code retrieves the Name and Path properties of the current project. It uses the With...End With construct to retrieve the properties of the CurrentProject object:

The AllForms Collection

As previously mentioned, the CurrentProject object contains collections that refer to the various objects in your database. The following code iterates through the AllForms collection of the CurrentProject, printing the name of each form:

The AllReports Collection

The AllReports collection allows you to loop through all reports in the current project. The example that follows prints the name of each report stored in the database referenced by the CurrentProject object:

The AllMacros Collection

The AllMacros collection allows you to iterate through all macros stored in the current project. The example that follows prints the name of each macro stored in the database referenced by the CurrentProject object:

The AllModules Collection

The AllModules collection is another collection associated with the CurrentProject object. The code that follows iterates through all modules located in the database referenced by the CurrentProject object. The name of each module is printed to the Immediate window.

The CurrentData Object

Whereas you use the CurrentProject object to access and manipulate the application components of your database, you use the CurrentData object to reference the data elements of the database. The CurrentData object contains six collections: AllDatabaseDiagrams, AllQueries, AllStoredProcedures, AllTables, AllViews, and AllFunctions. You use these collections to iterate through and manipulate all the database diagrams, queries, stored procedures, views, and functions stored in the database. The sections that follow cover the AllTables and AllQueries collections. The AllDatabaseDiagrams, AllStoredProcedures, AllViews, and AllFunctions collections are available only in Access Data Projects and are discussed in detail in Alison Balter’s Mastering Access 2002 Enterprise Development.

The AllTables Collection

The AllTables collection is used to iterate through all tables in the database referenced by the CurrentData object, as shown in the following code. It prints the name of each table in the database.

The AllQueries Collection

You use the AllQueries collection to iterate through all queries located in the database referenced by the CurrentData object. The following example loops through all queries in the database referenced by the CurrentData object. The name of each query is printed to the Immediate window.

The CodeProject Object

You use the CodeProject object when your database implements code libraries. It is similar to the CurrentProject object but is used to reference the properties and collections stored within the library database. Chapter 26, “Creating Your Own Libraries,” covers library databases.

The CodeData Object

Just as the CodeProject object is used to reference the application objects stored within a library database, the CodeData object is used to reference the data elements of a code library. These elements include the database diagrams, queries, stored procedures, tables, views, and functions stored within the library.

The Screen Object

You can use the Screen object to refer to the form, datasheet, report, data access page, or control that has the focus. The Screen object contains properties that refer to the active form, active report, active control, and previous control. Using these properties, you can manipulate the currently active form, report, or control, as well as the control that was active just before the current control. If you try to refer to the Screen object when no form or report is active, a runtime error occurs.

The DoCmd Object

The DoCmd object is used to perform macro commands or Access actions from VBA code; it’s followed by a period and the name of an action. Most of the DoCmd actions—the OpenQuery action, for example—also require arguments. The OpenQuery action is used to execute an Access query. It receives the following arguments:

• Query Name—The name of the query you want to execute
• View—Datasheet, Design, or Print preview
• Data Mode—Add, edit, or read-only

Here’s an example of the OpenQuery action of the DoCmd object:

DoCmd.OpenQuery "qryCustomers", acViewNormal, acReadOnly

The OpenQuery action is performed by the DoCmd object. The first argument, the query name, is "qryCustomers". This is the name of the query that’s opened in Datasheet view (rather than Design view or Print preview). It’s opened in read-only mode, meaning the resulting data can’t be modified.

Taking Advantage of Additional Useful Properties

In addition to the properties already discussed, two other properties are worth mentioning. They are the DateCreated and DateModified properties. They are available for all Access objects. Here’s an example that shows the use of these properties with the AllTables collection:

This code loops through each table stored in the database referenced by the CurrentData object. The name, creation date, and last modification data are all printed to the Immediate window.

Practical Examples: Working with Objects

Most applications use objects throughout. The example that follows applies the technique you learned to enable and disable command buttons in response to the user making changes to the data on the frmClients form, located in Chap9Ex.mdb on the sample code website.

Enabling and Disabling Command Buttons

When a user is in the middle of modifying form data, there’s really no need for her to use other parts of the application. It makes sense to disable other features until the user has opted to save the changes to the Client data. The clean form begins with the View Projects command button enabled and the Save and Cancel buttons disabled. The KeyPreview property of the form is set to Yes so that the form previews all keystrokes before the individual controls process them. In the example, the KeyDown event of the form is used to respond to the user “dirtying” the form. It executes whenever the user types ANSI characters while the form has the focus. The KeyDown event of the form (discussed in detail in Chapter 10, “Advanced Form Techniques”) looks like this:

The KeyDown event automatically receives the code of the key that was pressed, whether Shift, Alt, or Ctrl was pressed along with that key. The event routine checks to determine whether the Save button is already enabled. If it is, there’s no reason to continue; the Enabled property of the command buttons has already been flipped. If Save isn’t already enabled, the ImportantKey function (discussed in detail later) is called. It receives the key that was pressed, despite whether Shift, Alt, or Ctrl was used.

The ImportantKey evaluates the key that was pressed to determine whether a keystroke is modifying the data. If it is, the function returns True. Otherwise, it returns False. If ImportantKey returns True, the FlipEnabled routine is executed. FlipEnabled flips the enabled property of the command buttons on the form so that Save and Cancel are enabled, and View Projects is disabled.

If the value returned from the ImportantKey function is True, the enabled property cboSelectClient combo is set to False. If you fail to prevent movement to other records while the form is dirty, Access automatically saves the user’s changes when the user navigates to another record. Furthermore, the enabled state of the command buttons still reflects a dirty state of the form.

Finally, if Save is already enabled, you know that the form is in a dirty state. If that is the case, it is not appropriate for the user to be able to move to another record using the PageUp and PageDown keys. If the cmdSave command button is enabled, and the key pressed is PageUp or PageDown, the keystroke is ignored.

Now that you understand the role of the KeyDown event of the form, take a look at the functions that underlie its functionality. The ImportantKey function looks like this:

This generic function, found in basUtils, sets its default return value to False. It tests to see whether the user pressed the Alt key. If so, the user was accessing a menu or accelerator key, which means that there’s no reason to flip the command buttons. The function is exited. If the user didn’t press the Alt key, the key that was pressed is evaluated. If the Delete key, Backspace key, or any key with an ANSI value between 31 and 256 was pressed (excluding the left-, right-, up-, and down-arrow keys, and PageUp or PageDown), True is returned from this function. The KeyDown event of the form then calls the FlipEnabled routine. It looks like this:

This generic routine, also found in basUtils, flips the Enabled property of every command button in the form, except the one that was passed to the routine as the second parameter. The FlipEnabled routine receives a form and a control as parameters. It begins by creating a control object variable; then it enables the control that was passed as a parameter and sets focus to it. The routine then loops through every control on the form that was passed to it. It tests to see whether each control is a command button. If it finds a command button, and the name of the command button isn’t the same as the name of the control that was passed to it, it flips the Enabled property of the command button. The idea is this: When the user clicks Save, you can’t immediately disable the Save button because it still has focus. You must first enable a selected control (the one that was passed to the routine) and set focus to the enabled control. After the control is enabled, you don’t want to disable it again, so you need to eliminate it from the processing loop.

Remember that as long as the cmdSave command button is enabled, the PageUp and PageDown keys are ignored. This is an important step because it is imperative that the user not be able to move from record to record while editing the form data.

You need a way to flip the command buttons back the other way when editing is complete. The Click event of the Save button contains the following code:

This code saves the current record and enables the cboSelectClient control. It then calls the FlipEnabled routine, passing a reference to the cboSelectClient control as a parameter. The FlipEnabled routine flips the command buttons back to their original state.

The form contains a cancel command button with a similar routine. It looks like this:

This code undoes changes to the current record. It enables the cboSelectClient control and calls the FlipEnabled routine to once again disable Save and Cancel and enable View Projects.

Summary

The ability to successfully work with objects and understand their properties, methods, and events is fundamental to your success as an Access programmer. In this chapter, you learned about various objects, properties, methods, and events. You learned how to set properties at design time and change their values in response to events that occur at runtime. You also learned how to pass forms and other objects to subroutines and functions to make the VBA language extremely robust and flexible.