Objects are all the things that make up your database. They include tables, queries, forms, reports, data access pages, 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 can be taken upon it.

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.

Most properties can be set at design time and modified at runtime; however, some can’t be modified at runtime, and others can’t be accessed at design time (just modified at runtime). Access’s built-in Help for each property tells you one of the following:

Each of these descriptions indicates when the property can be modified.

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. If a client is paying 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 associated with an object is actually quite easy to do; just invoke Help by pressing F1. Click the Index tab in the help topics dialog box and type the name of the object whose properties you want to view. In Figure 8.1, Combo box has been typed into the text box. Click Search. Notice that one of the entries in the list box at the bottom of the dialog box is Properties of list boxes, combo boxes, drop-down list boxes, and Lookup fields. If you click that entry, information about the Combo Box Control appears in the right pane. (See Figure 8.2.) To find out about the LimitToList property, type LimitToList in the text box and click Search. Figure 8.3 shows help information on the LimitToList property. You can also use the Object Browser to quickly and easily view all properties associated with an object.

Figure 8.1. The Help Topics dialog box.

Figure 8.2. Information about the combo box control.

Figure 8.3. Help associated with a combo box’s LimitToList property.

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:

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 any other object. If you search for help on methods, you see that the Choose a Topic list box fills with a list of topics. (See Figure 8.5.) If you select Undo Method in the list of topics, help appears on the Undo method. (See Figure 8.6.)

Figure 8.5. Getting help on methods.

Figure 8.6. Help on the Undo method.

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 dialog box shown in Figure 8.7 appears.

Figure 8.7. The Object Browser dialog box with the database object selected.

The Object Browser displays two levels of information about the selected library or database. With the Chap8Ex database open, if you select Chap8Ex.MDB from the Project/Library drop-down (the top drop-down), your screen will look similar to Figure 8.7. 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 CHAP8Ex.MDB database. Looking at the list box on the right, you can see the 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. The Members Of list box displays the methods, properties, and data elements defined for the selected object. (See Figure 8.8.) The Access item has been selected from the Library combo box, so the list box on the left shows all Access 2002’s classes. The list box on the right shows all the members of the selected object—in this case, the Application object. You can even add other libraries to the Library drop-down list by referring to other type libraries. This method is covered in Chapter 22, “Automation: Communicating with Other Applications.”

Figure 8.8. Selecting the Access 2002 library in the Object Browser.

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.

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.

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

Dim intVar1 As Integer
Dim intVar2 As Integer
intVar1 = 5
intVar2 = intVar1
intVar1 = 10
Debug.Print intVar1 'Prints 10
Debug.Print intVar2 'Prints 5

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:

Private Sub Command5_Click()
    Dim ctlText As TextBox
    Set ctlText = Forms.frmSales.txtProductID
    ctlText.Text = "New Text"
    Debug.Print Forms.frmSales.txtProductID.Text 'Prints New Text
End Sub

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.

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:

Private Sub ChangeVisible_Click()
    Dim ctlAny As Control
    Set ctlAny = Me.txtCustomerID
    ctlAny.Visible = False
End Sub

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

Private Sub cmdChangeVisible_Click()
    Dim txtAny As TextBox
    Set txtAny = Me.txtCustomerID
    txtAny.Visible = False
End Sub

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

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

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.

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

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

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, covered in the following section.

Sub FormCaptions()
    Dim frm As Form
    For Each frm In Forms
        frm.Caption = frm.Caption & " - " & CurrentUser
    Next frm
End Sub

An overview of the superficial Access Object Model is shown in Access help. At the top of the model, you can see the Application object, which refers to the active Access application. It contains all 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. The Application object can be used 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.

Public Function IdentifyBrokenReference() As Boolean
    'Return whether or not broken references are identified
    'within the current project
    IdentifyBrokenReference = Application.BrokenReference
End Function

Sub CompactRepairDB()
    Dim strFilePath As String

    'Store path of current database in a variable
    strFilePath = CurrentProject.Path

    'If destination database exists, delete it
    If Len(Dir(strFilePath & "Chap8Small.mdb")) Then
        Kill strFilePath & "Chap8Small.mdb"
    End If

    'Use the CompactRepair method of the application object
    'to compact and repair the database
    Application.CompactRepair strFilePath & "Chap8Big.mdb", _
        strFilePath & "Chap8Small.mdb", True

