Special Edition Using Microsoft Office Access 2003

Class modules are containers for VBA code that relate to a particular class of objects. Access 2003 defines two classes (collections) Forms and Reports that contain VBA code for a particular instance of the class: a Form or Report object. In object-oriented programming terms, class modules encapsulate VBA code within a Form or Report object. Code encapsulation lets you create reusable objects. For example, when you copy a form from one Access database to another, the copy you make includes the code in the form's class module.

Access's Form and Report class modules differ from conventional modules in that a Form or Report object is integral to the code and contributes the object's visible properties (appearance). Conventional modules, such as Northwind.mdb's Utility Functions, appear in the Modules page of the Database window. Your event-handling code creates a custom set of methods (behavior) that are applicable to the object. When you open a form or report, you create the default instance of the corresponding Form or Report object. The default instance of the object appears in the Forms or Reports page of the Database window. VBA 6.0 also lets you create additional temporary, nondefault instances of Form and Report objects with the New reserved word. You don't need to add an explicit reference to the associated form or report in your code, although it's good programming practice to use the Me self-reference to specify the current instance of the Form or Report object.

Note

Access 9x called the window that displays class module code the Class Module window. The Class Module window was integrated with other Access windows. Access 2000+ uses the VBA Integrated Design Environment (IDE), which this book calls the VBA editor.

For help navigating through this integrated window, see "Exploring the VBA Editor," p. 1179.

The Main Switchboard Class Module

The Northwind Traders Main Switchboard form is a good starting point for gaining an understanding of class modules and simple event-handling code. The Main Switchboard form contains two event-handling subprocedures: one each for the Display Database Window and Exit Microsoft Access command buttons, plus a single OpenForms function that services the Categories, Suppliers, Products, Orders, and Print Sales Reports command buttons. Figure 28.1 illustrates the relationships between the command buttons and the function or subprocedure that executes when you click the button.

To view the event-handling code in the Main Switchboard form, follow these steps:

1. Select the Main Switchboard form in the Database window.

2. Click the Code button of the toolbar to open the VBA editor window, Form_Main Switchboard (Code), for the Main Switchboard form. By default, the editor window opens with the Declarations section at the top of the window.

3. Open the left drop-down list, which Microsoft calls the Object box, to display a list of the control objects of the form, plus the Form object (see Figure 28.2).

   Figure 28.2. The lefthand list of a Form object contains items representing the form and its controls. Controls on the Main Switchboard form don't use the recommended VBA naming conventions, such as a cmd prefix for command buttons.

   Tip

   Undocking and closing unused components of the VBA editor and maximizing the code window makes it easier to view and write code. To undock the individual VBA editor windows, choose Tools, Options, click the Docking tab, and clear the appropriate check boxes. Docking the Project Explorer window, however, makes it easier to switch between class modules of the forms and reports in your project.

4. Select one of the command buttons, such as DisplayDatabaseWindow, from the Object list to display the subprocedure Sub _DisplayDatabaseWindow_Click for the On Click event of the Display Database Window command button (see Figure 28.3).

   Figure 28.3. Selecting a control or the Form object in the left-hand list positions the beginning of the event-handler code for the object at the top of the editor window, if an event handler exists.

5. Open the right drop-down list, which Microsoft calls the Procedure box, to display a list of events applicable to the selection in the Object box. The Click event appears in bold type because the DisplayDatabaseWindows_Click event subprocedure contains VBA code. When you select an event, such as DblClick, without an existing subprocedure, Access creates a subprocedure stub. A subprocedure stub in a class module consists only of the Private Sub [ObjectName_] EventName...End Sub entries (see Figure 28.4).

   Figure 28.4. Selecting an event without a handler in the right-hand list creates a new Private Sub ControlName_ EventName or Private Sub Form_ EventName subprocedure stub.

Tip

All VBA procedures and functions in Access class modules have Public scope unless you specify otherwise; procedures that you declare with Sub, Function, Public Sub, or Public Function are visible to all other class modules and conventional code modules. For example, you can execute the Main Switchboard form's ExitMicrosoftAccess_Click procedure from the Utility Functions module with a [Form_Main Switchboard].ExitMicrosoftAccess_Click instruction. When you create a procedure stub in a class module, Access adds the Private modifier. Private subprocedures and functions have slightly less overhead than Public subprocedures and functions and improve performance in large Access applications. The function and subprocedures of the Form_Main Switchboard class module are declared without the default Private modifier, which is not a good programming practice for event handlers.

Event-Handling Code in the Main Switchboard Form

Listing 28.1 shows all the code contained in the Form_Main Switchboard class module. Each of the procedures includes standard error-handling code consisting of On Error GoTo Err_Lable... Resume Exit_ Label...Exit{ Function| Sub} statements. Adding error handling to every procedure you write is a good VBA programming practice.

For details on use of the On Error statement, see "Handling Runtime Errors," p. 1176.

Listing 28.1 Event-Handling Code of the Form_Main Switchboard Class Module

Option Compare Database 'Use database order for string comparisons. Option Explicit 'Requires variables to be declared before they are used. Function OpenForms(strFormName As String) As Integer 'This function is used in the Click event of command buttons that 'open forms on the Main Switchboard. Using a function is more efficient 'than repeating the same code in multiple event procedures. On Error GoTo Err_OpenForms 'Open specified form. DoCmd.OpenForm strFormName Exit_OpenForms: Exit Function Err_OpenForms: MsgBox Err.Description Resume Exit_OpenForms End Function Sub ExitMicrosoftAccess_Click() 'This code created by Command Button Wizard. On Error GoTo Err_ExitMicrosoftAccess_Click 'Exit Microsoft Access. DoCmd.Quit Exit_ExitMicrosoftAccess_Click: Exit Sub Err_ExitMicrosoftAccess_Click: MsgBox Err.Description Resume Exit_ExitMicrosoftAccess_Click End Sub Sub DisplayDatabaseWindow_Click() 'This code created in part by Command Button Wizard. On Error GoTo Err_DisplayDatabaseWindow_Click Dim strDocName As String strDocName ="Categories" 'Close Main Switchboard form. DoCmd.Close 'Give focus to Database window; select Categories table (first 'form in list). DoCmd.SelectObject acTable, strDocName, True Exit_DisplayDatabaseWindow_Click: Exit Sub Err_DisplayDatabaseWindow_Click: MsgBox Err.Description Resume Exit_DisplayDatabaseWindow_Click End Sub

Tip

The Default to Full Module view and Procedure Separator settings on the Editor page of the Options properties sheet make reading VBA code easier. By default, all the procedures in the class module appear after the Declarations section of the class module in the alphabetical order of the procedure name, separated by a horizontal gray line. With Full Module view specified, you can use the scroll bars to view all the procedures within a module.

Access 2003's DoCmd object is the key to manipulating Access application objects with VBA. DoCmd lets a VBA statement execute the equivalent of an Access macro, such as OpenForm or Quit. Application-specific reserved words, such as DoCmd, preclude a common set of VBA objects for all members of Office; thus, DoCmd is an Access-specific object, not a reserved word.

To learn more about the DoCmd object, see "Working with Access 2003's DoCmd Methods," p. 1209.