A LAST LOOK AT ERROR-HANDLING ISSUES
The following sections cover some of the issues to watch for when creating error handlers for your routine. Watching for issues such as environment switches and knowing how to handle transactions correctly can save you from many hassles later.
Watching for Environment Switches
When setting Echo off, HourGlass on, and SetWarnings off in your routines, be sure to toggle them back to the way you want them in your error handler. These environment settings affect what happens in the application's environment. This is a good reason for using a label to exit your routine and placing these commands in the code to toggle the settings as necessary. Listing 7.18 shows an example.
Listing 7.18. VideoApp.mdb: Resetting the Environment Settings
Sub ShowErrorCleanUp() On Error GoTo Error_ShowErrorCleanUp DoCmd.Hourglass True DoCmd.Echo False, "Displaying Message" DoCmd.SetWarnings False DoCmd.DeleteObject acTable, "NoTable" '-- This label starts the cleanup and exit section Exit_ShowErrorCleanUp: DoCmd.Hourglass False DoCmd.Echo True DoCmd.SetWarnings True Exit Sub '-- This label is for the error handler Error_ShowErrorCleanUp: MsgBox Err.Description, vbCritical, "Delete Table Error" Resume Exit_ShowErrorCleanUp End Sub |
The problem with this way of switching the environment variables back is that they aren't switched until after the error message box appears. This can confuse users when the cursor is still shown as an hourglass and not a pointer to the message box. Figure 7.14 shows this problem.
Figure 7.14. When the cursor is still shown as an hourglass after an error occurs, users might become confused.
To solve the problem, repeat the commands in the error handler and then just exit the routine, so you don't perform the commands twice. Listing 7.19 shows this method.
Listing 7.19. VideoApp.mdb: Preventing the Resetting of the Environment Switches
Sub ShowErrorCleanUp2() On Error GoTo Error_ShowErrorCleanUp2 DoCmd.Hourglass True DoCmd.Echo False, "Displaying Message" DoCmd.SetWarnings False DoCmd.DeleteObject acTable, "NoTable" DoCmd.Hourglass False DoCmd.Echo True DoCmd.SetWarnings True Exit Sub '-- This label is for the error handler Error_ShowErrorCleanUp2: DoCmd.Hourglass False DoCmd.Echo True DoCmd.SetWarnings True MsgBox Err.Description, vbCritical, "Delete Table Error" Exit Sub End Sub |
Notice the cleanup commands in the error handler itself. The Exit label is unnecessary at this point.
Using Your Error Handler to Roll Back Transactions
You can do transaction processing by using VBA. With transaction processing, you can wrap multiple tasks into one transaction. If one task fails, the whole transaction can be rolled back, and all the tables involved can be restored to where they were before any tasks were begun. This is where error handlers come in. You'll most likely use the error handler to roll back transactions.
Note
Transaction processing also gives the Jet engine more information about when to cache data. This gives the engine greater speed for the tasks that need to be done and also provides better multiuser concurrency.
Listing 7.20 shows that even though the error occurred after the deletion of records, the transaction is rolled back and the records are still in the table. The Err.Raise method is used intentionally to cause an error.
Listing 7.20. VideoApp.mdb: Rolling Back the Transaction on an Error
Caution
You must have a RollBack or CommitTrans in your code! If you don't complete the transaction, using one command or the other keeps the recordset open—and locked—until you quit the application (even if you closed the recordset or have the recordset variable declared as Local).
Transaction processing is straightforward to use, and very powerful; however, you need to have error handling involved to make it robust as well.
Using a Form's On Error Event
In VBA, you can use error handlers to trap errors. But what about when users are in a form and try to delete a customer that has invoices? If you have set up referential integrity correctly, the user sees a dialog similar to Figure 7.15.
Figure 7.15. The default error message appears when users try to delete customer records with invoices where referential integrity is set.
Users sometimes need a more understandable message than the messages for Access and Jet errors. Access gives you a method for rewording or handling errors on forms and reports with the Form/Report error handler.
Figure 7.16 shows the property sheet for the Customers form in VideoApp.mdb. You can see the On Error event listed here.
Figure 7.16. The On Error event is available on forms and reports.
When you take advantage of the On Error event, you can catch and change the referential integrity error shown earlier in Figure 7.15. Listing 7.21 shows the code used to perform this task, assigned to On Error.
Listing 7.21. VideoApp.mdb: Handling Form Errors
Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 3200 Then
'-- Can't delete because of Referential Integrity
Beep
MsgBox "Sorry, but since this customer has invoices, " & _
"they can't be deleted."
Response = acDataErrContinue
End If
End Sub
|
The On Error event uses two parameters:
DataErr is the error number.
Response tells Access how to continue with the error. acDataErrContinue doesn't display an error and continues handling the error, whereas AcDataErrDisplay displays the error.
Note
Although you can display new messages and clean up errors, you can't cancel an error that occurred with the Form/Report error handler.
The code used in Listing 7.21 creates the new message box, shown in Figure 7.17.
Figure 7.17. This message is a little easier for users to understand.
Tip
You can have the Form/Report error handler look up the error inside the tblErrorInfo table, thereby using the custom messages found there. When you're trying to give detailed custom messages, it might be hard to look up errors generically for a whole application.
Nesting Error Handlers
You can nest error handlers in such a way that you can just have a main handler per each chain of routines that's called. Assume that you have two routines—Routine A calls Routine B. Here are three scenarios:
Routine A has an error handler; Routine B doesn't. If an error occurs in Routine B, it uses Routine A's error handler.
Routine A has an error handler; so does Routine B. If an error occurs in Routine B, its own error handler takes precedence.
If neither has an error handler, Access's default error handling takes over.
Looking at Some New Options for Error Handling
VBA has some new options that affect how code breaks when errors occur. You can see these settings on the General page of the Options dialog (while in the VBE, choose Options from the Tools menu). Now click the General tab. In the middle right side of the page are three options in the Error Trapping section (see Figure 7.18):
Break on All Errors—This choice breaks on all errors, whether or not custom error handling is invoked. This is good when you're debugging your application, but not when you distribute it.
Break in Class Module—This breaks only in a class module (discussed in Chapter 2, “Coding in Access 2000 with VBA”), unless you've placed error handling in them.
Break on Unhandled Errors—Errors that aren't handled by you with the On Error statement break whether or not they're in a class module or public module.
Figure 7.18. These options specify how you want Access to handle errors during runtime.
