Previous Page TOC Index Next Page Home


17

Handling Those Dreaded Runtime Errors

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.

Working with Error Events

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.

Using On Error Statements

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.

On Error Goto

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.

Including the Error Number and Description in the Error handler

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

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

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.

Resume, Resume Next, and Resume <LineLabel> Statements

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.

The Resume Statement

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.

The Resume Next Statement

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> Statement

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.

Clearing an Error

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.

The Cascading Error Effect

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

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.

Raising an Error

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.

Generating an Error on Purpose

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.

Creating User-Defined Errors

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

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.

Creating a Generic Error Handler

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.

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 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.

Determining the Appropriate Response to an Error

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.

Creating an Error Form

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.

Printing the Error Form

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

Preventing Your Own Error Handling from Being Invoked

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.

Practical Examples

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.

Summary

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.

Previous Page TOC Index Next Page Home