Special Edition Using Microsoft Office Access 2003

No matter how thoroughly you test and debug your code, runtime errors appear eventually. Runtime errors are errors that occur when Access executes your VBA code. Use the On Error GoTo instruction to control what happens in your application when a runtime error occurs. On Error isn't a very sophisticated instruction, but it's your first choice for error processing in Access modules until Access supports Visual Basic .NET. You can branch to a label or ignore the error. The general syntax of On Error... follows:

On Error GoTo LabelName On Error Resume Next On Error GoTo 0

On Error GoTo LabelName branches to that part of your code that begins with the label LabelName:. LabelName must be a label; it can't be the name of a procedure. The code following LabelName, however, can (and often does) include a procedure call to an error-handling procedure, such as ErrorProc, as in the following:

On Error GoTo ErrHandler ... [RepeatCode: (Code using ErrProc to handle errors)] ... GoTo SkipHandler ErrHandler: Call ErrorProc [GoTo RepeatCode] SkipHandler: ... (Additional code)

In this example, the On Error GoTo instruction causes program flow to branch to the ErrHandler label that executes the error-handling procedure ErrorProc. Ordinarily, the error-handler code is located at the end of the procedure. If you have more than one error handler or if the error handler is in the middle of a group of instructions, you must bypass it if the preceding code is error-free. Use the GoTo SkipHandler statement that bypasses ErrHandler: instructions. To repeat the code that generated the error after ErrorProc has completed its job, add a label such as RepeatCode: at the beginning of the repeated code, and then branch to the code in the ErrHandler: code. Alternatively, you can add the keyword Resume at the end of your code to resume processing at the line that created the error.

On Error Resume Next disregards the error and continues processing the succeeding instructions.

After an On Error GoTo statement executes, it remains in effect for all succeeding errors until execution encounters another On Error GoTo instruction or you turn off error processing with the On Error GoTo 0 form of the statement.

If you don't trap errors with an On Error GoTo statement or if you've turned error trapping off with On Error GoTo 0, a runtime error message appears when an error is encountered. Clicking Debug opens the VBA Editor at the offending line. If you correct the error at this point, press F5 to continue code execution. Otherwise, your only option is to click End to halt code execution.

Tip

Always include error-handling code in runtime applications. If you don't provide at least one error-handling routine in your VBA code for runtime Access applications you distribute with the MOD, your application quits abruptly when the error occurs.

Detecting the Type of Error with the Err Object

The VBA Err object replaces the Err function of earlier versions of Access. The default property, Err.Number, returns an integer representing the code of the last error or returns 0 if no error occurs. This property ordinarily is used within a Select Case structure to determine the action to take in the error handler based on the type of error incurred. Use the Err.Description property, which replaces the Error function, to return the text name of the error number specified as its argument, as in the following example:

strErrorName = Err.Description Select Case Err.Number Case 58 To 76 Call FileError 'procedure for handling file errors Case 340 To 344 Call ArrayError 'procedure for control array errors Case 281 To 22000 Call DDEError 'procedure for handling DDE errors End Select Err.Clear

Tip

The preceding code example illustrate use of comments in VBA code. You create a comment be preceding text with an apostrophe ('). Adding explanatory comments to your code assists others in understanding the purpose of procedures and how the code within the procedures works or is supposed to work.

You can substitute the actual error-processing code for the Call instructions shown in the preceding example, but using individual procedures for error handling is the recommended approach. Err.Number sets the error code to a specific integer. Use the Err.Clear method to reset the error code to 0 after your error handler has completed its operation, as shown in the preceding example.

The Error and RaiseError statements simulate an error so that you can test any error handlers you write. You can specify any of the valid integer error codes or create a user-defined error code by selecting an integer that's not included in the list. A user-defined error code returns "User-defined error" to Error.Description.

Using the Error Event in Form and Report Modules

Access has an event, Error, that's triggered when an error occurs on a form or report. You can use an event-handling procedure in a form or report to process the error, or you can assign a generic error-handling function in an Access module to the Error event with an =ErrorHandler() entry to call the ErrorHandler() function.

When you invoke an error-handling function from the Error event, you must use the Err object to detect the error that occurred and take corrective action, as described in the preceding section.