Working with Objects - Part 3

In the article entitled Working with Objects – Part 1, you learned the basics of working with objects, properties, methods, and events. In Working with Objects – Part 2, you learned how to work with object variables, collections of objects, and how to pass objects to subroutines and functions. As with the techniques covered in Parts 1 and 2, the techniques that you learn in this article make you much more powerful as a VBA programmer.

Understanding Access’s Object Model

Now that you’ve learned 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 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:

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

Select all Open in new window

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:

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 & “\Chap9Big.accdb”, _
        strFilePath & “\Chap9Small.accdb”, True

End Sub

Select all Open in new window

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 article, the CurrentProject object returns a reference to the current database project. The code uses the Dir 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:

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 & “\ChapV2007.accdb”)) Then
        Kill strFilePath & “\Chap9V2007.accdb”
    End If

    'Convert source database to Access 2007 file format
    Application.ConvertAccessProject strFilePath & “\Chap9Ex.mdb”, _
        strFilePath & “\Chap9V2007.accdb”, _
        DestinationFileFormat:=acFileFormatAccess2007
End Sub

Select all Open in new window

This code first places the path associated with the current project into a variable called strFilePath. Next, it determines whether a file called ChapV2007.acdb 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.

Note

The Forms collection isn’t the same as the list of all forms in the database; that list is part of the CurrentProject object discussed later in this article.

The code that follows iterates through the Forms collection, printing the name of each form. 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

Select all Open in new window

Note

You can easily invoke the Immediate window using the Ctrl+G keystroke combination.

Note

Notice that you do not need to refer to Application.Forms. The reason is that the Application object is always assumed when writing VBA code within Access.

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

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

Select all Open in new window

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:

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

Select all Open in new window

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:

Sub IterateAllForms()

    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

Select all Open in new window

Note

You might easily confuse the AllForms collection of the CurrentProject object with the Forms collection. The AllForms collection of the CurrentProject object comprises all the saved forms that are part of the database; the Forms collection comprises only the forms currently running in memory. If you want to see a list of all the forms that make up a database, you must use the AllForms collection of the CurrentProject object. However, if you want to change the caption of all the open forms, you must use the Forms collection.

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:

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

Select all Open in new window

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:

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

Select all Open in new window

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.

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

Select all Open in new 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 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.

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

Select all Open in new window

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.

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

Select all Open in new 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.

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:


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

DoCmd.OpenQuery "qryCustomers", acViewNormal, acReadOnly

Select all Open in new window

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:

Public Sub GetDates()
    '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, date created, and the date 
            'the table was last modified
            Debug.Print vnt.Name & “, “ & _
                vnt.DateCreated & “, “ & _
                vnt.DateModified
        Next vnt
    End With
End Sub

Select all Open in new window

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 a form.

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

Select all Open in new window

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

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 typeable character was pressed
    If KeyCode = vbKeyDelete Or KeyCode = vbKeyBack Or (KeyCode > 31 _
        And KeyCode < 256) Then
        'If the typeable character was NOT a right, left, up,
        'or down arrow, 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

Select all Open in new window

This generic function, 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:

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 parameter
    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

Select all Open in new window

This generic routine 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

Select all Open in new window

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

Select all Open in new window

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 the three articles in this series you learned about various object, properties, methods and events. In this article you learned about Access’s object model, and how you can take advantage of it. You also saw practical examples of working with objects. After reading this series of three articles, you should now be prepared to make the most out of the objects included in the applications that you build.

This article was excerpted from Alison Balter's Mastering Office Access 2007 Development published by SAMS.
Chap9Ex.accdb