| home Java Script MS Access Perl HTML Delphi C ++ Visual Basic Java CGIPerl MS Excel Front Page 98 Windows 98 Ms Word Builder PHP Assembler Link to us Links |
Errors happen, even in the absence of programmer error. It is necessary to protect your programs and your data from the adverse effects of errors. You accomplish this through the process of error handling.
Error handling is also known as error trapping. Error handling is the process of intercepting Jet or VBA's response to an error. It enables the developer to determine the severity of an error and to take the appropriate action in response to the error.
Without error handling, the user of your application is forced to abruptly exit from your application code. Consider the following example:
Private Sub cmdCallError_Click()
Call CallError(txtValue1, txtValue2)
End Sub
Sub TestError(Numerator As Integer, Denominator As Integer)
Debug.Print Numerator / Denominator
MsgBox "I am in Test Error"
End Sub
The click event behind the command button calls the routine TestError, passing it the values from two text boxes. TestError accepts those parameters and attempts to divide the first parameter by the second parameter. If the second parameter is equal to zero, a runtime error occurs. Because there is no error handling in effect, the program terminates.
The error message that the user receives is shown in Figure 17.1. As you can see, the choices are Debug, Continue, End, and Help. If users choose Debug, the module window appears, and they are placed in Debug mode on the line of code causing the error. If users select Continue (this is not always available), Access ignores the error and continues with the execution of the program. If users choose End, the execution of the programming code terminates. If the application is running with the runtime version of Access, the application shuts down and users are returned to Windows. With error handling in effect, you can attempt to handle the error in a more appropriate way whenever possible.
Figure 17.1. Default error handling.
Error-handling code can be added to the error event procedure of a form or report. It can also be added to any VBA subroutine, function, or event routine. The previous code can easily be modified to handle the error gracefully. The code that follows gives an example of a simple error-handling routine:
Sub TestError(Numerator As Integer, Denominator As Integer)
On Error GoTo TestError_Err
Debug.Print Numerator / Denominator
MsgBox "I am in Test Error"
Exit Sub
TestError_Err:
If Err = 11 Then
MsgBox "Variable 2 Cannot Be a Zero", , "Custom Error Handler"
End If
Exit Sub
End Sub
This code is found in a module called basError, contained in the database named CHAP17EX.MDB.
The routine now invokes error handling. If a divide-by-zero error occurs, a message box displays, alerting the user to the problem. Upon the occurrence of an error, the screen appears as in Figure 17.2.
Figure 17.2. A custom error handler.
Every form and report contains an error event procedure. This event is triggered by any interface or Jet database engine error. It is not triggered by a programming error that the Access developer has made.
Errors often occur in the interface of a form or report, as well as in the Jet database engine. For example, a user might try to enter an order for a customer who doesn't exist. Rather than displaying Access's default error message, you might want to intercept and handle the error in a particular way.
After an error occurs within a form, its error event is triggered. In the following code, you can see Sub Form_Error. It contains two parameters. The first parameter is the number of the error. The second parameter is the way you want to respond to the error. The error number is an Access-generated number.
This code, found within the frmOrders form in the CHAP17EX.MDB database, tests to see whether a referential integrity error has occurred. If it has, a message box is displayed, asking whether the user wants to add the customer. If the user answers yes, the customer form is displayed:
Private Sub Form_Error(DataErr As Integer, Response As Integer)
Dim intAnswer As Integer
If DataErr = 3201 Then 'Referential Integrity Error
intAnswer = MsgBox("Customer Does Not Exist... _
Would You Like to Add Them Now", vbYesNo)
If intAnswer = vbYes Then
DoCmd.OpenForm "frmCustomer", , , , acAdd, acDialog
End If
End If
Response = acDataErrContinue
End Sub
Be aware that the preceding sample only traps referential integrity errors. It does not handle any other error.
The Response = acDataErrContinue line is very important. It instructs Access to continue the code execution without displaying the standard error message. The other option for Response is AcDataErrDisplay. It instructs Access to display the default error message.
If you want to get a list of all the errors that can occur in Access, as well as a description of what each error number means, search for Error Codes in the help index. A list appears, containing each error number and a description of the error. You can click on an error description to get a more detailed explanation of the error.
An On Error statement is a statement that activates error handling. Each routine must contain its own On Error statement if you want that routine to do its own error handling. Otherwise, error handling is cascaded up the call stack. If no On Error statements are found in the call stack, VBA's own error handling is invoked.
Using an On Error statement, you can cause the application to branch to error-handling code, resume on the line immediately following the error, or attempt to reexecute the problem line of code.
You must decide the most appropriate response to a particular error. Sometimes it is most appropriate for your application to halt in response to an error. At other times, it is best if the routine skips the offending line entirely. By combining the use of On Error Goto, On Error Resume Next, and On Error Resume, you can handle each error appropriately.
The statement On Error Goto <label> tells VBA that from this point forward, if an error occurs, it should jump to the label specified within the statement. This is the most common form of error handling.
The label specified in the On Error statement must be within the current procedure, and it must be unique within a module. The following code illustrates a simple example of error handling:
Sub SimpleErrorHandler(iVar1 As Integer, iVar2 As Integer)
On Error GoTo SimpleErrorHandler_Err
Dim sngResult As String
sngResult = iVar1 / iVar2
Exit Sub
SimpleErrorHandler_Err:
MsgBox "Oops!"
Exit Sub
End Sub
Some important things can be learned from this simple routine. The routine receives two integer values. It then invokes the error handler. When an error occurs, execution continues at the label. Notice that this routine contains two Exit Sub statements. If you remove the first Exit Sub statement, the code falls through to the label regardless of whether an error occurred. The Exit Sub statement at the bottom gracefully exits the procedure, setting the error code back to zero.
The previous error-handling code did not provide a very descriptive message to the user. The Description and Number properties of the Err object assist in providing the user with more meaningful error messages. The Err object is covered in detail later in this chapter in the section "The Err Object." For now, let's look at the Description and Number properties to see how they can enhance an error-handling routine. To display the error number and description, you must modify the error-handling code to look like this:
SimpleErrorHandler_Err:
MsgBox "Error #" & Err.Number & ": " & Err.Description
Exit Sub
This time, instead of hard-coding the error message, you display the error number and VBA's internal error string. The resulting error message looks like Figure 17.3. The SimpleErrorHandler routine, as well as all the examples that follow, can be found in the basError module of the CHAP17EX.MDB database.
Figure 17.3. An error message with an error number and error string.
On Error Resume Next continues program execution on the line immediately following the error. This construct is usually used when it is acceptable to ignore an error and continue code execution. The following is an example of such a situation:
Sub ResumeNext()
On Error Resume Next
Kill "AnyFile"
MsgBox "We Didn't Die, But the Error Was: " & Err.Description
End Sub
The Kill statement is used to delete a file from disk. If the specified file is not found, an error results. You only delete the file if it exists, so you are not concerned about an error. On Error Resume Next is very appropriate in this situation because there is no harm done by resuming execution after the offending line of code.
On Error Goto 0 is used for two purposes:
Generally, you don't want Access to return to its default error handler. You might do this only if you were, for some reason, unable to handle the error, or if you were in the testing phase and not yet ready to implement your own error handler.
The reason that you want Access to return the error to a higher level routine is much more clear. You do this if you want to "centralize" the error handling, meaning that one routine might call several others. Rather than placing error-handling code in each routine that is called, you can place the error handling in the calling routine.
While you are in your error-handling code, you can use the Resume, Resume Next, and Resume <LineLabel> statements to specify how you want VBA to respond to the error. Resume attempts to reexecute the offending line of code, Resume Next resumes execution after the offending line of code, and Resume <LineLabel> continues execution at a specified Line Label. The following sections cover these statements in detail.
As mentioned previously, the Resume statement resumes code execution on the line of code that caused the error. This statement must be used with extreme care, because it can throw the code into an unrecoverable endless loop. Here is an example of an inappropriate use of the Resume statement:
Function BadResume(sFileName As String)
On Error GoTo BadResume_Err
Dim strFile As String
strFile = Dir(sFileName)
If strFile = "" Then
BadResume = False
Else
BadResume = True
End If
Exit Function
BadResume_Err:
MsgBox Error
Resume
End Function
This function is passed a filename. The Dir function searches for the filename and returns True or False depending on whether the specified filename is found. The problem occurs when the drive requested is not available or does not exist. This code throws the computer into an endless loop. To remedy the problem, your code should be modified to look like this:
Function GoodResume(sFileName As String)
On Error GoTo GoodResume_Err
Dim strFile As String
strFile = Dir(sFileName)
If strFile = "" Then
GoodResume = False
Else
GoodResume = True
End If
Exit Function
GoodResume_Err:
Dim intAnswer As Integer
intAnswer = MsgBox(Error & ", Would You Like to Try Again?", vbYesNo)
If intAnswer = vbYes Then
Resume
Else
Exit Function
End If
End Function
In this example, the error handler enables the user to decide whether to try again. Only if the user's response is affirmative does the Resume occur.
Just as you can invoke error handling using an On Error Resume Next, you can place a Resume Next statement in your error handler:
Sub ResumeNextInError()
On Error GoTo ResumeNextInError_Err
Kill "AnyFile"
MsgBox "We Didn't Die!"
Exit Sub
ResumeNextInError_Err:
Resume Next
End Sub
In this example, the code is instructed to go to the label called ResumeNextInError_Err when an error occurs. The ResumeNextInError_Err label issues a Resume Next. This clears the error and causes execution to continue on the line after the line in which the error occurred.
The Resume <LineLabel> command enables you to specify a line of code where you would like code execution to continue after an error occurs. This is a great way to eliminate the two Exit Sub or Exit Function statements required by the error-handling routines that you have looked at so far. Here's an example:
Sub ResumeLineLabel(iVar1 As Integer, iVar2 As Integer)
On Error GoTo SimpleErrorHandler_Err
Dim sngResult As String
sngResult = iVar1 / iVar2
SimpleErrorHandler_Exit:
Exit Sub
SimpleErrorHandler_Err:
MsgBox "Error #" & Err.Number & ": " & Err.Description
Resume SimpleErrorHandler_Exit
End Sub
Notice that this routine contains only one Exit Sub. If no error occurs, Access drops through the SimpleErrorHandler_Exit label to the Exit Sub. If an error does occur, the code within the SimpleErrorHandler_Err label executes. Notice that the last line of the label resumes execution at the SimpleErrorHandler_Exit label.
This method of resolving an error is useful because any code required to execute as the routine is exited can be written in one place. For example, object variables might need to be set equal to Nothing as the routine is exited. These lines of code can be placed in the exit routine.
When an error occurs, the Err object remains set with the error information until one of the following clears the error:
Until the error is somehow cleared, all the information remains set within the Err object. After the error is cleared, no information is found within the Err object.
If Access does not find any error handling in a particular subroutine or function, it looks up the call stack for a previous error handler. This is illustrated with the following code:
Sub Func1()
On Error GoTo Func1_Err
Debug.Print "I am in Function 1"
Call Func2
Debug.Print "I am back in Function 1"
Exit Sub
Func1_Err:
MsgBox "Error in Func1"
Resume Next
End Sub
Sub Func2()
Debug.Print "I am in Func 2"
Call Func3
Debug.Print "I am still in Func2"
End Sub
Sub Func3()
Dim sngAnswer As Single
Debug.Print "I am in Func 3"
sngAnswer = 5 / 0
Debug.Print "I am still in Func3"
End Sub
In this situation, the error occurs in Func3. Because Func3 does not have its own error handling, it refers back to Func2. Func2 does not have any error handling either. Func2 relinquishes control to Func1. VBA executes the error code in Func1. The real problem occurs because of the Resume Next. The application continues executing within Func1 on the statement that reads Debug.Print "I am back in Function 1". This type of error handling is dangerous and confusing. It is therefore best to develop a generic error-handling routine that is accessed throughout your application.
The Err object contains information about the most recent error that occurred. As with all Access objects, it has its own built-in properties and methods. The properties of the Err object are listed in Table 17.1.
Property |
Description |
|
Number |
The number of the error that has been set |
|
Description |
The description of the error that has occurred |
|
HelpContext |
The Context ID for the help file |
|
HelpFile |
The path and filename of the help file |
|
LastDLLError |
The last error that occurred in a 32-bit DLL |
|
Source |
The system in which the error occurred (which is extremely useful when you are using OLE automation to control another application, such as Excel) |
The Err object has only two methods: Clear and Raise. The Clear method enables you to clear an error condition explicitly. The Clear method is used primarily when you write code that uses the On Error Resume Next statement. The On Error Resume Next statement does not clear the error condition. Remember that there is no reason to issue the Clear method explicitly with any type of Resume, Exit Sub, Exit Function, Exit Property, or On Error Goto. The Clear method is implicitly issued when these constructs are used. The Raise method of the Err object is covered in the next section.
The Raise method of the error object is used in the following situations:
Using the Raise method to generate an error on purpose and create a user-defined error is covered in the following sections.
Many times, you want to generate an error when testing so that you can test out your own error handling. Rather than figuring out how to "cause" the error condition, you can use the Raise method of the Err object to accomplish this task. Here's an example:
Sub RaiseError()
On Error GoTo RaiseError_Err
Dim sngResult As String
Err.Raise 11
Exit Sub
RaiseError_Err:
MsgBox "Error #" & Err.Number & ": " & Err.Description
Exit Sub
End Sub
This code invokes an error 11 (divide by zero). By generating the error, you can test the effectiveness of your error-handling routine.
Another important use of the Raise method of the Err object is the generation of a custom error condition. This is when you want to have something that does not generate an Access error generate a user-defined error that you send through the normal error-handling process. Because the Raise method enables you to set all the properties of the Err object, you can create a user-defined error complete with a number, description, source, and so forth. Here's an example:
Sub CustomError()
On Error GoTo CustomError_Err
Dim strName As String
strName = InputBox("Please Enter Your Name")
If Len(strName) < 5 Then
Err.Raise Number:=11111, _
Description:="Length of Name is Too Short"
Else
MsgBox "You Entered " & strName
End If
Exit Sub
CustomError_Err:
MsgBox "Error # " & Err.Number & _
" - " & Err.Description
Exit Sub
End Sub
Although it is very simple, this example illustrates an important use of generating user-defined errors. The code tests to see whether the value entered has less than five characters. If it does, a user-defined error (number 11111) is generated. The routine drops into the normal error-handling routine. Later in the chapter, you explore how to create a generic error handler. By passing user-defined errors through your generic error handler, all errors—user-defined or not—are handled in the same way.
The ErrorsCollection is part of Access's Jet engine. It stores the most recent set of errors that have occurred. This is important when dealing with DAO and ODBC. With either DAO or ODBC, one operation can result in multiple errors. If you are concerned with each error that was generated by the one operation, you need to look at the ErrorsCollection. The ErrorsCollection has the same properties as the Err object. If you want to view the errors stored within the ErrorsCollection, you must loop through it, viewing the properties of each Err object. The code looks like this:
Sub ErrorsCollection()
On Error GoTo ErrorsCollection_Err
Dim db As Database
Set db = CurrentDb
db.Execute ("qryNonExistent")
Exit Sub
ErrorsCollection_Err:
Dim ErrorDescrip As Error
For Each ErrorDescrip In Errors
Debug.Print ErrorDescrip.Description
Next ErrorDescrip
Exit Sub
End Sub
The routine loops through each Err object in the ErrorsCollection, printing the description of each error contained within the collection.
A generic error handler is an error handler that can be called from anywhere within your application. It is capable of responding to any type of error.
A generic error handler prevents you from having to write specific error handling into each of your subroutines and functions. This enables you to invoke error handling throughout your application in the most efficient manner possible.
There are many approaches to creating a generic error handler. A generic error handler should provide the user with information about the error, enable the user to print out this information, and log the information to a file. A generic error handler should be able to be called from every procedure within your application.
The On Error routine (in this case, the label AnySub_Err) of every procedure that does error handling should look like the error-handling routine contained within the following subroutine:
Sub AnySub()
Dim strSubName As String
strSubName = "AnySub"
On Error GoTo AnySub_Err
MsgBox "This is the rest of your code...."
Err.Raise 11
MsgBox "We are Past the Error!!"
Exit Sub
AnySub_Err:
Dim intAction As Integer
intAction = ErrorHandler(intErrorNum:=Err.Number, _
strErrorDescription:=Err.Description, _
strModuleName:=mstrModuleName, _
strRoutineName:=strSubName)
Select Case intAction
Case ERR_CONTINUE
Resume Next
Case ERR_RETRY
Resume
Case ERR_EXIT
Exit Sub
Case ERR_QUIT
Quit
End Select
End Sub
This error-handling routine within AnySub creates an Integer variable that holds the return value from the error system. The intAction variable is used to hold an appropriate response to the error that has occurred. The error routine calls the generic error-handling function named 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 named mstrModuleName. The Private constant is declared in the General section of the module and needs to be created for every module that you make. The name of the subroutine or function is stored in a local variable called strSubName. With this approach, you create a local string 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. 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.
Now that you have seen how to implement error handling in your procedures, take a look at the function that is called when an error occurs:
Type typErrors
intErrorNum As Integer
strMessage As String
strModule As String
strRoutine As String
strUserName As String
datDateTime As Variant
End Type
Public pError As typErrors
Public Const ERR_CONTINUE = 0 'Resume Next
Public Const ERR_RETRY = 1 'Resume
Public Const ERR_QUIT = 2 'End
Public Const ERR_EXIT = 3 'Exit Sub or Func
The preceding code is placed in the general section of basHandleErrors. The type structure that is declared holds all the pertinent information about the error. The public variable pError holds all the information from the type structure. The constants are used to help determine the fate of the application after an error has occurred. Here is the ErrorHandler function:
Function ErrorHandler(intErrorNum As Integer, _
strErrorDescription As String, _
strModuleName As String, _
strRoutineName As String) As Integer
pError.intErrorNum = intErrorNum
pError.strMessage = strErrorDescription
pError.strModule = strModuleName
pError.strRoutine = strRoutineName
pError.strUserName = CurrentUser()
pError.datDateTime = Now
Call LogError
Dim db As Database
Dim snp As Snapshot
Set db = CurrentDb()
Set snp = db.OpenRecordset("Select Response from tblErrors Where _
ErrorNum = " & intErrorNum)
If snp.EOF Then
DoCmd.OpenForm "frmError", WindowMode:=acDialog, _
OpenArgs:=ErrorHandler
ErrorHandler = ERR_QUIT
Else
Select Case snp.Response
Case ERR_QUIT
DoCmd.OpenForm "frmError", WindowMode:=acDialog, _
OpenArgs:="Critical Error: Application will Terminate"
ErrorHandler = ERR_QUIT
Case ERR_RETRY
ErrorHandler = ERR_RETRY
Case ERR_EXIT
DoCmd.OpenForm "frmError", WindowMode:=acDialog, _
OpenArgs:="Severe Error: Processing Did Not Complete"
ErrorHandler = ERR_EXIT
Case ERR_CONTINUE
ErrorHandler = ERR_CONTINUE
End Select
End If
End Function
The ErrorHandler function receives the error number, error description, module name, and subroutine or function name as parameters. It then fills in the pError 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 the error code is not found in the error table, an error form is displayed and a return value is sent to the calling function, indicating that application execution is to be terminated. If the error code is found in the tblErrors table and determined to be critical or severe, an error form displays before control is returned to the calling routine. In any case, a severity code for the error is returned to the calling function. The details involved in each step of the process are discussed in the following section.
The LogError routine is responsible for logging all the error information into an Access table. Because users often decide not to print out the error form or provide you with inaccurate information about what was happening when the error occurred (or, perhaps, 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. Errors can be logged to either a text file or a data table. This section shows you both methods of logging your error. Start with logging your errors to a table. The LogError routine looks like this:
Sub LogError()
Dim sSQL As String
DoCmd.SetWarnings False
sSQL = "INSERT INTO tblErrorLog (ErrorDate, ErrorTime, UserName, _
ErrorNum, ErrorString, Module, Routine) "
sSQL = sSQL & "VALUES ( #" & pError.datDateTime & "#, #" _
& pError.datDateTime & "#, '" _
& pError.strUserName & "', " _
& pError.intErrorNum & ", '" _
& pError.strMessage & "', '" _
& pError.strModule & "', '" _
& pError.strRoutine & "')"
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True
End Sub
This routine uses an SQL statement to add a record to your error table. The record contains all the information from the structure called pError. The information is logged to a table called tblErrorLog. The structure of this table appears in Figure 17.4.
Figure 17.4. The tblErrorLog table.
The alternative is to write the information to a textual error log file:
Sub LogErrorText()
Dim intFile As Integer
intFile = FreeFile
Open CurDir & "\ErrorLog.Txt" For Append Shared As intFile
Write #intFile, "LogErrorDemo", Now, Err, Error, CurrentUser()
Close intFile
End Sub
This code uses the low-level file functions Open and Write to open and write to an ASCII text file. All the pertinent information about the error is written 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.
After the error has been logged, 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. The structure of the tblErrors table appears in Figure 17.5. This table should contain a list of all the error numbers for which you want to trap. It contains two fields: ErrorNum 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 uses the following code to locate the error code within the tblErrors table:
Dim db As Database
Dim snp As Snapshot
Set db = CurrentDb()
Set snp = db.OpenRecordset("Select Response from tblErrors Where _
ErrorNum = " & intErrorNum)
If snp.EOF Then
DoCmd.OpenForm "frmError", WindowMode:=acDialog, _
OpenArgs:="ErrorHandler"
ErrorHandler = ERR_QUIT
Else
Select Case snp!Response
Case ERR_QUIT
DoCmd.OpenForm "frmError", WindowMode:=acDialog, _
OpenArgs:="Critical Error: Application will Terminate"
ErrorHandler = ERR_QUIT
Case ERR_RETRY
ErrorHandler = ERR_RETRY
Case ERR_EXIT
DoCmd.OpenForm "frmError", WindowMode:=acDialog, _
OpenArgs:="Severe Error: Processing Did Not Complete"
ErrorHandler = ERR_EXIT
Case ERR_CONTINUE
ErrorHandler = ERR_CONTINUE
End Select
End If
Figure 17.5. The structure of tblErrors.
This part of the ErrorHandler function creates both a Database and a Snapshot object variable. It opens a Snapshot type of Recordset using a Select statement. The Select statement searches a table called tblErrors. If a match is found, the Response column is used to determine the response to the error. Notice in the code 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 Response field is evaluated and the appropriate action is taken (via the case statement). If it is not found, the frmError form is opened and the ERR_QUIT constant value is returned from the ErrorHandler function. In this way, you need to add to the table only specific errors that you want to trap for.
If no records are found within tblErrors that match the SQL statement, the frmError form is opened, and the return value for the function is set equal to the constant value ERR_QUIT. If the error number is found within tblErrors, the Response field from the Snapshot is evaluated. If the Response field contains the constant value ERR_QUIT or ERR_EXIT, the frmError form is displayed before the constant value is returned to the offending function or subroutine. If the Response field contains the constant value for ERR_RETRY or ERR_CONTINUE, the constant value is returned without displaying the frmError form.
The return value from the ErrorHandler function is used as follows:
Sub AnySub()
Dim strSubName As String
strSubName = "AnySub"
On Error GoTo AnySub_Err
MsgBox "This is the rest of your code...."
Err.Raise 11
MsgBox "We are Past the Error!!"
Exit Sub
AnySub_Err:
Dim intAction As Integer
intAction = ErrorHandler(intErrorNum:=Err.Number, _
strErrorDescription:=Err.Description, _
strModuleName:=mstrModuleName, _
strRoutineName:=strSubName)
Select Case intAction
Case ERR_CONTINUE
Resume Next
Case ERR_RETRY
Resume
Case ERR_EXIT
Exit Sub
Case ERR_QUIT
Quit
End Select
End Sub
In this example, the AnySub routine generates an error 11 (divide by zero). Because tblErrors contains the number zero in the Response column, and the ERR_CONTINUE constant is equal to three, the error form is displayed and the AnySub routine is exited with an Exit Sub.
The code in the error form's load event calls two subroutines:
Private Sub Form_Load()
Call GetSysInfo(Me)
Call GetErrorInfo(Me)
Me!lblAction.Caption = Me.OpenArgs
End Sub
The first subroutine is called GetSystemInfo. It performs several Windows API calls to fill in the system information on your form. The code is listed here, but it is discussed in Chapter 29, "External Functions: The Windows API":
Sub GetSysInfo (frmAny As Form)
'Get Free Memory
Dim MS As MEMORYSTATUS
MS.dwLength = Len(MS)
GlobalMemoryStatus MS
frmAny!lblMemoryTotal.Caption = Format(MS.dwTotalPhys, "Standard")
frmAny!lblMemoryAvail.Caption = Format(MS.dwAvailPhys, "Standard")
'Get Version Information
Dim OSInfo As OSVERSIONINFO
OSInfo.dwOSVersionInfoSize = Len(OSInfo)
If GetVersionEx(OSInfo) Then
frmAny!lblOSVersion.Caption = OSInfo.dwMajorVersion & "." _
& OSInfo.dwMinorVersion
frmAny!lblBuild.Caption = OSInfo.dwBuildNumber And &HFFFF&
If OSInfo.dwPlatformId = 0 Then
frmAny!lblPlatform.Caption = "Windows 95"
Else
frmAny!lblPlatform.Caption = "Windows NT"
End If
End If
'Get System Information
Dim SI As SYSTEM_INFO
GetSystemInfo SI
frmAny!lblProcessor.Caption = SI.dwProcessorType
End Sub
These API calls require the following Declare statements and constants. They are placed in a module called basAPI:
Option Compare Database
Option Explicit
Private Declare Sub GlobalMemoryStatus Lib "Kernel32" (lpBuffer As MEMORYSTATUS)
Private Type MEMORYSTATUS
dwLength As Long
dwMemoryLoad As Long
dwTotalPhys As Long
dwAvailPhys As Long
dwTotalPageFile As Long
dwAvailPageFile As Long
dwTotalVirtual As Long
dwAvailVirtual As Long
End Type
Private Declare Function GetVersionEx Lib "Kernel32" Alias "GetVersionExA" _
(lpOSInfo As OSVERSIONINFO) As Boolean
Type OSVERSIONINFO
dwOSVersionInfoSize As Long
dwMajorVersion As Long
dwMinorVersion As Long
dwBuildNumber As Long
dwPlatformId As Long
strReserved As String * 128
End Type
Private Declare Sub GetSystemInfo Lib "Kernel32" (lpSystemInfo As SYSTEM_INFO)
Private Type SYSTEM_INFO
dwOemID As Long
dwPageSize As Long
lpMinimumApplicationAddress As Long
lpMaximumApplicationAddress As Long
dwActiveProcessorMask As Long
dwNumberOrfProcessors As Long
dwProcessorType As Long
dwAllocationGranularity As Long
dwReserved As Long
End Type
The second subroutine, called GetErrorInfo, fills in the labels on the error form with all the information from your structure:
Sub GetErrorInfo(frmAny As Form)
frmAny!lblErrorNumber.Caption = pError.intErrorNum
frmAny!lblErrorString.Caption = pError.strMessage
frmAny!lblUserName.Caption = pError.strUserName
frmAny!lblDateTime.Caption = Format(pError.datDateTime, "c")
frmAny!lblModuleName.Caption = pError.strModule
frmAny!lblRoutineName.Caption = pError.strRoutine
End Sub
Finally, the disposition of the error, sent as an OpenArg from the ErrorHandler function, is displayed in a label on the form. The error form appears in Figure 17.6.
Figure 17.6. The frmErrors form created by the Form Load routine.
Users are often not very accurate in describing an error and corresponding error message. It is therefore important to give them the ability to print out their error message. The following code prints your error form:
Sub cmdPrint_Click()
On Error GoTo Err_cmdPrint_Click
DoCmd.PrintOut
Exit_cmdPrint_Click:
Exit Sub
Err_cmdPrint_Click:
MsgBox Err.Description
Resume Exit_cmdPrint_Click
End Sub
When you are testing your application, you do not want your own error handling to be triggered. Instead, you want VBA's error handling to be activated. The trick is in the Options dialog. Select Tools|Options and click on the Modules tab. Check the option Break on All Errors. As long as this option is set, your error handling is ignored and Access's default error handling is invoked. Using this setting, you can turn error handling on and off from one central location.
Error-handling code should be added throughout the Time and Billing application. The following example shows you how to incorporate the generic error handler into the Time and Billing application.
The Time and Billing application contains a routine called GetCompanyInfo. This routine reads all the Company Information from the tblCompanyInfo table. The information is read from the type structure, as needed, while the application is running. This routine, as any routine, has the potential for error. The original routine has been modified to incorporate the generic error handler:
Sub GetCompanyInfo()
On Error GoTo GetCompanyInfo_Err
Dim strSubName As String
Dim db As DATABASE
Dim rs As Recordset
strSubName = "GetCompanyInfo"
Set db = CurrentDb
Set rs = db.OpenRecordset("tblCompanyInfo", dbOpenSnapshot)
typCompanyInfo.SetUpID = rs!SetUpID
typCompanyInfo.CompanyName = rs!CompanyName
typCompanyInfo.Address = rs!Address
typCompanyInfo.City = rs!City
typCompanyInfo.StateProvince = rs!StateProvince
typCompanyInfo.PostalCode = rs!PostalCode
typCompanyInfo.Country = rs!Country
typCompanyInfo.PhoneNumber = rs!PhoneNumber
typCompanyInfo.FaxNumber = rs!PhoneNumber
rs.Close
db.Close
Exit Sub
GetCompanyInfo_Err:
Dim intAction As Integer
intAction = ErrorHandler(intErrorNum:=Err.Number, _
strErrorDescription:=Err.Description, _
strModuleName:=mstrModuleName, _
strRoutineName:=strSubName)
Select Case intAction
Case ERR_CONTINUE
Resume Next
Case ERR_RETRY
Resume
Case ERR_EXIT
Exit Sub
Case ERR_QUIT
Quit
End Select
End Sub
Notice the On Error Goto statement at the beginning of the routine and that the local variable strSubName has been declared and set equal to GetCompanyInfo. The error handler GetCompanyInfo_Err calls the ErrorHandler function and then evaluates its return value.
In this chapter, you learned the alternatives for handling errors in your Access applications. Regardless of the amount of testing that is done on an application, errors will occur. It is important that you properly trap for those errors.
This chapter covered how you can use the error event to trap for application and Jet engine errors in forms and reports. You also learned how to use the On Error statement. Finally, you learned how to build a generic error system.