Handling Errors in VBA

When one of the statements in your VBA code cannot be successfully executed at run time, an error condition is raised. If you have made provision for that possibility, your code can recover gracefully and continue or terminate as appropriate; if not, Access will do its best to handle the error itself -- usually not in the way you would prefer.

An untrapped, or unhandled, error is one that is raised by your application and not handled by error-handling code that you write. Such errors are then dealt with by Access's default error-handling routine, which displays the description of the error and, depending on your option settings, may allow you to debug the code. But it also tends to reset the VBA project, so that all global variables are returned to their uninitialized states. And if the database is run using the Access run-time module and not the full version of Access, the application will simply shut down.

To avoid having this happen, put error-handling code in (at least) all your top-level VBA procedures. By "top-level" procedures, I mean those that are not called by other procedures you write, but rather are triggered by events. You can also write whatever specialized error-handling you want for lower-level procedures that are called from the top-level procedures, but if a lower-level procedure doesn't have its own error-handling code, its errors will be handled by a higher-level procedure's error-handler, if there is one, so you don't *necessarily* have to write an error-handler for every procedure.

By error-handling code, I refer to using the On Error statement to define what will happen and where code execution will continue in the event of an error being raised by your code. Most often you will want to use the "On Error GoTo" form of the statement, to transfer control to an error-handling section in the procedure, from which section, eventually, the Resume statement is used to continue execution after the error has been dealt with. An alternative to this is "in-line" error-handling, which is done by using the "On Error Resume Next" statement. In that case, your own code checks after executing each statement, to see if an error has occurred, and deals with errors right there.

Here's a very basic example of error-handling using "On Error GoTo", with comments on the essential elements:

code: click in the frame, Select All, then Paste into your code editor

Here's an example of in-line error-handling:

code: click in the frame, Select All, then Paste into your code editor

Note that you can use a combination of error-handler blocks and in-line error-handling in the same procedure, and you can have more than one error-handler block. Also, you can write your error-handler block to resume execution at different locations depending on your evaluation of the error that occurred. Error-handling can be quite sophisticated, if you take the trouble to program it that way.

For more information about VBA error-handling, I suggest you start reading with the VBA help-file topic for the On Error statement, and branch out from there.