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
The Exit Sub statement is required at the end of the error-handling
routine, even though the End Sub statement follows. In place of the
Exit Sub statement, you could use a Resume Next statement to return
control to the line following the line of code that caused the error.
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
The following Sub procedure illustrates how the Error$() function can
create informative error messages to help you and your users debug
your program:
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
You can use the Err() function to build a SQL SELECT statement that
traps for specific errors and takes a different action depending on
the error number. You can use the Erl() function to pinpoint exactly
which line is causing the problem in your program, to anticipate error
conditions in your program, and to handle errors more smoothly.
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
Because there is no possibility that a file called "AUTOEXECBAT" exists,
this line of code errors out and program control passes to the error-
handling routine "ErlTest_Err". The Erl() function detects the number
of the line in which the error occurred and displays the "Could not
open AUTOEXEC.BAT file" error message.