Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

Creating a Generic Error Handler

A

generic error handler can be called from every procedure in your application to respond to any type of error.

A generic error handler prevents you from having to write specific error handling in each of your subroutines and functions. This enables you to invoke error handling throughout your application in the most efficient manner possible.

You can take many approaches to create a generic error handler. It should give users information about the error, enable users to print this information, and log the information to a file. You might even want to email this information to yourself, or to someone else in charge of maintaining the database.

The On Error routine (in this case, the label AnySub_Err) of every procedure that performs error handling should look like the error-handling routine contained in the subroutine in Listing 16.13.

Listing 16.13 A Generic Error Handler for All Your Functions and Subroutines

This error-handling routine in AnySub creates an Integer variable that holds the return value from the error system. The intAction variable holds an appropriate response to the error that occurred. The error routine calls the generic error-handling function ErrorHandler, passing it the error number (Err.Number), a description of the error (Err.Description), the name of the module containing the error, and the name of the subroutine or function containing the error. The name of the module is stored in a Private constant called MODULENAME. The Private constant is declared in the General section of the module and needs to be created for every module you make. The name of the subroutine or function is stored in a local constant called SUBNAME. With this approach, you create a local constant and assign it the name of the sub at the beginning of each procedure. This requires upkeep, because procedure names can change, and you need to remember to change your string. Unfortunately, because the VBA environment does not expose the subroutine and module names to you when an error occurs, this sort of brute force is necessary if you want your error handler to track the subroutine and module. When the code returns from the ErrorHandler function, a return value is placed in the intAction variable. This return value is used to determine the fate of the routine.Chapter 31, "Third-Party Tools That Can Help You Get Your Job Done Effectively." Among its many features, it allows you to create a custom error template that you can use to quickly and easily add error handling to all of the routines that you create.

Now that you have seen how to implement error handling in your procedures, take a look at the function that's called when an error occurs, as shown in Listing 16.14.

Listing 16.14 A Type Structure Declaration to Be Used for Generic Error Handling

This code is placed in the General section of basHandleErrors. The type structure declared holds all the pertinent information about the error. A

type structure is a special kind of variable made up of various parts, each of which stores a different piece of information. (Type structures are covered in Chapter 12, "Advanced VBA Techniques.")

In Listing 16.14, the public variable gtypError holds all the information from the type structure. The constants are used to help determine the fate of the application after an error occurs. Listing 16.15 shows the ErrorHandler function.

Listing 16.15 Using the ErrorHandler Function

The ErrorHandler function receives the error number, error description, module name, and subroutine or function name as parameters. It then fills in the gtypError type structure with the information that it was passed, as well as the current user and date. Next it calls a routine that logs the error into an Access table. The routine looks up the severity of the error code in an Access table called tblErrors to decide the most appropriate way to handle the error. If it does not find the error code in the error table, the code displays an error form, and it sends a return value to the calling function, indicating that application execution is to be terminated. If it finds the error code in the tblErrors table and determines it to be critical or severe, the code displays an error form before it returns control to the calling routine. In any case, the code returns a severity code for the error to the calling function. The following section discusses the details involved in each step of the process.

Logging the Error

The LogError routine is responsible for logging all the error information into an Access table. Because users often decide not to print the error form or provide you with inaccurate information about what was happening when the error occurred (or neglect to tell you about the error), it is important that you log each error so that you can review the error log at any time. You can log errors to a text file or to a data table. This section shows you both methods of logging your errors. Start with logging your errors to a table, as shown in Listing 16.16 with the LogError routine.

Listing 16.16 Using the LogError Routine

[View full width]

Sub LogError()
'Declare a Connection object
Dim cnn As adodb.Connection
Dim strSQL As String
'Point the Connection object at the connection
'associated with the current project
Set cnn = CurrentProject.Connection
'Build a SQL statement that inserts error information
'into the tblErrorLog table
strSQL = "INSERT INTO tblErrorLog ( ErrorDate, ErrorTime, UserName, ErrorNum,
ErrorString, ModuleName, RoutineName) "
strSQL = strSQL & "Select #" & gtypError.datDateTime & "#, #" _
& gtypError.datDateTime & "#, '" _
& gtypError.strUserName & "', " _
& gtypError.lngErrorNum & ", '" _
& gtypError.strMessage & "', '" _
& gtypError.strModule & "', '" _
& gtypError.strRoutine & "'"
'Execute the SQL statement
cnn.Execute strSQL, , adExecuteNoRecords
End Sub

This routine uses the Execute method of the ADO Connection object to add a record to your error table. The record contains all the information from the structure called gtypError. The code logs the information to a table called tblErrorLog. Figure 16.4 shows the structure of this table.

The alternative error-logging method is to write the information to a textual error log file, as shown in Listing 16.17.

Listing 16.17 Writing Information to a Textual Error Log File

This code uses low-level file functions to Open and Write to an ASCII text file. The code writes all the pertinent information about the error to this text file. The routine then uses the Close command to close the text file. The potential advantage of this routine is that, if the problem is with the database (for example, the network is down), the error-logging process still succeeds.

Determining the Appropriate Response to an Error

