Error Handling in MS Access - MS Access

VBA supports an On Error statement to indicate how errors should be handled.

The simplest way to understand how errors can occur is to generate an error and then look at the strategies for handling it. The following code contains an error:

When you execute the code, it generates the error message illustrated in Figure.

The error message displayed as the result of an untrapped error in program code.

The error message displayed as the result of an untrapped error in program code.

This is probably not the kind of interaction that you would like your user to have with your application.

On Error Resume Next

This is a useful, yet possibly dangerous technique—it is dangerous if you fail to very carefully add lines of code to test for when an error has occurred. In the following example, you set this at the beginning of a procedure, which means that after a line executes, any error allows the execution to continue. When this is complete, you need to test the Err object to see if an error occurred and to display the message box shown in Figure:

Using On Error Resume Next to trap an error.

Using On Error Resume Next to trap an error.

This method can be used to perform special error handling for individual lines, but wherever it is used, you should test to see if the error has occurred and then take appropriate action.

Taking care when using On Error Resume Next and On Error Resume

The last line of code in the previous example contains an On Error GoTo 0 statement. This disables any further error handling and suspends the action of the On Error Resume Next statement. You can also mix these blocks inside more sophisticated error handlers. So in the following code, we override the main error handler (On Error GoToErrorHandler:) to perform some specialist error processing (within the On Error resume Next area), and then resume the main error handler once we are finished (by restating the On Error Goto ErrorHandler:):

It is worth emphasizing that just adding On Error Resume Next at the top of a procedure is a very dangerous practice, and if you are setting this around a block of code, you should test for any errors, and then use On Error Goto ErrorHandler: to either switch back on the main error handler, or On Error GoTo 0 to switch off error handling.

There is another alternative to using the On Error Resume Next, which is On Error Resume, which simply means try again. On its own, you are unlikely to use On Error Resume, but if you trap the error, you could ask the user if he wants to retry an operation several times (ensuring that they can escape from any program loop you create).

Err Object

When an error occurs, the error object can be used to display the error number and error description. You can also clear the error object by using the Err.Clear operation to ensure that subsequent errors are correctly managed, as shown in the following example (see for example in modErrorHandler_SimpleError_Handler2a):

On Error GoTo

This mechanism is the preferred way to handle errors because it allows you to define a unique error handling block of code for every procedure, which can then take a specific action of passing the error to a general purpose piece of code. The following code shows the general structure of this error handling code:

Developing a General Purpose Error Handler

In this section, we outline a general purpose routine for recording the errors and allowing the user to decide what to do after an error occurs. The label Errorhandler shown in the code that follows can have the same name in all procedures. To start, you need to decide on what options a user should be presented with:

  • Try again
  • Skip and Continue
  • Exit Chapter

The error handler in the preceding code calls a library routine, and then depending on the user’s interaction with that routine, takes appropriate action.

The code is split into three parts. The first part uses Data Access Objects (DAO) programming to log the error in an error recording table . It also uses a special function called modErrorHandler_GetUserName(), which retrieves the users logon name. This uses a Windows API call. Note that this API call will work only in Office 32-bit; it would need to be modified to work in Office 64-bit. This is shown in the sample database and discussed in Chapter, “Developing Applications.”

A custom error log file.

A custom error log file.

Figure illustrates an example of a form that you can display to log and then take appropriate action when an error occurs (see modErrorhandler_SimpleError_Handler4 to view the code).

A form that provides potential actions for a user to take when an error occurs.

A form that provides potential actions for a user to take when an error occurs.

When you open a target form as a dialog form, your program code pauses until the dialog is closed. Yet the dialog form can also still have its own code that executes even when the calling code is paused. This is a very subtle feature.

OpenArgs and Dialog Forms

Chapter , “Applying the Access Object Model,” shows examples of writing values into the controls of a form after opening the form, and in Chapter 6, “Using Forms and Events,” there are examples showing how to call public code on a form after opening the form. But when the form is opened as a dialog form these techniques cannot be used to manipulate the form that you are opening because the code on the calling form will stop execution after opening the dialog form.

For a dialog form, you can make use of the Open Argument Parameter (variant) when using the DoCmd.OpenForm action (this method is a general technique that can also be used when opening forms other than as a dialog). The following code demonstrates an example of code passing this parameter to a form (in this example, the “|” character separates the parameters):

The sample form called frmUsingOpenArgs then contains the following code on the open event, which uses the Split function to break apart the parameters:

An example form showing the OpenArgs being displayed in a set of controls.

An example form showing the OpenArgs being displayed in a set of controls.

Notice that the message box in the calling code is not executed until you close the dialog form. An alternative method to solving this problem is to load a set of global variables before opening the form, and then read the values of these variables in the form’s Open event.


This is a feature of VBA that you are more likely to use if you decide to create your own VBA classes. Classes are complex objects, and as such, they cannot simply return an error code like a function can. An additional problem with classes is that they need have their own internal error handling but still be able to pass an error back to the program code that uses the class. The error code range 513–65535 is available for you to use for raising error numbers, as demonstrated in the code that follows:

The vbObjectError constant

There is some confusion with respect to a constant called vbObjectError (which has the value –2147221504). The Help system and other sources emphasize that you should add this number to your own error number, but this is not normally required. Summary In this chapter, you were introduced to the VBA Programming Language features. The following points summarize key areas with which you should now be familiar:

  • The use of built-in function libraries
  • How object variables are used in program code
  • The benefits

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

MS Access Topics