How do I... Obtain and use information about runtime errors in Access applications?

To make sure a runtime error doesn't derail your Access applications, you should include error handling that ignores or fixes the error and continues processing, requests information from the user and then uses that information to continue processing, or terminates the current process without crashing the database. Here's a look at how you can add a little code that will control what happens when an error occurs.

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.