After the code logs the error, you are ready to determine the best way to respond to the error. By making your error system data-driven, you can handle each error a little differently. Figure 16.5 shows the structure of the tblErrors table. This table should contain a list of all the error numbers you want to trap. It contains three fields: ErrorNum, ErrorDescription, and Response. When an error occurs, the ErrorHandler function searches for a record with a value in the ErrorNum field that matches the number of the error that occurred.

The ErrorHandler function, as shown in Listing 16.15, uses the code in Listing 16.18 to locate the error code in the tblErrors table.

Listing 16.18 Locating the Error Code in the tblErrors Table

The part of the ErrorHandler function shown in Listing 16.18 creates an ADO Recordset object variable. It opens a recordset using a Select statement, which in turn searches a table called tblErrors. If a match is found, the code uses the Response column to determine the response to the error. Notice in Listing 16.18 that, if the error number is not found in tblErrors, default error handling occurs, which means that the code handles all other errors as a group. (This is my default error handling, not Access's.) If the error number is found, the code evaluates the Response field and takes the appropriate action (via the Case statement). If it is not found, the code opens the frmError form, and the ERR_QUIT constant value is returned from the ErrorHandler function. When using this method, you need to add to the table only specific errors that you want to trap.

If the error number is found in tblErrors, the code evaluates the Response field from the recordset. If the Response field contains the constant value ERR_QUIT or ERR_EXIT, the frmError form appears before the code returns the constant value to the offending function or subroutine. If the Response field contains the constant value for ERR_RETRY or ERR_CONTINUE, the code returns the constant value without displaying the frmError form.

NOTE

The tblErrors table is included in CHAP16EX.MDB on the sample code CD-ROM. To take full advantage of this table, you must add all the errors you want to trap, along with the actions you want the error handler to take when a particular error occurs.

Listing 16.19 shows how the code uses the return value from the ErrorHandler function.

Listing 16.19 Using the Return Value from the ErrorHandler Function

In Listing 16.19, the AnySub routine generates an error 11 (divide by 0). Because tblErrors contains the number 3 in the Response column and the ERR_CONTINUE constant is equal to 3, the error form displays, and the AnySub routine exits with an Exit Sub statement.

NOTE

To test what happens when the error code is not found in the tblErrors table, run the SubWithUnknownError routine found in basError. To test what happens when the code returns the ERR_CONTINUE code, execute the SubWithContinue routine.

Emailing the Error

Whereas the LogError routine is responsible for logging the error to a database, the MailError routine, shown in Listing 16.20, is responsible for mailing the error information to a key system administrator.

Listing 16.20 The MailError Routine

The MailError routine receives user and error information from the calling routine. It instantiates Outlook MailItem, NameSpace, and MAPIFolder objects. It then points the NameSpace object at the namespace returned from the GetOutlook function. The GetOutlook function appears in Listing 16.21.

Listing 16.21 The GetOutlook Function

The GetOutlook function declares an Outlook NameSpace object and an Outlook Application object. It uses the GetNamespace method of the Outlook Application object to obtain a reference to a MAPI namespace. It then uses the Logon method of the namespace to log on to the namespace, using a given profile name and password. You will need to change the values of strProfile and strPassword to a valid profile name and password for this code to run properly on your machine. Finally, this code returns a reference to the namespace that it created.

After the MailError routine has a reference to a MAPI namespace, it can use that reference to point to the Outbox. The code uses the Add method of the Items collection of the Outbox to add a mail item to the outbox. The code then sets the appropriate properties (such as To, Subject, and so on) to the appropriate pieces of the error information. Finally, the code can either save the message as a draft, or else send it so that it's immediately sent to the administrator and appears in the Sent Items folder of the sender.

NOTE

Listing 16.21 requires that you know the username and profile of the user sending the email. You could provide a dialog where the user can enter this information each time an error occurs, or this could be part of the initial logon to the system. I generally recommend that this be integrated as part of the initial logon, so that the user does not have to take extra steps each time an error occurs.

Creating an Error Form

The code in the error form's Load event calls two subroutines: GetSystemInfo and GetErrorInfo, as shown here:

Private Sub Form_Load()
'Call routine to obtain system information
Call GetSysInfo(Me)
'Call routine to obtain error information
Call GetErrorInfo(Me)
'If FormCaption property contains a value, use the
'value as the caption for the form
If Not IsNull(Me.OpenArgs) Then
Me.lblAction.Caption = Me.OpenArgs
End If
End Sub

The first subroutine is called GetSysInfo. It performs several Windows Application Programming Interface (API) calls to fill in the system information on your form. The code is shown in Chapter 23, "Exploiting the Power of the Windows API."

Listing 16.22 Getting System Information Through Code

These API calls require the Declare statements and constants shown in Listing 16.23. You will find them in a module called basAPI.

Listing 16.23 Declaring Windows API Calls

The second subroutine, GetErrorInfo, fills in the labels on the error form with all the information from your structure, as shown in Listing 16.24.

Listing 16.24 Using the GetErrorInfo Subroutine

Finally, the disposition of the error, sent as an OpenArg from the ErrorHandler function, is displayed in a label on the form. Figure 16.6 shows the error form.

Printing the Error Form

Users often are not very accurate in describing an error and the corresponding error message. It's therefore important to give them the capability to print their error message. The code in Listing 16.25 prints your error form. You will find it behind the click event of the Print button on the error form.

Listing 16.25 Printing an Error Form