Revision as of 16:08, 3 March 2011

Handling Access Errors with VBA

At some point in the journey to learn and master Access, the generation our VBA code will supercede the use of certain wizards. One of those will eventually be the VBA Code Wizard. Although it is a quick method of generating a procedural ‘shell’, it’s auto-generated error handling can be more robust when we more effectively manage any errors that may occur.

Writing error handling routines for each and every procedure can be tedious. The code example(s) and demo app of this tutorial can provide a way to trap and manage unexpected errors. But then, what other kind of error is there? (Random, undocumented features, to be sure!)

A ‘global’ approach to error handling is the thrust of this discussion, where we will employ ‘global’ variables to assist in the management of the information collected during an error trapping process. We will also explore the primary Error Handling routine(s)/module in detail and how the ‘global’ variables play their part in passing a procedure’s error info to the primary routine.

Then, once the primary routine has been given a procedural call and error info, we will be able to present a user with an on-screen message, a text-based error file and/or the ability to email the error to a developer and/or application administrator.

Also demonstrated will be how to bring these Global Error Handling routines into your working Access application.

Discussion:

Always backup any database before making changes of any kind.

The primary module, modErrorHandler, contains both the global variable declarations as well as the primary error handling engine. Normally, I keep all global variable declarations in a module named modGlbVars, but for this tutorial, we’ll house them in the modErrorHandler module.

This discussion will bounce across three levels of application: module (global), forms/reports (public) and class procedures. (Class procedures being those bits of code ‘behind’ a form and/or report, such as OnClick, etc.)

When an error occurs in VBA code behind a form or report, for the developer, it is a good thing to know what error occurred and where. The first thing we want to know is what error occurred. When an error is raised, it normally has an error number (Err.Number) and description (Err.Description). These two bits of information provide some clues as to what went wrong, Err.Description being most informative. Sometimes, an error number has no associated description and you will get: Application-defined or object-defined error.

Each procedure, that the developer wants error handling in, will have an error trapping routine that passes information to the form’s public error handler. See the Procedural Call section of the code.

Each form/report, that the developer wants error handling in, will have a ‘public’ procedure that manages errors for the entire form or report, passing the collected information to the modErrorHandler. The modErrorHandler is where the error information is finally managed to generate a screen message, error text file and/or email message. See the Form/Report level Public Procedure section of the code.

The Variables:

There is also a way to obtain the specific line of code where the error took place within the procedure. By simply adding a number in the first column of the line of code; it must be followed by at least one space. The line number will be picked up by the Access constant Erl. It is suggested that when writing your code, begin your numbering sequence by 10’s or 5’s. That way, you’ll have room to squeeze in more code later. The Erl is transferred to the el Global variable to prevent confusion of the first Erl trapped, in the event of multiple errors. It is wise to start with the first error encountered when mutiples occur.

The Err.Number and Err.Description are generated by Access/Jet, collected at the procedure level and passed to the modErrorHandler module with the en and ed variables, respectively. This allows the values to be passed even the event of another error, which would alter the Err.Number and Err.Description values.

ScrnActvFrmNm collects the name of the form in which the error occurred with the Screen.ActiveForm.Name method.

ctrlfnctnm collects the name of the procedure in which the error occurred.

errMsgStr collects a custom (developer defined) error message

errFileName defines the path and name of the error text file. (If used)

doFile, doMsg, doMail, doTbl and hasOL are the Boolean values, stored in the USysInfoTbl table. These are used to turn the display message, error file writing, error table writing and/or error email on or off. doFile and doMail are turned off as default in the demo. You can turn them on or off in the “frmSetUp” form. doMsg and doFile are turned on. hasOL is the Boolean for using Outlook or not. So, if you do not use Outlook, be sure this is toggled off. In “frmSetUp”, the toggle is identified as: “Use Outlook”. Also, if you are going to send emails, be sure to provide an email address in the “Email Error Messages to:” field on the form. In the USysInfo table, it is the emailDev field. (see Image 1)

Image 1

The Code:

Master Error Handler (Save the code below into a module)

Option Compare Database
Option Explicit

Global errFileName As String    ' Path and name of Error Message Text File
Global doFile As Boolean        ' If true, writes error to file
Global doMsg As Boolean         ' If true, displays message to user
Global doMail As Boolean        ' If true, sends error as email
Global hasOL As Boolean         ' If true, provides for Outlook object in send mail routine
Global emDev As String          ' Set in table: USysInfoTbl.emailDev
Global errMsgStr As String      ' Custom Error Message
Global ctrlfnctnm As String     ' Name of Offending Control in Form
Global el As Long               ' collects the specific line number of the procedure
Global en As Long               ' collects the specific error number of the procedure
Global ed As String             ' collects the specific error description of the procedure
Global ErrorFlag As Boolean     ' True if error raised

