Errors happen, even in the absence of programmer error. You need to protect your programs and your data from the adverse effects of errors by practicing error handling.

Error handling is also known as error trapping. Error handling is the process of intercepting Jet's 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 exit abruptly from your application code. Consider the example in Listing 17.1.

Private Sub cmdCallError_Click()
    Call TestError(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 0, a runtime error occurs. Because no error handling is in effect, the program terminates.

Figure 17.1 shows the error message the user receives. 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. Choosing Continue (this is not always available), tells Access to ignore the error and continue with the execution of the program. Clicking End terminates execution of the programming code. If the application is running with the runtime version of Access, it 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.

You can add error-handling code to the error event procedure of a form or report. You also can add it to any VBA subroutine, function, or event routine. You easily can modify the code in Listing 17.1 to handle the error gracefully. The code in Listing 17.2 shows 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 located in the basError module, which is in the CHAP17EX.MDB database on the accompanying CD-ROM.

The routine now invokes error handling. If a divide-by-zero error occurs, a message box alerts the user to the problem, as Figure 17.2 shows.

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 made by the Access developer.

Errors often occur in the interface of a form or report, as well as in the Jet Database Engine. A user might try to enter an order for a customer who doesn't exist, for example. Instead of displaying Access's default error message, you might want to intercept and handle the error in a particular way.

After an error occurs in a form, its error event is triggered. In Listing 17.3, 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, which is located in the frmOrders form in the CHAP17EX.MDB database, tests to see whether a referential integrity error has occurred. If it has, a message box asks 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

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, which tells Access to display the default error message.

On Error statements activate 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.

Suppose that Func1 calls Func2, and Func2 calls Func3. Only Func1 contains error handling. An error occurs in Func3. Func3 passes control up to Func2. Func2 has no error handling, so it passes control up to Func1. Func1 handles the error. Needless to say, the error handler found in Func1 is not necessarily appropriate to handle the error that occurred in Func3.

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

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

Including the Error Number and Description in the Error Handler

The error-handling code in Listing 17.4 did not give a very descriptive message to users. The Description and Number properties of the Err object give users more meaningful error messages. The Err object is covered in detail later in this chapter in the section “Using the Err Object.” For now, take a look at the Description and Number properties to see how you can use them to 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. Figure 17.3 shows the resulting error message. The SimpleErrorHandler routine and all the following examples are located in the basError module of the CHAP17EX.MDB database.

Figure 17.3. An error message with an error number and error string.

Sub TestResumeNext()
    On Error Resume Next
    Kill "AnyFile"
    MsgBox "We Didn't Die, But the Error Was: " & Err.Description
End Sub

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

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

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

Sub TestResumeNextInError()
    On Error GoTo TestResumeNextInError_Err
    Kill "AnyFile"
    MsgBox "We Didn't Die!"
    Exit Sub

TestResumeNextInError_Err:
    Resume Next
End Sub

Sub TestResumeLineLabel(iVar1 As Integer, iVar2 As Integer)
    On Error GoTo TestResumeLineLabel_Err

    Dim sngResult As String
    sngResult = iVar1 / iVar2

TestResumeLineLabel_Exit:
    Exit Sub

TestResumeLineLabel_Err:
    MsgBox "Error #" & Err.Number & ": " & Err.Description
    Resume TestResumeLineLabel_Exit

End Sub

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 cleared somehow, all the information remains set in the Err object. After the error is cleared, no information is found in the Err object.

As mentioned earlier in this chapter, 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. Listing 17.10 shows an example of this process.

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, so Func2 relinquishes control to Func1. VBA executes the error code in Func1. The real problem occurs because of the Resume Next statement. The application continues executing within Func1 on the Debug.Print "I am back in Function 1" statement. This type of error handling is dangerous and confusing. It therefore is 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. Table 17.1 lists the properties of the Err object.

The Err object has only two methods: Clear and Raise. The Clear method enables you to clear an error condition explicitly. It is used primarily when you write code that uses the On Error Resume Next statement. This 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 statement. The Clear method is implicitly issued when these constructs are used. The Raise method of the Err object is covered in the next section.

You use the Raise method of the error object in these situations:

Using the Raise method to generate an error on purpose and create a user-defined error is covered in the following sections.

Sub TestRaiseError()
    On Error GoTo TestRaiseError_Err

    Dim sngResult As String
    Err.Raise 11

    Exit Sub

TestRaiseError_Err:
    MsgBox "Error #" & Err.Number & ": " & Err.Description
    Exit Sub

End Sub

Sub TestCustomError()
   On Error GoTo TestCustomError_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

TestCustomError_Err:
      MsgBox "Error # " & Err.Number & _
            " - " & Err.Description
      Exit Sub
End Sub

The Errors collection 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, when one operation can result in multiple errors. If you are concerned with each error that was generated by one operation, you need to look at the Errors collection. The Errors collection has the same properties as the Err object. If you want to view the errors stored in the Errors collection, you must loop through it, viewing the properties of each Err object. Listing 17.13 shows the code you can use to accomplish this.

Sub TestErrorsCollection()
   On Error GoTo TestErrorsCollection_Err
   Dim db As Database
   Set db = CurrentDb
   db.Execute ("qryNonExistent")
   Exit Sub

TestErrorsCollection_Err:
   Dim ErrorDescrip As Error
   For Each ErrorDescrip In Errors
      Debug.Print ErrorDescrip.Description
   Next ErrorDescrip
   Exit Sub
End Sub

This routine loops through each Err object in the Errors collection, printing the description of each error contained in the collection.

A generic error handler is an error handler that can be called from anywhere in 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 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. A generic error handler should give users information about the error, enable users to print 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 performs error handling should look like the error-handling routine contained in the subroutine in Listing 17.14.

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 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 mstrModuleName. 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 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, as shown in Listing 17.15.

Type typErrors
    intErrorNum As Integer
    strMessage As String
    strModule As String
    strRoutine As String
    strUserName As String
    datDateTime As Variant
End Type

Public ptypError 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

This code is placed in the General section of basHandleErrors. The type structure declared holds all the pertinent information about the error. The Public variable ptypError 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 17.16 shows the ErrorHandler function.

Function ErrorHandler(intErrorNum As Integer, _
strErrorDescription As String, _
                  strModuleName As String, _
                  strRoutineName As String) As Integer
    ptypError.intErrorNum = intErrorNum
    ptypError.strMessage = strErrorDescription
    ptypError.strModule = strModuleName
    ptypError.strRoutine = strRoutineName
    ptypError.strUserName = CurrentUser()
    ptypError.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 ptypError 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 appears before control is returned to the calling routine. In any case, a severity code for the error is returned 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. Errors can be logged to 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. Listing 17.17 shows the LogError routine.

Example 17.17. Using the LogError routine.

Sub LogError()
    Dim sSQL As String
    DoCmd.SetWarnings False
    sSQL = "INSERT INTO tblErrorLog (ErrorDate, ErrorTime, UserName, _
    ErrorNum, ErrorString, Module, Routine) "
    sSQL = sSQL & "VALUES ( #" & ptypError.datDateTime & "#, #" _
                              & ptypError.datDateTime & "#, '" _
                              & ptypError.strUserName & "', " _
                              & ptypError.intErrorNum & ", '" _
                              & ptypError.strMessage & "', '" _
                              & ptypError.strModule & "', '" _
                              & ptypError.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 ptypError. The information is logged to a table called tblErrorLog. Figure 17.4 shows the structure of this table.

