Automating Microsoft Access with VBA

Access forms are the place where your database's users interact directly with the Jet engine. And as such, they are also the places where many things can go wrong. Users might enter data that violates a validation rule, or try to create records with duplicate primary keys, or cause a referential integrity problem.

NOTE

As you probably already know, Jet is the underlying database engine of Access the part of the application that actually saves and retrieves data.

None of these errors can be trapped directly in VBA, because VBA does not get involved between the Access user interface and the Jet engine. But there is still a hook to let your VBA code get involved if you like: the Form's Error event is called whenever the Jet engine is ready to process a data error of any kind. If you like, you can react to this error to amplify or override Jet's default error message. Here's how:

1. Open the Timeslips form and start entering a new record. Enter "Tuesday" for the date worked and press Tab. You'll see the error message shown in Figure 10.5. This error message comes directly from the Jet engine.

   Figure 10.5. A Jet error message during data entry.

2. Press Esc twice to discard the new timeslip and switch the form to Design mode.

3. Add this code to the form's module:

   Private Sub Form_Error(DataErr As Integer, _ Response As Integer) ' Respond to Jet errors Select Case DataErr Case 2113 ' Data isn't right for the field MsgBox "The data you entered isn't right for " & _ "this field. Please try again, or press the " & _ "Escape key to undo.", vbInformation ' Suppress the default Access message Response = acDataErrContinue Case Else ' Let Access handle other errors Response = acDataErrDisplay End Select End Sub

4. Save the form and switch back to Form view.

5. Start entering a new record. Enter "Tuesday" for the date worked and press Tab. You'll see the error message shown in Figure 10.6. This error message comes from the code that you just wrote. When you click OK, the default Access error message is not displayed.

   Figure 10.6. A custom error message during data entry.

Whenever a Jet error occurs during data entry, Access calls the VBA event handler for the form's Error event. This handler has two arguments. The DataErr argument is the number of the Jet error that triggered the event. In this case, you're looking for event 2113, which corresponds to invalid data. The Response argument is one that you can set when you're done dealing with the error. If you set this argument to the constant acDataErrDisplay, Access displays its own default error message. If you set this argument to the constant acDataErrContinue, Access suppresses its own default error message.

TIP

There's no master list of Jet errors. But you can set up a skeletal event handler with a breakpoint in it, and then check the value of DataErr in the Immediate window, to determine the error numbers of the errors that you want to deal with.