Automating Microsoft Access with VBA

 < Day Day Up > 

There's one more part of working with objects that you haven't read about yet: dealing with events. You're probably familiar with events from working in the Access user interface. Events provide "hooks" that let you run your own code when something happens. For example, buttons have a Click event, and when the user clicks a button in your application, you can run a macro in response.

However, it turns out that running macros in response to events is a shortcut invented by the Access team to make it possible to construct reasonably complex applications without writing any code. Most professional developers avoid the use of macros for several reasons (notably that macros do not implement any error trapping). Instead of using macros to respond to events, you can use event procedures, which are special pieces of VBA code.

In the TimeTrack sample application, all the buttons use macros to perform their work. Let's convert one of these to use an event procedure so that you can see the syntax involved.

  1. Open the Switchboard form in Design view.

  2. Select the Clients button and display its properties.

  3. Change the name of the button to cmdClients. When you're writing event procedures, it's helpful to use meaningful names for controls, so that it's obvious in code which control you are referring to.

  4. Change the On Click property from OpenClients (the name of the macro that the click event currently calls) to [Event Procedure] (you can find this choice, including the square brackets, in the drop-down list for the property).

  5. Click the ellipsis button next to the property to construct the event procedure. Access then opens the VBA editor with the code for the form loaded and constructs an empty event procedure for you.

  6. Fill in the body of the event procedure with this code:

    Private Sub cmdClients_Click() DoCmd.OpenForm "Clients" End Sub

  7. Save the form and switch back to Access. Click the button and it will still open the Clients form. You can set a breakpoint in the event procedure to verify that the code is doing the work.

VBA hooks up event procedures to events based on a naming convention and the location of the code. In this case, the code is in the module for the Switchboard form, and the procedure is named using the pattern objectname_eventname. As Figure 8.7 shows, there are also cues in the VBA user interface that let you know when you're working in an event procedure. If you want to construct a new event procedure without using the Access interface, you can select the appropriate object and event from the drop-down lists in the VBA editor.

Figure 8.7. An event procedure in the VBA editor.

This event procedure introduces one more of the built-in Access objects: the DoCmd object. You can think of the DoCmd object as a bridge between the world of Access macros and that of VBA code. The DoCmd object has no properties, but it implements one method for each of the actions that you can include in an Access macro. Anything that you can do with an Access macro, you can do in VBA code with the DoCmd object.

CASE STUDY: Opening Forms and Handling Errors

As you get comfortable with VBA, you'll want to start using event procedures to handle events. You might also want to convert existing macros to event procedures. Doing so helps you catch any errors in event handling moving forward, and places all your code in one place (the VBA editor) instead of spreading it around multiple macros.

The TimeTrack application contains six macros; five of them are called from the Switchboard form and they all open various other forms. Here's the code to use VBA in place of these macros, with some basic error handling added:

Option Compare Database Option Explicit Private Sub cmdBillingReport_Click() On Error GoTo HandleErr DoCmd.OpenForm "BillingReportSetup" ExitHere: Exit Sub HandleErr: MsgBox "Error " & Err.Number & ": " & _ Err.Description & " in cmdBillingReport_Click" Resume ExitHere End Sub Private Sub cmdClients_Click() On Error GoTo HandleErr DoCmd.OpenForm "Clients" ExitHere: Exit Sub HandleErr: MsgBox "Error " & Err.Number & ": " & _ Err.Description & " in cmdClients_Click" Resume ExitHere End Sub Private Sub cmdEmployees_Click() On Error GoTo HandleErr DoCmd.OpenForm "Employees" ExitHere: Exit Sub HandleErr: MsgBox "Error " & Err.Number & ": " & _ Err.Description & " in cmdEmployees_Click" Resume ExitHere End Sub Private Sub cmdProjects_Click() On Error GoTo HandleErr DoCmd.OpenForm "Projects" ExitHere: Exit Sub HandleErr: MsgBox "Error " & Err.Number & ": " & _ Err.Description & " in cmdProjects_Click" Resume ExitHere End Sub Private Sub cmdTimeslips_Click() On Error GoTo HandleErr DoCmd.OpenForm "Timeslips" ExitHere: Exit Sub HandleErr: MsgBox "Error " & Err.Number & ": " & _ Err.Description & " in cmdTimeslips_Click" Resume ExitHere End Sub

CAUTION

If you're entering this code yourself, remember that you need also to change the control names to match those in the code.

This code demonstrates a simple error-handling pattern that you can use by default whenever you don't need to implement more complex error handling. Each procedure starts by using On Error GoTo to identify a label where errors will be trapped. At the end of the normal code in the procedure, a labeled Exit Sub statement is used to finish the procedure without entering the error handler. Within the error handler, the code uses a message box to display some properties of the built-in Err object, which contains information about the most recent error. It then uses a Resume statement to return to the same exit point used by the normal code.

To review the basics of error-handling code, see "Implementing Error Handling," p. 52.

The final macro in the sample database is used by the BillingReportSetup form to open the report. Converting this one to VBA is slightly trickier, because the code needs to specify that the report opens in Print Preview mode:

Private Sub cmdOpenReport_Click() On Error GoTo HandleErr DoCmd.OpenReport "BillingReport", acViewPreview ExitHere: Exit Sub HandleErr: MsgBox "Error " & Err.Number & ": " & _ Err.Description & " in cmdBillingReport_Click" Resume ExitHere End Sub

If you use the Object Browser to look at the OpenReport method of the DoCmd object, you'll see that it has one parameter corresponding to each argument of the matching macro action. The second parameter to the OpenReport method is a constant that indicates the initial view to use for the report. In this case, the built-in constant acViewPreview specifies Print Preview mode. In general, you'll find that the arguments for DoCmd methods are in the same order as the arguments for the corresponding macro actions, which makes converting macros to VBA code fairly easy.

     < Day Day Up > 
    Related

    Категории