Figure 17.4. The tblErrorLog table.

The alternative is to write the information to a textual error log file, as shown in Listing 17.18.

Example 17.18. Writing 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 is 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. Figure 17.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 uses the code in Listing 17.19 to locate the error code in the tblErrors table.

Figure 17.5. The structure of tblErrors.

Example 17.19. Using the ErrorHandler function.

Dim db As Database
    Dim snp As Recordset
    Set db = CurrentDb()
    Set snp = db.OpenRecordset("Select Response from tblErrors Where _
    ErrorNum = " & intErrorNum, dbOpenSnapshot)
    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

This part of the ErrorHandler function creates both a Database and a Recordset 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 Listing 17.19 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. By using this method, you need to add to the table only specific errors that you want to trap.

If no records are found in 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 in 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 appears 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.

Note

The tblErrors table is included in CHAP17EX.MDB on the sample code CD-ROM. To take full advantage of this table, you must add all errors that you want to trap for along with the action(s) that you want the error handler to take when that error occurs.

The return value from the ErrorHandler function is used as shown in Listing 17.20.

Example 17.20. Using the return value from the ErrorHandler function.

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 0). Because tblErrors contains the number 0 in the Response column and the ERR_CONTINUE constant is equal to 3, the error form is displayed and the AnySub routine is exited 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 ERR_CONTINUE code is returned, execute the SubWithContinue routine.

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 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 shown in Listing 17.21, and it is discussed in Chapter 31, “Using External Functions: The Windows API.”

Example 17.21. Getting system information through code.

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 Declare statements and constants shown in Listing 17.22. They are placed in a module called basAPI.

Example 17.22. Declaring Windows API calls.

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, GetErrorInfo, fills in the labels on the error form with all the information from your structure, as shown in Listing 17.23.

Example 17.23. Using the GetErrorInfo subroutine.

Sub GetErrorInfo(frmAny As Form)
    frmAny!lblErrorNumber.Caption = ptypError.intErrorNum
    frmAny!lblErrorString.Caption = ptypError.strMessage
    frmAny!lblUserName.Caption = ptypError.strUserName
    frmAny!lblDateTime.Caption = Format(ptypError.datDateTime, "c")
    frmAny!lblModuleName.Caption = ptypError.strModule
    frmAny!lblRoutineName.Caption = ptypError.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. Figure 17.6 shows the error form.

Figure 17.6. The frmErrors form created by the Form Load routine.

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 box. Choose Tools | Options and click the Advanced tab. Enable the option Break on All Errors located in the Error Trapping section. 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, like any routine, has the potential for error. The original routine has been modified to incorporate the generic error handler, as shown in Listing 17.25.

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 done on an application, errors will occur. It is important that you trap properly 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.