Chapter 25: Handling Errors and Exceptions
Access database applications prepared by even the very best developers have problems. By their very nature, database applications tend to be fairly complicated when you consider table and query design, forms and reports, and implementation details. All the VBA code that needs to be written for most databases can inevitably result in coding errors. If you’re lucky the problem and its cause are obvious and are easy to fix. The situation becomes difficult when you know there’s a problem, but its source is not immediately apparent. The worst situation for all concerned are those bugs that silently and perniciously damage data, or the representation of data in an application, and without any warning.
This chapter takes a look at the types of errors you’ll encounter in Access applications, and some of the steps you can take to uncover and repair these little critters. This chapter largely ignores the errors caused by poor design: those of misrepresentation of data caused by ill-designed queries, update and insert anomalies caused by inappropriate application of referential integrity rules, and so on. For the most part the causes of poor design errors are rooted in failure to conform to proper design disciplines, misunderstanding Access query design, among other causes. Careful attention to database design principles is required to produce truly robust Access applications.
There is no database for this chapter as the chapter contains only fairly simple VBA code examples.
Dealing with Errors
We all have to deal with the errors that occur in our applications. Even the best-written code fails now and then, very often because of problems with data entry or poorly trained users. Other times errors occur because we’ve written the code incorrectly, or we haven’t adequately tested and debugged an application before distributing to users.
Logical errors
Logical errors can be sometimes be difficult to detect and remove. A logical error (a bug), often occurs because of some mathematical error: Perhaps a data was misused of in a recordset, or maybe there is some other problem dealing with the data or program flow of the application, or unexpected input from the user is not properly checked by the application.
For instance, consider the situation where a patient management program assumes the user has entered both the first name and last name for a patient. Let’s also say that a patient data entry form contains text boxes for both these values. So, the user typing in the details will certainly fill in both values. Right?
Either of the first or last names might be missing from the data. There could be a perfectly valid reason. For instance, there could be a patient too young to know their own name or an Alzheimer’s patient who can’t recall his or her last name. A text value left as empty contains a null value, unless a default value has been provided. A logical error is generated if the application then tries to use the patient’s first name in a lookup or sorting operation. If you’re lucky, the user will notice the logical error. The user could be informed through the use of a pop-up dialog box or error message, and the error corrected before the data is committed to the database.
Other logical errors are created when, for instance, an application incorrectly calculates the days between dates, uses the wrong value in a division or multiplication operation, and so on. Virtually any time data is mishandled or inappropriately used in your application, a logical error is likely to be the result.
The obvious solution to the missing first name / last name situation described earlier is to add some VBA code to the form’s BeforeUpdate event to verify that both a first and last name have been entered, and notify the user that one or both names are missing. Alternatively, the application may insert a default value such as “N/A” for the first and last names when either has been left blank.
Even so, a well-mannered application should detect errors when they occur, and handle them gracefully. Access, like most Windows applications, handles errors in a fairly unfriendly fashion, popping up a dialog box that may or may not adequately describe the error to the user. A carefully written application traps these errors before Access takes over, handling them without disturbing the user’s workflow.
Runtime errors
Assuming you get past the syntax checking using the VBA editor and your code compiles, the errors that occur as the user works with an application are generally referred to as runtime errors. Runtime errors occur for a multitude of reasons. When they do occur, a runtime error causes one of following four things to happen:
• A fatal error and the application crashes
• An untrapped error and the default Access error dialog box appears
• The error is handled by the application and your code takes care of the problem
• An unanticipated application error that may or may not cause problems with your Access application
Fatal errors
A fatal error is a non-recoverable and crashes the application. These errors are generally a result of an operation outside the Access environment, and there is no way for Access to handle it. In most cases, your code won’t be able to trap and respond to fatal errors. An example of this type of error is calling a poorly written Windows API function. As you’ll see in Chapter 30, Windows API functions are extremely fussy about the type and number of parameters passed to them. Because Windows API functions execute outside of Access, there is no way for VBA code to trap an API error. In extreme cases, your users encounter the dreaded blue screen of death often seen when a computer is infected by a virus.
Because you cannot do much about these fatal errors other than fix them, you should concentrate on the types of errors you can control.
Simple runtime errors
Runtime errors often give no direct indication of an error condition, such as a pop-up message on a screen. More likely a runtime error is detected after the fact, when something with the data is discovered to be erroneous. For instance, a report may contain blank text boxes when the user expects to see names and other data. This situation could even occur a long time after the data entry error has occurred, and the error may occur many times before its discovery.
On rare occasions, runtime errors are traceable to a hardware failure such as a full disk or network problems, leading to the frequent “*&#^ computer’s fault!” complaint. Modern desktop computers are much more reliable than they were a few years ago, making this a rare occurrence. Most application errors are caused by the people who program computers. Many hardware failures, such as a hard-drive crash, are easily recognized by the user.
Other runtime errors may be less obvious and more difficult to deal with. For instance, a network glitch may cause data loss or make lookup data temporarily unavailable. Running out of swap disk space makes Windows run erratically or crash. Many computers are equipped with marginal memory, making it difficult or impossible to run large queries or use the built-in Access wizards.
A second approach to avoiding runtime errors is to keep an Access database file well maintained by periodically running a compact and repair cycle:
1. Click the round File icon at the very extreme top left of the Access 2007 window.
2. Select the Manage option from the menu.
3. Click the Compact and Repair database option from the menu, as shown in Figure 25-1.
What used to be compact and repair functionality in Access 2003 is now under the Microsoft Office Diagnostics option, in the Windows menu for Microsoft Office.
Figure 25-1
Compacting and repairing a database
Prudence dictates frequent diagnostic attention to an Access 2007 .accdb file, particularly if data is often deleted or modified. A compact and repair cycle can also help to ensure physical integrity of the .accdb file on the hard drive.
Default error handling in Access
Access, Visual Basic, and the other VBA applications, such as Word and Excel, can handle errors for you. Unfortunately, the built-in error handling in most applications is not really intended for end users. Figure 25-2 shows a typical runtime error message produced by Access. Notice how unhelpful the message is. Most users have no idea how to respond to the error message shown in Figure 25-2. This error message includes technical expressions, such as “type mismatch.” Unless appropriate training has been provided, it is unlikely the user will simply guess at the correct action to take in response.
The error in Figure 25-2 occurs because the procedure declares and creates a text string variable and a numeric variable and then tries to assign the numeric variable the value of the text string. The type mismatch occurs because you can’t assign nonnumeric characters to a numeric variable.
Figure 25-2
Built-in error messages are usually not helpful.
The error message in Figure 25-2 reports Runtime error ‘13’:—which happens to be a type mismatch. Unless you know the problem, how does this message help you? Without a great deal of experience, how do you then fix this type of problem? In fact, how do you determine what the problem is? Clearly, this message box is not much help to a user who has entered character data into a text box that should be filled with a numeric value.
In the error dialog box in Figure 25-2, notice the Debug button. The Debug button stops the program, opens the VBA code editor, and places a breakpoint on the offending statement. The program is in a state of limbo. All the values of temporary variables are intact, and you can view them to help you solve the error. The End button causes the program to stop running, and you cannot use any tools to check the problem.
The Access Error dialog box shown in Figure 25-2 appears for untrapped errors. This can be good for development because problems can be traced to the specific line of code that caused the error. When you click the Debug button, the VBA window opens and highlights the guilty line of code. But this is not the kind of reaction you generally want with your applications and end users. For this reason, having an error handler and making it a handled error is much better. You can sometimes not just alert the user of a problem, but maybe prevent the user from even worrying about the problem by having the code take some action to work around the error or correct the problem.
Unanticipated errors
The last type of error is the unknown or unanticipated application error. This is most often a logical error in the code. Often no error is displayed because the program is working exactly the way it was coded. For example, an endless loop occurs if you forget to advance a record pointer as you traverse a recordset or the condition ending a Do.Loop never happens. The problem is that the code is doing the wrong thing, even though it is executing as programmed.
The unanticipated error is often the hardest type of problem to resolve. There are several different ways to handle these errors:
• Check the results programmatically by redundantly checking the results.
• Use the Watch window or breakpoints to watch the code run line by line.
A well-written application may prevent runaway, endless loops by counting how many times the loop has executed and forcing an end to the loop when the maximum value has been exceeded. Other sophisticated ways of handling unanticipated errors include keeping track of the number of times a particular function has been called, or monitoring how long it takes for a query to execute or a form or report to open. Such extreme measures are not necessary in the vast majority of Access applications, but you should be aware that there are solutions to virtually any unexpected application problem.
Which Errors Can Be Detected?
There are several hundred trappable errors that can occur in VBA applications. Only a minor portion of these hundreds of errors are likely to occur in your applications. The question is, then, which of the remaining fifty or one hundred relevant errors should you trap in your applications?
Most developers begin simply and write an error handler that catches one or two of the most obvious errors. In the case of the navigation buttons on an Access or Visual Basic form, you should trap the error that occurs when the user tries to move off either end of the recordset. Such an error is readily anticipated and is the result of normal navigation through a recordset.
However, say a problem makes it so the recordset itself cannot be created. The solution might not be as obvious. There are many reasons why the OpenRecordset method may fail. Perhaps the table cannot be found because it’s been deleted or a link to the table is broken. Or, there could be an error in the SQL statement used to create the recordset and no records are returned. During development you may never see an error caused by an empty recordset because your test data is always available.
Experience will tell you which errors are expected as you write your VBA procedures. But, you should always prepare for the unexpected.
What is an error handle?
VBA provides extensive runtime error handling capabilities. You are able to add code to your applications to detect when an error occurs. Other code directs the program to handle anticipated errors in a predictable fashion. Still other code can catch unanticipated errors, automatically correcting the problem, preventing data loss, and reducing support costs.
Almost all error-handling routines in VBA programs require a three-step process:
1. Trap the error.
2. Redirect program flow to error handler.
3. Direct program flow out of error handler back to main body of procedure.
It is important to note that all VBA error handling is done locally. That is, each procedure contains its own error handling code. Although a procedure’s error handler can call other functions and subroutines, the error handler exists entirely within the procedure causing the error. In fact, as discussed later in this chapter, after you start implementing error handling, it is important to include error handling code in almost every procedure. An error that occurs in a procedure without error handling is passed back to the routine that called the procedure, causing some confusion as to which procedure actually failed.
Setting a basic error trap
The VBA engine is constantly looking for problems and immediately notifies you when something unexpected happens. The default error message is generally technical in nature. A single line of VBA code is all that’s necessary to intercept an error and redirect program flow to code that provides a more user-friendly approach to error resolution.
The following procedure shows how error handling is implemented in VBA procedures:
Sub RoutineA()
On Error GoTo HandleError
MsgBox “Now in routine A”
ExitHere:
MsgBox “Now leaving routine A”
Exit Sub
HandleError:
MsgBox “Error in routine A”
Resume ExitHere
End Sub
The On Error statement near the top of the procedure sets the error trap for the routine. Code near the bottom of the routine implements the error-handling mechanism for this subroutine. The error-handling code in this procedure is the very least you can include in your procedures to handle runtime errors effectively. The error-handling statements in this procedure are the template you can consistently use in all your VBA programs.
The On Error clause informs VBA that you want to defeat the built-in VBA error-handling system by sending execution to the location identified by the “HandleError” label. The GoTo Label statement is an unconditional branch (in the event of an error) to a label somewhere within the procedure.
A VBA label is nothing more than an identifier (such as “HandleError”) followed by a colon. A word by itself on a line in a VBA procedure is interpreted as the name of a procedure, variable, or VBA keyword, and VBA tries to evaluate it. When followed by a colon, the VBA interpreter understands that the word is actually a label and should not be executed or evaluated.
The On Error statement is a switch that disables the default VBA error handling. This statement switches the VBA engine away from its built-in error handling and redirects error handling to your code. After you set the error trap with the On Error statement, you suppress the appearance of the default Access error dialog boxes.
After an error occurs the VBA engine’s normal operation is suspended. Normal execution is directed to the error handler and further error trapping is inhibited. If an error occurs in your error handler VBA responds with its default behavior. Some resources available to you (discussed later in this chapter) determine which error occurred and exactly where the error occurred. You also have several options as to where you want program flow to commence after the error handler has done its job.
It is important to note that there is nothing special about the labels used in the error handling statements. “HandleError” and “ExitHere” are just words; they convey no special meaning to Access or the VBA language engine. Choose any label you wish—HandleError and ExitHere are used throughout this book simply because VBA developers understand these labels. In fact, you can use exactly the same labels and every VBA procedure. This fact makes it easier to copy and paste the error-handling template from one procedure to another in your Access application.
Trapping Errors with VBA
Several situations can cause a great of frustration for your application users: A program that has been operating without a hint of trouble suddenly crashes, popping up a dialog box containing a contradictory or confusing error message. Another problem is a program behaving inconsistently. In one situation the program operates in a predictable fashion, reliably churning out reports and displaying the results of calculations. Under other conditions the program, operating on seemingly identical data, behaves erratically, stopping execution or perhaps displaying the data in unexpected ways.
A third, much more dangerous situation, is a program that appears to be functioning properly but is in fact corrupting data. (Corruption in this context simply means unexpectedly changing the value of the data.) This program silently makes changes to the data or reports erroneous values without indicating an error exists. An example is a program that calculates currency exchange rates. The user of this program may believe the program is correctly calculating the monetary exchange values while in fact the program is actually reporting incorrect results.
The worst type of situation occurs when the values returned by the program appear to be correct, but are in fact wrong.
Using VBA error-handling techniques, you can add code to your applications to prevent unexpected crashes or inconsistent behavior. Unfortunately, there is little you can do to correct a poorly programmed application. If calculations are being performed incorrectly, there is little that the VBA engine can do to correct these types of error. VBA code can be utilized to gracefully cater to unexpected behavior in Access.
Access 2007 provides several basic programming elements used for catering to errors, including the following:
• The Error event
• The Errors collection
• The Err object
• VBA Error statements
The following sections detail each of these program elements.
The Error event
Access provides a special Error event when running a bound form or report. The Error event provides a nice way to trap an error that occurs in the database engine supplying data to the form or report. You need to create an event procedure for the Error event to trap these errors. The procedure looks like one of the following, depending on whether it was a form or a report:
Sub Form_Error(DataErr As Integer, Response As Integer)
‘Insert error handler here
End Sub
Sub Report_Error(DataErr As Integer, Response As Integer)
‘Insert error handler here
End Sub
There are two arguments for these subroutines: DataErr and Response. DataErr is the error code returned by the Access database engine when an error occurs. Note that the Err object is superseded by Error event, and is not helpful when this event is triggered by a problem with the data underlying the form or report. You must use the DataErr argument to determine which error occurred. The second argument, Response, is set to either of the following constants by the procedure:
• AcDataErrContinue: Ignore the error and continue without displaying the default Access error message.
• AcDataErrDisplay: Display the default Access error message. (This is the default.)
When you use AcDataErrContinue, you can then supply a custom error message or handler in place of the default error message.
The following is a typical Form_Error event procedure:
Private Sub Form_Error(DataErr As Integer, _
Response As Integer)
Dim strMsg As String
Select Case DataErr
Case 7787 ‘OverwriteErr:
strMsg = “You lose. Click on OK to see”_
& “updates for other people.”
MsgBox strMsg, vbOKOnly + vbInformation
Response = acDataErrContinue
Case 7878 ‘DataChangedErr:
strMsg = “Another user has changed this” _
& “data while you were looking at it.” _
& vbCrLf & “Click OK to see “ _
& “the other user changes.”
MsgBox strMsg, vbOKOnly + vbInformation
Response = acDataErrContinue
Case Else
‘Default for any other errors:
Response = acDataErrDisplay
End Select
End Sub
This particular error-handling routine traps the errors on a bound form that occur when multiple users make simultaneous changes to the same record. The Access database engine raises the error, allowing the form to intelligently notify the user that a problem has occurred.
Notice how DataErr is examined to see if its value is 7787 or 7878, and an appropriate action (notifying the user of the problem) is taken to handle the problem. Response is set to acDataErrContinue to notify Access that the form’s data error has been handled.
If any other error occurs, Reponse is set to acDataErrDisplay, allowing Access to present the user with the default error message. Hopefully the user can make some sense of the error message, or at least notify someone of the situation.
Keep in mind that the form and report Error event fires only in response to data errors detected by the database engine. The Error event is not related to problems caused by the user, other than inappropriate data entry, and a failure to add or update the wrong kind of data in the database.
The ADO Errors collection
When an error occurs in an ADO object, an error object is created in the Errors collection of the Connection object. These are referred to as data access errors. When an error occurs, the collection is cleared and the new set of objects is put into the collection. Although the collection exists only for the most recent error event, the event could generate several errors. Each of these errors is stored in the Errors collection. The Errors collection is an object of the Connection object, not ADO.
The Errors collection has one property, Count, which contains the number of errors or error objects. It has a value of zero if there are no errors. There are a few properties of the Error object. These include Description, HelpContext, HelpFile, Number, and Source. When there are multiple errors, the lowest-level error is the first object in the collection, and the highest-level error is the last object in the collection.
When an ADO error occurs, the VBA Err object contains the error number for the first object in the Errors collection. You need to check the Errors collection to see whether additional ADO errors have occurred.
In the following code, you find an error handler that can be used in a procedure that deals with an ADO connection. When an error occurs, the code following the label ErrorHandler runs and first checks to see if the Error object contains any items. If it does, it checks to see if the error is the same as the Err object. If it is the same, the error was an ADO error and the variable strMessage contains the descriptions of all the errors in the Errors collection. If it is not an ADO error, the error is from VBA and the single Err.Description value is displayed:
Sub ADOTest()
Dim cnn As New ADODB.Connection
Dim errX As ADODB.Error
Dim strMessage As String
On Error GoTo HandleError
‘Insert your code here
ExitHere:
Exit Sub
HandleError:
If cnn.Errors.Count > 0 Then
If Err.Number = cnn.Errors.Item(0).Number Then
‘Error is an ADO Connection Error:
For Each errX In cnn.Errors
strMessage = strMessage & Err.Description & vbCrLf
Next
MsgBox strMessage, , “ADO Error Handler”
End If
Resume ExitHere
Else
‘The error is a VBA Error:
MsgBox Err.Description, vbExclamation, _
“VBA Error Handler”
Resume ExitHere
End If
End Sub
The Err object
The Err object is a part of the VBA language and is always present in every Access application. When an error occurs, information about the error is stored in the Err object, enabling you to examine the Err object and learn the details of the error.
The Err object contains information about only the most recent error and does not contain information about more than one error at a time. When a new error occurs, the Err object is cleared and updated to include information about that most recent error.
The Err object has several properties, including Number, Description, and Source. The Number is the VBA number of the ; Description gives you a little more information about the error. The Source property is not normally very useful in Access applications; it identifies the VBA project that generated the error, which, in Access applications, is the name of the Access application by default.
The Err object also has two methods: Clear, to clear information from the Err object; and Raise, to simulate an error.
The Description property returns the built-in description of the error that has occurred. Whether you choose to use this description is entirely up to you. Perhaps the most important property of the Err object is the Number associated with the error. The following listing shows how you might use the Err.Number property to determine which error has triggered the error handler.
The following procedure demonstrates the use of the Err object and its Number attribute:
Sub GenericProcedure()
On Error GoTo HandleError
‘Other VBA statements here
ExitHere:
‘Shut down statements here
Exit Sub
HandleError:
Select Case Err.Number
Case X
‘Handle X case
Resume ExitHere
Case Y
‘Handle Y case
Resume ExitHere
Case Z
‘Handle Z case
Resume ExitHere
Case Else ‘Unanticipated error
MsgBox Err.Number & “ “ & Err.Description
Resume ExitHere
End Select
End Sub
The Select Case statement in the error handler, in the procedure called GenericProcedure uses the Err.Number property to execute any of a number of responses to the error. The beauty of Select Case is that the error-handling code can be extended as far as necessary. There is no practical limit on the number of Case statements that can be contained within the Select Case construct, and multiple Err.Number values can be handled by the same Case.
In each Case construct you choose whether to include the Resume ExitHere statement. For instance, perhaps Case Y fixes the problem, and you really want the code to return to the statement that caused the error so that it can be executed a second time. In this case, rather than Resume ExitHere, use a simple Resume statement with no target label. Resume instructs VBA to go back to the statement that caused the problem and execute it again.
Obviously, the Select Case construct is not the only way to handle multiple error conditions. You could, for instance, use nested If..Then..Else and If..Then..ElseIf statements. However, you’ll find that the If statement is not easily extensible and the logical flow through nested If..Then..Else statements can be difficult to follow.
A bit later in this chapter you’ll read about the special Resume statement you use to redirect program flow out of the error handler.
VBA Error statement variations
You’ve already seen several examples of the basic VBA statements for handling errors:
• On Error
• Resume
There are a number of forms of the On Error statement:
• On Error Resume Next
• On Error GoTo <label>
• On Error GoTo 0
An error trap is a section of code that is executed when some kind of an error occurs. That error can be specifically specified, or it can be general. Essentially, when an error is detected by an error trap, then whatever the error trap dictates (the code the trap contains) is what is executed.
There are obviously numerous ways to deal with errors within forms, reports, and code. Each form and report, as well as each function and subroutine, can and probably should have an error-handling routine. It is not unusual to see a good part of the development effort devoted to error handling. Probably the most common routine is the following one:
Function SampleCode
‘Dim statements here
On Error goto HandleError
‘Insert functional code here
ExitHere:
Exit Function
ErrorHandler:
‘error handler code here
Msgbox err.description
‘either enter a resume statement here
‘or nothing and let the function end
Resume ExitHere
End Function
The On Error statement enables the error handler, and if an error occurs, execution branches to the first line after the label ErrorHandler. This label could be any valid VBA label. The error-handler code would deal with the error and then either resume execution back in the body of the procedure or just exit the function or subroutine. The inclusion of the Msgbox statement in the error handler is a typical way of informing the user what happened.
When an error occurs in a called function or subroutine that doesn’t have an enabled error handler, VBA returns to the calling procedure looking for an enabled error handler. This process proceeds up the call stack until an error handler is found. If no error handler is found, execution stops with a default Access error message displayed.
On Error Resume Next
The On Error Resume Next statement ignores the line causing an error. Processing continues at the line immediately following the line causing that error. No error-handling routine is called. This statement is useful if you want to sometimes ignore errors. The following procedure shows that, for any error dealing with the error log table, the rest of the routine will simply be ignored and passed over:
Sub LogError(iNumber As Integer, sDesc As String)
Dim db As Database
Dim rs As Recordset
On Error Resume Next
Set db = CurrentDb()
Set rs = db.OpenRecordset(“SELECT * FROM ErrorLog”)
If Err.Number <> 0 Then
‘Put code here to create The table ErrorLog
End If
rs.Close
End Sub
On Error Resume Next must be used appropriately. After On Error Resume Next is set, Access ignores all errors until the procedure ends or until another error directive is encountered, as in this example:
Sub LogError(iNumber As Integer, sDesc As String)
Dim db As Database
Dim rs As Recordset
On Error Resume Next
Set db = CurrentDb()
Set rs = db.OpenRecordset(“SELECT * FROM ErrorLog”)
If Err.Number <> 0 Then
‘Put code here to create The table ErrorLog
End If
On Error GoTo HandleError
... Other code here ...
End Sub
In this case, the On Error Resume Next causes Access to ignore the error that occurs if the ErrorLog table does not exist. Once past this section of code, the On Error GoTo HandleError statement you’ve seen in several places in this chapter establishes the usual error handler for the remainder of the procedure.
On Error GoTo <label>
The On Error GoTo <label> statement enables an error-handling routine. This is the standard error handling directive described earlier in this chapter. This statement enables error handling for the procedures. Here is another example of using On Error GoTo <label> as a simple error-handling operation:
Sub LogMoreErrors(iNumber As Integer, sDesc As String)
Dim db As Database
Dim rs As Recordset
On Error GoTo HandleError
Set db = CurrentDb()
Set rs = db.OpenRecordset(“SELECT * FROM ErrorLog”)
If Err.Number <> 0 Then
‘Put code here to create The table ErrorLog
End If
rs.Close
ExitHere
Exit Sub
HandleError:
MsgBox Err.Number & “ “ & Err.Description
Resume ExitHere
End Sub
Keep in mind that you are free to use any labels you wish as the targets of the On Error and Resume statements. The labels you see here were chosen simply because of the obvious purposes they serve.
On Error GoTo 0
The On Error GoTo 0 statement disables error handling. This statement also resets the properties of the Err object. The following procedure shows an example of using GoTo 0. After processing has bypassed the Delete method, the On Error GoTo 0 statement disables further error traps. This means any errors that occur after this statement will be handled by the default VBA error mechanism:
Sub DeleteTableDef()
Dim db As Database
Set db = CurrentDb()
On Error Resume Next
db.TableDefs.Delete “tblTemp”
On Error GoTo 0
‘More code here
End Sub
Although in most cases it is not desirable to let VBA handle its own errors, one situation where you may choose to use On Error GoTo 0 is during the development process. Assume you’re working on a complex procedure that has a number of different failure modes. You’re never really sure you’re trapping for all possible errors, so you may want to disable error handling temporarily so that you’ll be sure to see all errors that occur past the error trap you’ve prepared.
VBA Resume statements
As with the On Error statement, there are a number of forms of the Resume statement:
• Resume
• Resume Next
• Resume <label>
Using the Resume statement is all about gaining better and more effective program control over the occurrence of errors.
You shouldn’t simply fall out of the error handlers in your procedure. You’ve probably noticed that the error handler usually appears near the very bottom of a procedure. It’s tempting to just let the End Sub statement after the error handler terminate the procedure after the error has been managed. There are several problems with this approach:
• 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 VBA engine encounters a Resume statement (more on Resume later), or until another error occurs. Even though the end of the procedure resets VBA’s error mode, you should not count on this happening, particularly in deeply nested procedure calls.
• VBA procedures often open recordsets, establish object variables, and perform other tasks that may be left incomplete unless shut down in a predictable fashion. For instance, assume a procedure has opened a disk file and an error occurs. Unless the disk file is explicitly closed you run the risk of damaging the disk’s file structure. Using the Resume statement to redirect flow to the procedure’s shut down code provides a single point at which to close resources that are no longer needed.
Every VBA error handler should include some form of the Resume statement. This special VBA command instructs the VBA engine to resume normal execution. Depending on how you write the Resume statement, you can redirect program execution to any of a number of different points within the procedure.
The GoTo statement will not work in place of Resume. GoTo is an unconditional branch to another location within the current procedure and does not reset the VBA engine error status.
Resume
The Resume statement (with no label) returns execution to the line at which the error occurred. This statement is typically used when the user must make a correction, or when the error handler has repaired the problem causing the error. This might occur if you prompt the user for the name of a file to open and the user enters a filename that doesn’t exist. You can then force the execution of the code back to the point where the filename is requested.
In almost all cases, the Resume keyword assumes that the error handler repairs the error condition. Otherwise you’ll find yourself in an endless loop. Unless the error condition is corrected, every time the line causing the error is executed the error occurs, triggering the Resume statement, causing the cycle to repeat itself an infinite number of times. The following procedure shows how the Resume statement fits into a robust error handler, where judicious use of Resume can simplify coding:
Public Sub ResumeDemo()
On Error GoTo HandleError
‘Statement causing error occurs here:
Kill “C:\Temp.txt”
ExitHere:
Exit Sub
HandleError:
If MsgBox(“Error! Try again?”, vbYesNo) = vbYes Then
Resume
Else
Resume Exit_ResumeDemo
End If
End Sub
If the Temp.txt file cannot be found, processing jumps down to the error handler. A message box pops up with Yes and No buttons on it asking the user whether to try again to delete the file. If the user selects the Yes button (vbYes) processing moves back to the Kill statement. The cycle repeats itself until either the Temp.txt file becomes available and is deleted or until the user clicks the No button on the message box.
Resume Next
When your error handler corrects or works around the problem that caused the error, the Resume Next statement is used. Resume Next returns execution to the line immediately following the line at which the error occurred.
The assumption with Resume Next is that either the error handler corrected the error condition or that the error was relatively minor in nature and that it’s appropriate for processing to simply continue at the statement following the error condition.
The following procedure shows how to use On Error Resume Next. This simple error-logging routine tries to create a recordset object by selecting all fields from a table named ErrorLog. The call to the OpenRecordset method fails if ErrorLog is unavailable. If ErrorLog cannot be opened an error occurs, but because of the Resume Next directive processing simply falls through to the If statement immediately following the OpenRecordset. The code to create ErrorLog is missing from this routine, but the logic should be clear.
Sub LogErrors(iNumber As Integer, sDesc As String)
Dim db As Database
Dim rs As Recordset
On Error Resume Next
Set db = CurrentDb()
Set rs = db.OpenRecordset(“SELECT * FROM ErrorLog”)
If Err.Number <> 0 Then
‘Put code here to create tblErrorLog
End If
rs.AddNew
rs![TimeStamp] = Now()
rs![Number] = iNumber
rs![Description] = sDesc
rs.Update
rs.Close
Set rs = Nothing
End Sub
The LogError sub in the previous procedure does not capture the situation that occurs if ErrorLog cannot be created. In fact, if errors occur as the code tries to assign values to the fields in LogError, processing simply continues to fall through to the next statement until a successful statement is encountered. In most cases this means execution ends up in an unpredictable location. It also means subsequent errors are not properly trapped.
Resume <label>
Resume <lable> is the standard method for exiting an error handler. If you need to continue execution at some other place besides the line that caused the error or the line after the line that caused the error, the Resume <label> statement should be used. It returns execution to the line specified by the label argument.
The label must be a label appearing within the current procedure. You cannot resume execution at a point outside of the currently executing procedure. If you must use the code in another procedure as part of the error handler, simply call it at a point above the Resume statement.
When using error traps, one option is to redirect processing to an error trap, and log the error to a log file. After that, you could always continue processing. The result is that processing is not halted, perhaps prudent for less critical error situations.
One important aspect of Resume <label> is that program execution is typically directed to the procedures exit point. This gives you a handy place to put all of a procedure’s clean-up code, so that it executes whether or not an error occurs:
Sub LogErrors(iNumber As Integer, sDesc As String)
Dim db As Database
Dim rs As Recordset
On Error GoTo HandleError
Set db = CurrentDb()
Set rs = db.OpenRecordset(“SELECT * FROM ErrorLog”)
If Err.Number <> 0 Then
‘Put code here to create tblErrorLog
End If
rs.AddNew
rs![TimeStamp] = Now()
rs![Number] = iNumber
rs![Description] = sDesc
rs.Update
ExitHere:
‘These steps are followed whether or not an error
‘has occurred. This means there is a single place
‘in this procedure for “clean up” code:
rs.Close
Set rs = Nothing
Exit Sub
HandleError:
‘Handle the error here
Resume ExitHere
End Sub
In this short example, the statements following the ExitHere label are executed whether or not an error has occurred. You should always close recordset objects and set them to Nothing to conserve memory. These “clean up” statements normally appear near the bottom of procedures, but in this example are located midway through the subroutine. Execution of this procedure actually ends when the Exit Sub statement executes.
Summary
This chapter surveys the important topic of adding error handling in Access applications. All VBA hosts (Access, Word, Excel, and so on) use identical error-handling paradigms. This means that all the code you saw in this chapter is applicable to any VBA host application.
Error handling is enabled with the On Error keywords. The typical error-handling process is to trap the error, redirect program execution to the code segment handling the error, and then resume out of the error handler. Most procedures use the Resume statement to redirect program flow to a common exit point in the procedure. The code following the exit label performs any clean up (closing and discarding object variables, closing files that are open, and so on) and is executed whether or not an error occurs in the procedure.