How to Build an Error Handler in Access VBA

Microsoft Access, Microsoft Office, Visual Basic

Dealing with errors in VBA code, especially in Access, is a vital part of setting up procedures. If nothing else, an error-handler can give the user a better idea of what’s glitching, if something does. And having a message box come up to give the user even moderately clear information can be a huge help. It also helps the database admin person, and the VBA coder.

There are normally three main steps to setting up an error handling routine.

First, right up at the top of the procedure, we need a line to tell it what to do for errors. Some people put this above the Dim (variable declaration) statements, some put it below. (I usually put it above. Never had any trouble.) But it needs to go right up top so the procedure “knows” about it immediately. The name for the handler can be whatever you want, but a name like ErrorHandler is a good idea. And each procedure can use the same name—they’re particular to the one they’re in.

The second step is to insert the line “Exit Sub” after all the other code, but before the error routine. This is so the error-handler doesn’t run if it doesn’t need to. In other words, this line will force the “normal” run of the code to bypass, or jump over-and-out-of, the error part of the procedure. (“If we don’t need to do it, go around it.”) There are a few other things we might tell the handler besides “Exit”, such as Resume from some better point in the code, or to end the procedure if the error could cause problems. The key is to plan what should happen, so you can instruct the code to do what you need.

The third is to write the actual error handler routine, usually at the end of the procedure. Its content will vary a lot, depending on what the procedure does. In many cases, the simplest thing is a message box giving some idea of the nature of the error. A box with the error number and whatever description of the error is available would be basic but good. At least it would give the user and the coder something to go on.

Regardless of the error setting in the VBA options, having some mechanism for error handling is a good idea. Even if you can write clean code quickly and easily (congratulations if you can!), you’d be surprised how many weird things can generate errors. Preparing for them is not paranoia, it’s prudence.

For more on this, check out our Access VBA course.