Taking More Control of Access

Responding to Runtime Errors

Whether you're creating an Access application to use yourself, for a few colleagues to use, or for a much wider audience, your objective should be to do all in your power to make sure errors can't occur when your application runs. Unfortunately, in all but the simplest applications, that ideal is not possible because you're not completely in control of what a user does.

Three categories of errors are possible in an application:

• Syntax errors  These are errors in the use of the Visual Basic language. The Visual Basic Editor detects these types of errors automatically so that you can correct them.

• Logic errors  Even though your VBA code has no syntax errors and it runs without indicating the presence of errors, it might not produce the results that you intend due to the presence of logic errors. You can find errors of this type only by extensively testing your application using a wide range of conditions and circumstances.

• Runtime errors  These are errors that occur due to some unanticipated problems while your program is running.

This section shows you how to deal with runtime errors.

A few of the things that can happen while your application is being used are as follows:

• Enough disk space might not be available for an operation.

• A file that's needed might not be present or might not be accessible.

• An attempt might be made to divide by 0 (zero).

• A printer might not be available, might not be turned on, might have jammed, or might run out of paper.

• An attempt to make a dial-up connection might fail because the phone line is busy.

These are just some specific examples that are quite easy to think of. For each of these examples, you could probably write code that checks for that specific error condition before allowing code that might encounter that error to run. For example, you could write code that compares the disk space required by some data with the available disk space before the code that attempts to write data to disk runs. That might be okay if you're concerned only with a disk on the user's local computer to which only that user has access. What happens, though, if data can be written to a shared disk? In that case, it's possible that in the interval between testing the disk space and attempting to write to the disk, someone else writes a file to the same disk.

The point is that, however hard you might try to anticipate possible errors, you're unlikely to be completely successful. For that reason, VBA makes it possible for you to detect almost any error that occurs while an application is running and to write code that allows a user to deal with that error. The errors that you can detect and deal with in this way are known as trappable errors. The methods of dealing with these errors are known as error trapping. By incorporating error trapping in your VBA code, you ensure that your application isn't likely to crash when the unexpected occurs

The Help topic "Trappable Errors" contains a complete list of trappable errors. Each trappable error has an error number and an error message.

The general method for trapping errors has four parts:

• Setting the trap

• Creating an error handler

• Resuming program execution

• Clearing the trap

These four parts are described in the next sections.

Setting the Error Trap

Each procedure should normally have a statement that sets an error trap before the first executable statement. Most people place this statement immediately after any declaration statements at the beginning of a procedure. The syntax of an error trapping statement is this:

On Error GoTo linelabel

Here, linelabel marks the place in the procedure where error-handling statements begin. A line label is text followed by a colon. No spaces or punctuation characters are allowed in a line label.

Listing 2 is a skeleton of a procedure with error trapping included.

Listing 2  Typical VBA Code to Set an Error Trap

Sub SubName()
   On Error GoTo ErrorHandler
   normal procedure statements
   Exit Sub
ErrorHandler:
   error handler statements
End Sub

When this procedure executes, the first statement sets up error trapping for all the subsequent statements in the procedure. Two possibilities exist:

• All the subsequent statements execute without detecting a trappable error. In this case, the statements proceed as far as the Exit Sub statement, at which time the procedure terminates and error trapping is turned off.

• A trappable error occurs when one of the statements in the procedure executes. At the first trappable error, execution switches to the first statement after the ErrorHandler line label and proceeds from there.

Now, let's turn to a practical example. Previously in this chapter, the section "Executing DoCmd Methods" showed this example of an event procedure:

Private Sub cmdSpreadsheet_Click()
   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
   "tblPublisher", "C:\My Documents\Publisher.xls"
End Sub

As explained in that section, this event procedure attempts to copy the content of an Access table to an Excel workbook. The statement creates the file C:\My Documents\Publisher.xls if that file doesn't already exist. If the file does already exist, the statement alerts you to that fact and asks whether you want to replace the existing file.

It's quite possible that this event could cause an error for several reasons, one of the most obvious of which is that there isn't enough space on the disk for the new Excel workbook. To control what happens when an error occurs, you need to create an error handler—VBA code that executes when an error occurs.

After you've set an error trap, that trap remains in effect until one of the following statements executes:

Err.Clear

Exit Function

Exit Property

Exit Sub

On Error GoTo 0

On Error GoTo line

On Error Resume Next

Resume

Resume line

Resume Next

Instead of trying to remember that list, think of the lifetime of an error trap in this way. An error trap remains in effect until one of the following happens:

• You explicitly clear it with an Err.Clear or On Error GoTo 0 statement.

• The end of the procedure in which the trap is set is reached.

• An error is trapped and then code execution resumes.

As mentioned in the section "Using Custom Procedures," previously in this chapter, you can call a procedure from within another procedure. If you set an error trap in one procedure and, while that error trap is in effect, call another procedure, the error trap remains in effect while the called procedure runs.