Property Get myErrLogFile() As String

   ' this property is tied to the values found in the USysInfoTbl table
   ' File will be housed in the local path of the db and named the
   ' same name as the db with "_Errors.txt" as the suffix and extension;
   ' so, if the db is named "mydb.mdb", then the error file will be "mydb_Errors.txt"
   ' and it will be found in the same directoy as the database
   myErrLogFile = DFirst("[Path]", "USysInfoTbl") & Left(DFirst("[Title]", "USysInfoTbl"), InStr(1, DFirst("[Title]", "USysInfoTbl"), ".")) & "txt"

End Property

'Module: mdlErrorHandler
'Function: gsubErrorHandler
'Purpose: Trap detailed information about errors and track for troubleshooting
' and development
'Inputs • errFunction…………………name of calling function
' • errMsg………………………………message to give user
' • errStatement………………message to give programmer
' • errNumber………………………the internal vb error number
' • several boolean fields in the UsysInfo table
'outputs multi-optional output methods
' along with the date and time
' Prompts user to let them know to contact tech support
'
'Written by:…………………Mark Davis
'Date written:……………10/1/00 MGD
'Revision History: (Person making changes and date and time
'Revision comments add comments prior to change in sections where changes
'changes are made, who changed and when date and time
'
'Modified by:………………Mark Davis
'Date Modified:…………11/16/2000
'Get Error File to actually open and have error data appended to an error log file
'
'Modified by:………………Mark Davis
'Date Modified:…………2/18/2011
'Get Errors to log to a table and added function toggles

Public Sub gsubErrorHandler(ErrNum As Long, ErrDesc As String, ErrSrc As String, erln As Long, ScrnActvFrmNm As String, ScrnActvCtlNm As String, Optional errMsgStr As String)
On Error Resume Next
Dim op As String, pc As String, ap As String, subj As String, bdy As String
Dim strSQL As String, tm As Date, dt As Date
Dim intErrorFileHandle As Integer

1 doFile = DFirst("[dFile]", "USysInfoTbl")
2 doMsg = DFirst("[dMsg]", "USysInfoTbl")
3 doMail = DFirst("[dMail]", "USysInfoTbl")
4 doTbl = DFirst("[dTbl]", "USysInfoTbl")
5 hasOL = DFirst("[useOL]", "USysInfoTbl")
6 emDev = DFirst("[emailDev]", "USysInfoTbl")
7 ap = MyComputer.dbName
8 op = MyComputer.username
9 pc = MyComputer.ComputerName

20 DoCmd.Hourglass False 25 DoCmd.SetWarnings True

       On Error Resume Next

   ' set the body message for writing to a file or email

             " on the " & ScrnActvCtlNm & " control at line " & erln & ". Err #" & ErrNum & _
             " - " & ErrDesc & ".  By User :" & op & " on the PC named: " & pc & ".  " & errMsgStr

         Else

       End If

                  "and convey the following information:" & vbCrLf & vbCrLf & _
                  "Form : " & ScrnActvFrmNm & " - " & "Control : " & ScrnActvCtlNm & vbCrLf & _
                  errMsgStr & vbCrLf & vbCrLf & "Error # " & ErrNum & vbCrLf & _
                  ErrDesc, vbOKOnly + vbExclamation, "Application Error"
           ErrorFlag = True
       End If

       End If

       End If

                    "errProc, errProcLine, errDt, errCustMsg, errUsr, errPC, errAp ) " & _
                    "SELECT " & en & ", " & Chr(34) & ed & Chr(34) & _
                    ", '" & ScrnActvFrmNm & "', '" & ScrnActvCtlNm & _
                    "', '" & ctrlfnctnm & "', " & el & ", #" & Now() & _
                    "#, " & Chr(34) & errMsgStr & Chr(34) & ", '" & op & _
                    "', '" & pc & "', '" & ap & "';"

           'Debug.Print strSQL
       End If

End Function

Public Function myEmail(tgt As String, sbj As String, bdy As String)
On Error GoTo Err
Dim olapp As Object
Dim olns As Object
Dim olfolder As Object
Dim olitem As Object
Dim el As Long, en As Long, ed As String

10      If Not hasOL Then ' system does not use OutLook
           DoCmd.SendObject , , , tgt, , , sbj, bdy, 0
         Else
100         If bdy <> " " And Nz(bdy, "") <> "" Then
110             Set olapp = CreateObject("Outlook.Application")
120             Set olns = olapp.GetNamespace("MAPI")
130             Set olfolder = olns.GetDefaultFolder(6)
140             Set olitem = olapp.CreateItem(0)

