ACC: Access Basic Error-Handling Supplemental Information

• Microsoft Access versions 1.0, 1.1, 2.0

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article supplements the information available in the Microsoft Access "Language Reference" manual about how to handle errors in an Access Basic application.

MORE INFORMATION

There are times when your code becomes susceptible to user error problems, even though the code itself is proven. The larger the program, the more often situations arise that were not anticipated at the time the code was written. For this reason, always include error-handling routines in your code to prevent your application from crashing when unexpected errors occur. Error-trapping routines offer significant help in the debugging process.

On Error Statement

Access Basic includes the On Error statement for error trapping, as well as several other commands for reporting and processing errors. The On Error statement is followed by either a GoTo clause, which causes program control to branch to a predefined label when an error occurs, or by a Resume Next clause, which tells Access Basic to ignore the error condition and continue to the next statement.

To create a label for an On Error GoTo statement, type the name of the label, followed by a colon (:). The commands that follow the label are executed when control is passed to the label by the On Error GoTo statement.

Generally, you should place the label and its accompanying error-handling routine at the bottom of the procedure, preceded by an End Sub or End Function statement, to ensure that the routine executes only when branched to and not by mistake.

NOTE: A label does not necessarily indicate a break in program flow.

Shown below is a Sub procedure that performs a SQL action based on a SQL string value. The On Error Resume Next statement is used to handle any errors generated by the first DeleteQueryDef method (for example, if "TempQuery" does not exist). On Error GoTo SQLError means that if any other errors occur, program control passes to the SQLError error-handling routine. The On Error GoTo 0 statement is used at the end of the procedure to disable error trapping.

   Sub PerformSQLAction (InDB As String, SQLStmt As String)
      Dim SQLDb As Database, SQLQuery As QueryDef

      On Error Resume Next
         Set SQLDb = OpenDatabase(InDB)
         SQLDb.DeleteQueryDef ("TempQuery")
      On Error GoTo SQLError:
         Set SQLQuery = SQLDb.CreateQueryDef("TempQuery", SQLStmt)
         SQLQuery.Execute
         SQLQuery.Close
         SQLDb.DeleteQueryDef ("TempQuery")
      On Error GoTo 0
      Exit Sub

   SQLError:
      MsgBox "An error occurred while executing the SQL statement."
      Exit Sub
   End Sub

Err(), Erl(), and Error$() Functions

Access Basic includes the Err(), Erl(), and Error$() functions, all of which return information about the error that occurred. The Error$() function returns the error message as a string, the Err() function returns a number representing the error message, and the Erl() function returns the number of the line in which the error occurred. Using these functions, you can code your error-trapping routine to display meaningful error messages and trap for specific errors.

A generic error-trapping routine that uses both Error$() and Erl() functions is shown below. This type of routine tells you what error occurred and where; it also allows you to either ignore the error and continue or cancel execution and further investigate the problem.

   On Error GoTo ErrorHandler

   ErrorHandler:
      If MsgBox("The following error has occurred at line " &_
         Trim(Str(Erl)) & ":" & Chr(13) & Chr(10) & Chr(13) &_
         Chr(10) & Error$, 17) = 1 Then Resume Next Else Stop

   Sub MyError ()
      On Error GoTo ErrorHandler
      INTEGERVAL% = 99999 'Generates Numeric Overflow error
      Debug.Print "Error was ignored"
      Exit Sub

   ErrorHandler:
      If MsgBox("The following error has occurred at line " &_
         Trim(Str(Erl)) & ":" & Chr(13) & Chr(10) & Chr(13) &_
         Chr(10) & Error$, 17) = 1 Then Resume Next Else Stop
      Exit Sub
   End Sub

If you use the Erl() function, you do not need to number every line of your code to find the offending line number. Instead, number only those lines of code most likely to cause problems. (The line numbers represent labels, rather than classic line numbers, as in other Basic languages.) Then, if an error occurs in one of the numbered lines, the Erl() function reports only that line number.

You can also use this method if you have two lines of code that may generate the same error and you want to handle each case separately, as in the following example:

   Function ErlTest()
      On Error Goto ErlTest_Err
      10: Open "AUTOEXECBAT" For Input As #1 'causes an error.
      20: Open "CONFIG.SYS" For Input As #2
      Close
   Exit Sub

   ErlTest_Err:
      If Erl = 10 Then
         MsgBox "Could not open AUTOEXEC.BAT file."
      ElseIf Erl = 20 Then
         MsgBox "Could not open CONFIG.SYS file."
      End If
      Exit Sub
   End Sub

REFERENCES

Microsoft Access "Language Reference," version 1.0, pages 331-333

For more information about error-handling routines, search for "Error handling" using the Microsoft Access Help menu.