Creating an Error Handler

You can incorporate error trapping into the procedure described in the previous section by modifying it as shown in Listing 3.

Listing 3  Error Handler with Message to the User

Private Sub cmdSpreadsheet_Click()
   On Error GoTo ErrorHandler
   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
   "tblPublisher", "C:\My Documents\Publisher.xls"
   Exit Sub
ErrorHandler:
   MsgBox "Can't copy the data to a spreadsheet."
   Resume Next
End Sub

With this improvement to the code, the data is successfully copied to the workbook if no error occurs. The code executes as far as the Exit Sub statement, at which point the procedure terminates. If you omit the Exit Sub statement, the subsequent error-handler code executes, even if no error occurred.

If an error, such as insufficient space on the disk, does occur, the error-handler section of the code displays a message saying that the data can't be copied. The Resume Next statement causes the execution of the next statement after the one that caused the error. The overall result is that the data isn't copied to the workbook, but the user can continue with other operations.

The error-handler section of the code can do much more than is shown in this simple example. For example, the error-handler section could make it possible for a user to save the workbook on another disk. But that assumes that the reason for the user being unable to save the workbook is that insufficient disk space is available. Perhaps there are other reasons for being unable to save the worksheet.

Analyzing Errors

When an error occurs, VBA creates an error object, Err, that has several properties, the most useful of which are listed here:

• Number—Error number

• Description—Text that describes the error

• Source—Name of the project in which the error occurred

You can use these properties in an error handler as shown in Listing 4.

Listing 4  Error Handler with Number, Description, and Source Properties

Private Sub cmdSpreadsheet_Click()
   Dim errNumber As Integer
   Dim errDescription As String
   Dim errSource As String
   Dim strMsg As String

   On Error GoTo ErrorHandler
   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
   "tblPublisher", "C:\My Documents\Publisher.xls"
   Exit Sub
ErrorHandler:
   strMsg = "Error number is: " & Err.Number & vbCrLf
   strMsg = strMsg & "Error message is: " & Err.Description & vbCrLf
   strMsg = strMsg & "Error source is: " & Err.Source
   MsgBox strMsg, vbOKOnly + vbCritical, "Error"
   Resume Next
End Sub

The message box this code creates provides you, the developer, with a lot of information about the reason that errors occur; you'll find this type of code in error handlers very useful while you're optimizing an application. However, this type of information is not meaningful to people who use your application. For those people, you need to employ a different strategy: code that identifies possible errors and suggests a remedy for each one. This involves the use of the Select Case control structure. .

The Select Case control structure allows you to identify specific errors by their error numbers and to write code that's appropriate for each type of error. Listing 5 is a skeleton example.

Listing 5  Example of Using the Select Case Control Structure to Identify Errors

Private Sub cmdSpreadsheet_Click()
   On Error GoTo ErrorHandler
   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
   "tblPublisher", "C:\My Documents\Publisher.xls"
   Exit Sub
ErrorHandler:
   Select Case Err.Number
      Case 58    'File already exists
         code to solve problem
      Case 61    'Disk full
         code to solve problem
      Case 68 'Device unavailable
         code to solve problem
      Case Else 'Other errors
         Code to solve other problems
   End Select
End Sub

This error handler uses the Select Case control structure to look for specific error conditions recognized by their error numbers, and provides individual solutions for each type of error. You can use this type of error handler to provide specific solutions for the most likely problems and to provide a generic solution for any other problems.

Testing Your Error-Handling Strategies

After you've written an error handler, you need to test it to make sure it works correctly. It's impractical to set up all possible error conditions; fortunately, you don't have to. Instead, you can temporarily modify your code to make Visual Basic think that certain errors have occurred. This is known as raising errors. You specify which error you want to simulate by its error number.

For example, to raise error number 7 (out of memory), insert the statement

Err.Raise 7

somewhere in your code after the On Error GoTo statement and before the Exit Sub statement. When you run the code, your error handler does whatever it would do if that particular error had actually occurred.

Resuming Code Execution After an Error

With an error handler placed at the end of a procedure, immediately ahead of the End Sub statement, the procedure terminates after the code in the error handler has been processed, unless you specify otherwise.

You can use a Resume statement to control what happens after an error has been processed. A Resume statement can occur only within an error handler; an error occurs if you place a Resume statement elsewhere in your code. A Resume statement can be written in the three forms shown in Table 4. The explanations in that table apply when an error occurs in the same procedure as the error handler.

Table 4 Action of a Resume Statement When an Error Occurs in the Same Procedure as the Error Handler

The effect of a Resume statement is somewhat different if an error trap is set in one procedure from which another procedure is called and the error occurs in the called procedure. Table 5 explains what happens in this case.

Table 5 Action of a Resume Statement When an Error Occurs in a Called Procedure