200             olitem.cc = ""
210             olitem.To = tgt
220             olitem.subject = sbj
230             olitem.Body = bdy & Chr(13) & Chr(10)
240             olitem.Display
250             olitem.Send
260             Set olitem = Nothing
270             Set olfolder = Nothing
280             Set olns = Nothing
290             Set olapp = Nothing
           End If
       End If

err_exit:
   DoCmd.SetWarnings True
   DoCmd.Hourglass False
   Exit Function

Err:
   el = erl
   en = Err.Number
   ed = Err.Description
   If en = 287 Then
       Resume 260
     Else
       MsgBox "Error at line " & el & " when trying to Send " & sbj & " Email from" & _
              " the basEmailMods.myEmail procedure." & _
              vbCrLf & vbCrLf & "Error #" & en & " - " & ed, _
              vbInformation + vbOKOnly, "Error in the Sending Email Routine."
       Resume err_exit
   End If
 
End Function

Public Sub form_err(ErrNum As Long, ErrDesc As String, ctrlfnctnm As String, erln As Long, _
                    errMsgStr As String)
' this is the FORM's master error routine it collects the error data from any of the routines from within
' the form calling it and passes the collected data to the mdlErrorHandler module
    Call gsubErrorHandler(ErrNum, ErrDesc, ctrlfnctnm, erln, Screen.ActiveForm.Name, _
                          Screen.ActiveControl.Name, errMsgStr)
End Sub

Private Sub cmdClose_Click()
On Error GoTo Err_Ctrl

'YOUR CODE HERE
30   DoCmd.Close acForm, Me.FormName

Exit_Sub:
    DoCmd.SetWarnings True
    DoCmd.Hourglass False
    Exit Sub

Err_Ctrl:
    el = erl
    en = Err.Number
    ed = Err.Description
    errMsgStr = "Close Button Error"
    ctrlfnctnm = "cmdClose_Click"
    Call form_err(en, ed, ctrlfnctnm, el, errMsgStr)
    Resume Exit_Sub
    
End Sub

Private Sub cmdTest_Click()
On Error GoTo Err_Ctrl

'YOUR CODE HERE
    DoCmd.OpenForm "NoFormExists" 

Exit_Sub:
    DoCmd.SetWarnings True
    DoCmd.Hourglass False
    Exit Sub

Err_Ctrl: ' Call Local Form Level Error Routine
    el = erl
    en = Err.Number
    ed = Err.Description
    errMsgStr = "The form you wanted to close does not exist."
    ctrlfnctnm = "cmdTest_Click"
    Call form_err(en, ed, ctrlfnctnm, el, errMsgStr)
    Resume Exit_Sub
    
End Sub

Private Sub cmdTest2_Click()
On Error GoTo Err_Ctrl

' Force an error (Real Code would normally go here)
177    Err.Raise 2501, "Self", "Canceled"
    
Done:
    Exit Sub
   
Err_Ctrl: ' Call Local Form Level Error Routine
    el = erl
    en = Err.Number
    ed = Err.Description
    errMsgStr = "This Error was forced by the developer as a test."
    ctrlfnctnm = "cmdTest2_Click"
    Call form_err(en, ed, ctrlfnctnm, el, errMsgStr)
    Resume Next

End Sub

Option Compare Database
Option Explicit

'Windows API function declarations
Private Declare Function GetComputerName Lib "kernel32" Alias "GetComputerNameA" _
        (ByVal sBuffer As String, lSize As Long) As Long
Private Declare Function GetUserName Lib "advapi32" Alias "GetUserNameA" _
        (ByVal sBuffer As String, lSize As Long) As Long

'Private variable declarations
Public strComputerName As String
Public strUserName As String 

Private Sub Class_Initialize()
Dim lngBuffSize As Long
'Get computer name
    strComputerName = Space$(255)
    lngBuffSize = Len(strComputerName)
    If GetComputerName(strComputerName, lngBuffSize) Then
        strComputerName = Left$(strComputerName, lngBuffSize)
    End If
'Get user name
    strUserName = Space$(255)
    lngBuffSize = Len(strUserName)
    If GetUserName(strUserName, lngBuffSize) Then
        strUserName = Left$(strUserName, lngBuffSize - 1)
    End If
End Sub

Public Property Get ComputerName() As String
    ComputerName = strComputerName
End Property

Public Property Get username() As String
    username = strUserName
End Property

Public Property Get dbFullPath() As String
    dbFullPath = CurrentDb.Name
End Property

Public Property Get dbPath() As String
' returns just the path to the db
    dbPath = Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name)))
End Property

Public Property Get dbName() As String
' returns just the name of the db
    dbName = Dir(CurrentDb.Name)
End Property

Error Handling Tutorial  <--- click to retrieve file