End Sub

Sub ConvertAccessDatabase()
    Dim strFilePath As String

    'Store current file path into variable
    strFilePath = CurrentProject.Path

    'Delete destination database if it exists
    If Len(Dir(strFilePath & "Chap8V97.mdb")) Then
        Kill strFilePath & "Chap8V97.mdb"
    End If

    'Convert source database to Access 97 file format
    Application.ConvertAccessProject strFilePath & "Chap8Big.mdb", _
        strFilePath & "Chap8V97.mdb", _
        DestinationFileFormat:=acFileFormatAccess97
End Sub

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

Sub IterateOpenForms()
    'Declare a form object variable
    Dim frm As Form
    'Use the form object variable to point at each form in the Forms collection
    For Each frm In Forms
        'Print the name of the referenced form to the Immediate window
        Debug.Print frm.Name
    Next frm
End Sub

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 though each report in the Reports collection (the collection of reports open in print preview), printing its name.

Sub IterateOpenReports()
    'Declare a report object variable
    Dim rpt As Report
    'Use the report object variable to point at each report in the Reports
collection
    For Each rpt In Reports
        'Print the name of the referenced report to the Immediate window
        Debug.Print rpt.Name
    Next rpt
End Sub

Just as the Forms collection contains all the currently open forms and the Reports collection contains all the currently open reports, the DataAccessPages collection contains all the currently open data access pages. Using the DataAccessPages collection, you can perform an action on each open data access page.

The code that follows iterates through the DataAccessPages collection, printing the name of each open data access page. It is found in basApplicationObject. It establishes a DataAccessPage object variable. It then uses the For Each...Next construct, along with the object variable, to point at each data access page in the DataAccessPages collection (the collection of open data access pages). The name of each data access page is printed to the Immediate window.

Sub IterateOpenDataAccessPages()
    'Declare a data access page variable
    Dim dap As DataAccessPage
    'Use the data access page object variable to point at
    'each data access page in the data access pages collection
    For Each dap In DataAccessPages
        'Print the name of the referneced data access page
        'to the Immediate window
        Debug.Print dap.Name
    Next dap
End Sub

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 returns a reference to the current project. The CurrentProject contains properties such as Name, Path, and Connection. It contains the following collections: AllDataAccessPages, AllForms, AllMacros, AllModules, and AllReports. These collections are used 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.

Sub CurrentProjectObject()
    With CurrentProject
        Debug.Print .Name
        Debug.Print .Path
    End With
End Sub

Sub IterateAllForms()

    'Declare iteration variable
    Dim vnt As Variant
    'Loop through each form in the current project,
    'printing the name of each form to the Immediate window
    With CurrentProject
        For Each vnt In .AllForms
            Debug.Print vnt.Name
        Next vnt
    End With
End Sub

Sub IterateAllReports()
    'Declare iteration variable
    Dim vnt As Variant
    'Loop through each report in the current project,
    'printing the name of each report to the Immediate window
    With CurrentProject
        For Each vnt In .AllReports
            Debug.Print vnt.Name
        Next vnt
    End With
End Sub

Sub IterateAllMacros()
    'Declare iteration variable
    Dim vnt As Variant
    'Loop through each macro in the current project,
    'printing the name of each macro to the Immediate window
    With CurrentProject
        For Each vnt In .AllMacros
            Debug.Print vnt.Name
        Next vnt
    End With
End Sub

Sub IterateAllModules()
    'Declare iteration variable
    Dim vnt As Variant
    'Loop through each module in the current project,
    'printing the name of each module to the Immediate window
    With CurrentProject
        For Each vnt In .AllModules
            Debug.Print vnt.Name
        Next vnt
    End With
End Sub

Sub IterateAllDAPs()
    'Declare iteration variable
    Dim vnt As Variant
    'Loop through each data access page in the current project,
    'printing the name of each data access page to the Immediate window
    With CurrentProject
        For Each vnt In .AllDataAccessPages
            Debug.Print vnt.Name
        Next vnt
    End With
End Sub

Whereas the CurrentProject object is used to access and manipulate the application components of your database, the CurrentData object is used to reference the data elements of the database. The CurrentData object contains six collections: AllDatabaseDiagrams, AllQueries, AllStoredProcedures, AllTables, AllViews, and AllFunctions. These collections are used to iterate through and manipulate all the database diagrams, queries, stored procedures, views, and functions stored in the database. The AllTables and AllQueries collections are covered in the sections that follow. 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.

