Part III: More-Advanced Access Techniques
832
In this example, the Kill statement is used to delete a temporary file that may or may not exist on
the C: drive. Kill throws an error if the file does not exist, so On Error Resume Next is used
as an easy way to safely ignore the “File not found” that may occur.Caution
On Error Resume Next must be used with caution. Once On Error Resume Next is set, Access ignores all
errors until another error directive is encountered, as in this example:
Sub DoSomething()
On Error Resume Next
Kill “C:\Temp.txt”
On Error GoTo HandleError
‘... Other code here ...
End SubIn this case, the On Error Resume Next causes Access to ignore the error that occurs if the temporary file
does not exist. Once execution is past this section of code, the On Error GoTo HandleError statement
establishes the usual VBA error handler for the remainder of the procedure.
Tip
It is probably best to reserve the On Error Resume Next statement for situations where its special behavior
is needed.
VBA Resume statements
In earlier examples, you’ve seen the Resume ExitHere as a way to redirect processing out of a
procedure’s error handler. As with the On Error statement, there are a number of forms of the
Resume statement:l (^) Resume
l Resume Next
l (^) Resume Label
Using the Resume statement is all about gaining better and more effective program control over
the occurrence of errors. In any of its forms, the Resume statement redirects processing to another
location within the current procedure.
As a general rule, you shouldn’t simply fall out of the error handler at the bottom of a procedure.
You’ve probably noticed that the error handler usually appears near the bottom of a procedure. It’s
tempting to just let the End Sub or End Function statement after the error handler terminate the
procedure after the error has been managed, but there are several problems with this approach:
l (^) The VBA error mechanism is left in an indeterminate state. You’ll recall that as soon as
the error occurs, VBA enters a special “error” mode. This mode persists until the Err.
Clear method is invoked or the VBA engine encounters a Resume statement, or until
another error occurs. Even though the end of the procedure resets VBA’s error mode, you
shouldn’t count on this happening, particularly in deeply nested procedure calls.