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.
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.
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 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"
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)
1 doFile = DFirst("[dFile]", "USysInfoTbl")
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
' 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 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
' Send Error Message as eMail if Outlook is used and ' check if email address is properly formed: "email@example.com" 100 If doMail And hasOL And Len(emDev) > 6 And InStr(1, emDev,”@”)> = 1 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
'Debug.Print strSQL End If
Emailing Function (Save the code below into a module)
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
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 '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
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
Error Handling Tutorial <--- click to retrieve file
May you find this useful. Enjoy!