Sub IterateAllTables()
    'Declare looping variable
    Dim vnt As Variant
    'Loop through each table in the database
    'referenced by the CurrentData object
    With CurrentData
        For Each vnt In .AllTables
            'Print the name of the table
            Debug.Print vnt.Name
        Next vnt
    End With
End Sub

Sub IterateAllQueries()
    'Declare looping variable
    Dim vnt As Variant
    'Loop through each query in the database
    'referenced by the CurrentData object
    With CurrentData
        For Each vnt In .AllQueries
            'Print the name of the table
            Debug.Print vnt.Name
        Next vnt
    End With
End Sub

The CodeProject object is used 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. Library databases are covered in Chapter 24, “Creating Your Own Libraries.”

Just as the CodeProject 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 include the database diagrams, queries, stored procedures, tables, views, and functions stored within the library.

The Screen object can be used 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 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:

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

DoCmd.OpenQuery "qryCustomers", acNormal, 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.

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 9) looks like this:

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
    'If the Save command button is not already enabled
    If Not cmdSave.Enabled Then
        'If a relevant key was pressed
        If ImportantKey(KeyCode, Shift) Then

            'Flip the command buttons on the form,
            'setting focus to the active control
            Call FlipEnabled(Me, Screen.ActiveControl)

            'Disable the cboSelectClient combo box
            Me.cboSelectClient.Enabled = False

        End If

    'If the Save button is already enabled (form is dirty)
    'ignore the PageUp and PageDown keys
    Else
        If KeyCode = vbKeyPageDown Or _
        KeyCode = vbKeyPageUp Then
        KeyCode = 0
        End If
    End If
End Sub

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 Control 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 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, users’ changes are automatically saved (by Access) 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 keystoke 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:

Function ImportantKey(KeyCode, Shift)
    'Set return value to false
    ImportantKey = False

    'If Alt key was pressed, exit function
    If Shift = acAltMask Then
        Exit Function
    End If

    'If Delete, Backspace, or a a typeable character was pressed
    If KeyCode = vbKeyDelete Or KeyCode = vbKeyBack Or (KeyCode > 31 _
        And KeyCode < 256) Then
        'If the typeable character was NOT right, left, up
        'or down arrows, page up, or page down, return True
        If KeyCode = vbKeyRight Or KeyCode = vbKeyLeft Or _
            KeyCode = vbKeyUp Or KeyCode = vbKeyDown Or _
            KeyCode = vbKeyPageUp Or KeyCode = vbKeyPageDown Then

        Else
            ImportantKey = True
        End If
    End If
End Function

This generic function, found in basUtils, sets its default return value to False. It tests to see whether the Alt key was pressed. 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 Alt wasn’t pressed, 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 page up or page down), True is returned from this function. The Keydown event of the form then calls the FlipEnabled routine. It looks like this:

Sub FlipEnabled(frmAny As Form, ctlAny As Control)
    'Declare a control object variable
    Dim ctl As Control

    'If the type of control received as a parameter
    'is a command button, enable it and set focus to it
    ctlAny.Enabled = True
    ctlAny.SetFocus
    'Loop through each control in the controls collection
    'of the form that was received as a paramter
    For Each ctl In frmAny.Controls

        'If the type of the control is a command button
        'and the name of the control does not match the
        'name of the control received as a parameter
        'flip the enabled property of the control
        If ctl.ControlType = acCommandButton Then
            If ctl.Name <> ctlAny.Name Then
                ctl.Enabled = Not ctl.Enabled
            End If
        End If
    Next ctl
End Sub

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:

Private Sub cmdSave_Click()
    'Save changes to the client record
    DoCmd.RunCommand acCmdSaveRecord

    'Enable client selection combo
    Me.cboSelectClient.Enabled = True
    'Call routine to disable save and cancel and
    'enable view projects
    Call FlipEnabled(Me, Me.cboSelectClient)
End Sub

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:

Private Sub cmdUndo_Click()
    'Undo changes
    DoCmd.RunCommand acCmdUndo

    'Enable client selection combo
    Me.cboSelectClient.Enabled = True

    'Call routine to disable save and cancel and
    'enable view projects
    Call FlipEnabled(Me, Me.cboSelectClient)

End Sub

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.