This article is also available as a PDF download.

An unexpected runtime error can stop your application cold.
A runtime error is any error that
occurs while code is running. At the very least, the error will interrupt
someone’s work. At their worst, runtime errors can actually crash your
application. The good news is, you can avoid the problem with just a little
planning and additional code.

On its own, Access displays an error message and then stops
processing code when a runtime error occurs. This lets you, the developer, know
that something’s wrong — that’s a good thing during the developing and testing
stages. But an uncaptured error in a production
database is a problem. Most casual users won’t know how to clear the error and
get back to work. To avoid these work-stopping errors, your database should contain
error handling that does at least one of the following:

  • Ignores the error and continues processing.
  • Fixes the error and continues processing.
  • Requests information from the user and then uses
    that information to continue processing.
  • Terminates the current process without crashing
    the database.

About the Err and Error objects

Visual Basic for Applications (VBA) makes error information
easy to access via the Err object, which stores information about the most
recent error. Here’s a description of the object’s properties you’ll use:

  • The
    Description property     offers a brief description of the error. Sometimes
    these internal descriptions are too vague for users, although they provide
    useful information to the developer.

  • The Number property     stores an internal value that VBA uses to identify an error.
  • The
    Source property     identifies the object or application that generated the
    error.

The Err object has only two methods: The Raise method
generates a runtime error and the Clear method clears the Err object’s current
settings.

The Err object returns errors for VBA and some ADO errors. You’ll find
information about data-provider errors in the Error object, which works
similarly to the Err object. A data-provider
is a special file that connects the database to an external data source. If
there’s a problem with the connection, the Error object takes precedence.

Tip

The HelpContext and HelpFile
properties identify Help topics in the HelpFile property. You probably won’t
use this property unless you’re building a custom Help system to go along with
your database. The LastDLLError property returns a system error code that’s
specific to a called DLL. You won’t use this in normal error handling.

Handling errors

To control what happens when an error occurs, add error-handling
code to each VBA procedure in your database that:

  • Specifies what happens when the error occurs.
  • Handles the error.
  • Continues processing after handling the error or
    returns the user to a familiar state.

To let VBA know where to find directions for handling the
error when the error occurs, use an On Error statement. The simplest form
inhibits the current error and continues with the next statement as if nothing
happened:

On Error Resume Next

This statement is helpful when the code doesn’t need more
information to proceed. For instance, the following code creates an instance of
Word:

On Error Resume Next
 Err.Clear
 'Set appWord object variable to running instance of Word.
 Set appWord = GetObject(, "Word.Application")
 If Err.Number <> 0 Then
 'If Word isn't open, create a new instance of Word.
 Set appWord = New Word.Application
 End If

You could use more complicated error handling, but it isn’t
necessary. First, the code tries to create a Word object using a currently
running instance of Word. If Word isn’t running, the GetObject function generates
a runtime error, but Resume Next inhibits the error and lets VBA create the
Word object using a new instance of Word (by launching Word).

Resume Next has its place, but it isn’t always appropriate.
For the most part, you’ll exercise more control over errors using On Error in
the following form:

On Error GoTo line

where line
identifies the line of code where VBA will find directions for handling the
error as follows:

Sub | Function name ([argumentlist]) As datatype
 On Error GoTo errHandler
 ...procedural code...
 Exit Sub | Function

errHandler:
 ...error-handling code...
End Sub

There are two important things going on. First, you need an
Exit statement before the error-handling
routine. That way, when there’s no error, VBA knows when to exit the procedure.
If you omit the Exit statement, VBA will process the error-handling routine as
part of the procedure. Second, always follow the error-handling routine’s name
with a colon character (:).

Where you position the On Error statement within the
procedural code is important. Initially, you might consider using a generic routine
that you drop into the beginning of every procedure. With time, you’ll learn
how to rely on the code’s purpose to determine the best place to add On Error
statements.

Capturing errors

Capturing an error is relatively simple. As mentioned, Resume
Next inhibits some of them. For instance, the following function multiples a
passed value and returns the result as a Byte variable:

Function MultiplyInput(inputvalue As Byte) As Byte
 Dim product As Byte
 product = inputvalue * 2.5
 MultiplyInput = product
End Function

If the result of the evaluation is greater than a Byte data
type can handle, an overflow value occurs, as shown in Figure A. (Use the Immediate window to test the function.) You can
use Resume Next to inhibit the error, as shown in Figure B. If you do, VBA simply exits the function, which returns 0
— the default value for an undefined numeric variable.

Figure A
Runtime errors halt work.

 

Figure B
Resume Next inhibits a runtime error.

In this case, Resume Next captured and resolved the error,
but you might need more control. You could add code that knows how to handle a
0 value. Or you might add an error-handling routine that shares information
about the error using the Err object’s properties, as follows:

Function MultiplyInput(inputvalue As Byte) As Byte
 On Error GoTo errHandler
 Dim product As Byte
 product = inputvalue * 2.5
 MultiplyInput = product

errHandler:
 MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Error"
End Function

The same error occurs, but this time, GoTo passes processing
control to the error-handling routine, errHandler. Figure C shows the message the routine displays when an error
occurs. This type of information can be useful during the development and
testing stages, but it won’t help your users. First, they won’t understand it.
Second, the function still returns 0.

Figure C
Use an error-handling routine to display information about an error.

Now realistically, you could handle this error by preventing
it in the first place, using a validation rule of some sort to reject inappropriate
values. That way, VBA would pass only those values the procedure can actually
process. Handling an error before it
occurs is always the best line of defense. However, doing so isn’t always
practical, and that’s where error handling helps. Following our example, you
need to tell the user what’s wrong and how to solve the problem using code
similar to the following:

Function MultiplyInput(inputvalue As Byte) As Byte
 On Error GoTo errHandler
 Dim product As Byte
 product = inputvalue * 2.5
 MultiplyInput = product
 Exit Function

errHandler:
 MsgBox "You've entered an invalid number. " _
 & "Please enter a value between 1 and 102.", vbOKOnly, "Error"
End Function

Passing a value larger than 102 generates a runtime error,
and VBA passes control to errHandler, which displays the message box shown in Figure D. The user clicks OK to clear
the error message and then tries again, armed with the information needed to
avoid the error.

Figure D
Use an error-handling routine to instruct users.

Handle, inhibit, or inform

Keep unexpected errors from interrupting work by handling
errors when they occur. If possible, inhibit the error — the user won’t even
know that a problem occurred. If that’s not possible, tell the user what’s
wrong and offer instructions for resolving the problem. Doing so will keep your
users on track and keep you out of the doghouse.

 

Susan Sales Harkins is an independent consultant and the
author of several articles and books on database technologies. Her most recent
book is Mastering Microsoft SQL Server
2005 Express, with Mike Gunderloy, published by Sybex. Other collaborations
with Gunderloy are Automating Microsoft
Access 2003 with VBA, Upgrader’s Guide to
Microsoft Office System 2003, ICDL
Exam Cram 2, and Absolute Beginner’s
Guide to Microsoft Access 2003, all published by Que. Currently, Susan
volunteers as the Publications Director for Database
Advisors. You can reach her at ssharkins@setel.com.