UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Error Handling (Global)    
Error Handling (Global)

This article describes How-To content for a given task

Related Content:
    Error Handling <-- see for more links on handling errors

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.



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.

REALLY IMPORTANT: each line of code NEEDS to be numbered! Without the numbering, error trapping will only tell you in which procedure the error occurred. By numbering each line, the line number will also be trapped and passed to the error handler. Take note of the eaxamples below and you should get the idea.

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/ACE, collected at the procedure level and passed to the modErrorHandler module with the en and ed variables, respectively. The Erl is collected by the system ONLY when line numbers are provided. This allows the values to be passed even the event of another error, which would alter the Erl, 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)

 Setup form Image 1

The Code

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

Option Compare Database
Option Explicit

Global ScrnActvFrmNm As String  ' Current Active Form Name
Global ScrnActvCtlNm As String  ' Current Active Control Name
Global errFunction As String
Global errMsgStr As String
Global errStatement As String
Global errNumber As Integer
Global ctrlfnctnm As String     ' Name of Offending Control in Form
Global ErrorFlag As Boolean     ' Error Flag
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 doTbl As Boolean         ' If true, records error to table
Global hasOL As Boolean         ' If true, provides for Outlook object in send mail routine
Global emDev As String          ' Set in table: USysInfoTbl.emailDev
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

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 errors and write to a text file
'Inputs         errFunction  name of calling function
'               errMsg   message to give user
'               errStatement  message to give programmer
'               errNumber  the internal vb error number
'outputs        appends to a text file the inputs above
'               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:  11/16/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

       doFile = DFirst("[dFile]", "USysInfoTbl")
       doMsg = DFirst("[dMsg]", "USysInfoTbl")
       doMail = DFirst("[dMail]", "USysInfoTbl")
       doTbl = DFirst("[dTbl]", "USysInfoTbl")
       hasOL = DFirst("[useOL]", "USysInfoTbl")
       emDev = DFirst("[emailDev]", "USysInfoTbl")
       ap = MyComputer.dbName
       op = MyComputer.username
       pc = MyComputer.ComputerName

20      DoCmd.Hourglass False
25      DoCmd.SetWarnings True
       On Error Resume Next
30      intErrorFileHandle = FreeFile

40      dt = CStr(Date)
45      tm = CStr(Time())

' set the body message for writing to a file or email
50      bdy = "On " & dt & " at " & tm & ", an error occurred in the " & ScrnActvFrmNm & _
             " on the " & ScrnActvCtlNm & " control at line " & erln & ". Err #" & ErrNum & _
             " - " & ErrDesc & ".  By User :" & op & " on the PC named: " & pc & ".  " & errMsgStr
55      If Len(errMsgStr) > 0 Then
60          bdy = bdy & errMsgStr & ", on " & ap & "."
62          bdy = bdy & ", on " & ap & "."
       End If

' Display Error Message on screen
70      If doMsg Then
75          MsgBox "Please contact your Application Administrator" & vbCrLf & _
                  "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

' Write Error Message to file
80      If doFile Then
85          Open myErrLogFile For Append Access Write As #intErrorFileHandle
90          Print #intErrorFileHandle, bdy
95          Close #intErrorFileHandle
       End If

' Send Error Message as eMail if Outlook is used
' email address must be properly formed: "h@f.tv"
100     If doMail And hasOL And Len(emDev) > 6 And InStr(1, emDev, "@") >= 2 Then
120         subj = "Error in " & MyComputer.dbName
130         Call myEmail(emDev, subj, bdy)
       End If

' Write Error data to USysErrLog table
200     If doTbl Then
210         strSQL = "INSERT INTO USysErrLog ( errno, errDescrip, errObj, errCtrl, " & _
                    "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 & "';"
220         CurrentDb.Execute strSQL, dbFailOnError
           'Debug.Print strSQL
       End If
End Sub

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

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)

150         olitem.cc = ""
160         olitem.To = tgt
170         olitem.subject = sbj
180         olitem.Body = bdy & Chr(13) & Chr(10)
190         olitem.Display
200         olitem.Send

210         Set olitem = Nothing
220         Set olfolder = Nothing
230         Set olns = Nothing
240         Set olapp = Nothing
       End If

   DoCmd.SetWarnings True
   DoCmd.Hourglass False
   Exit Function

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

Form Level Error Handling Procedure (Save the code below into each form that requires error handling)

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

Procedure Level Error Handler (Save the code below into each procedure of a form that requires error handling)

Private Sub cmdClose_Click()
On Error GoTo Err_Ctrl

30   DoCmd.Close acForm, Me.FormName

   DoCmd.SetWarnings True
   DoCmd.Hourglass False
   Exit Sub

   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

The Demo

The Test Form (see Image 2)

 Test form Image 2

The image above is the testing tool for error handling mechanism(s). The People combobox is only a functional control to provide visual stimulation to the demo. The real tests are conducted by the command buttons entitled "Open Test" and "Open Test 2". (cmdTest and cmdTest2 respectively)

The "Open Test" button attempts to open a form that does not exist. This throws an error and subsequently engages the procedural error routine which calls the form level error handler and passes to it certain collected bits of information about the procedure. (see Code 1) The SetWarnings and Hourglass calls just ensure that those two settings are set back to their respective default values.


Private Sub cmdTest_Click()
On Error GoTo Err_Ctrl

    DoCmd.OpenForm "NoFormExists" 

    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

The "Open Test 2" button does nothing more than to force an intentional error as you can see in Code 2.


Private Sub cmdTest2_Click()
On Error GoTo Err_Ctrl

' Force an error (Real Code would normally go here)
177    Err.Raise 2501, "Self", "Canceled"
    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

Also included in the demo/tutorial file is a module named "MyComputer". That module is important in that it provides code to collect information about the computer and current user at error time to provide more information to the developer/administrator to aid in troubleshooting. (see Code 3)

To return any the values retrieved with MyComputer, use this syntax:


to obtain the name of the current PC.


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

The File:


The link below contains:

 • ErrorHandler5_97.mdb (221k)
 • ErrorHandler5_2K.mdb (324k)
 • ErrorHandler5_2K3.mdb (380k)
 • ErrorHandler5_2K7.accdb (483k)
 • Handling Errors in VBA - ver5.doc (59k)
Error Handling Tutorial  <--- click to retrieve file

May you find this useful. Enjoy!

Edit Discussion
Custom Search

Thank you for your support!
This page has been accessed 28,253 times.  This page was last modified 15:54, 11 August 2013 by Mark Davis. Contributions by Jack Leach, Glenn Lloyd and Walter Niesz  Disclaimers