Error Handling (Global)
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.
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)
Master Error Handler (Save the code below into a module)
Option Compare Database
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"
'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 & "."
' 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
' 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
' Send Error Message as eMail if Outlook is used
' email address must be properly formed: "email@example.com"
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)
' 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
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)
210 Set olitem = Nothing
220 Set olfolder = Nothing
230 Set olns = Nothing
240 Set olapp = Nothing
el = erl
en = Err.Number
ed = Err.Description
If en = 287 Then
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."
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)
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
'YOUR CODE HERE
30 DoCmd.Close acForm, Me.FormName
el = erl
en = Err.Number
ed = Err.Description
errMsgStr = "Close Button Error"
ctrlfnctnm = "cmdClose_Click"
Call form_err(en, ed, ctrlfnctnm, el, errMsgStr)
The Test Form (see 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 '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
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" 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
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 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!
|This page has been accessed 5,857 times. This page was last modified 18:35, 28 February 2013 by Mark Davis. Contributions by Jack Leach, Glenn Lloyd and Walter Niesz Disclaimers|