Error Handling in Access

Here is my current preferred method for handling errors in Microsoft Access VBA code.  This method will also work in existing Word/Office VBA code, you will just need to provide an .mdb file with the error log table.

I should point out that this is for errors in code only.  It doesn’t handle errors that happen in macros, queries, or non-code parts of forms and reports.  For example, if you have a calculated field in an Access query, and it causes a divide by zero error, it isn’t handled by this function.

The first thing you want to do is make it easier to add the error handling code to your procedures.  I use and recommend MZ-Tools 3.0 Add-In from MZ-Tools.com.  This free add-in provides a bunch of useful tools in the VBA IDE.  One of the buttons on the tool bar will add error handling code to the currently selected procedure, based on a code template that you can edit.  Download and install this add-in.

My goal was to have all errors logged in a local Access table, and present a consistent looking message to the user when an error occurs.  I also wanted it to be easily maintainable, so that I won’t have to go the entire code base just to make changes to how errors are handled.

First, I created a local table named tblErrorLog:

This table exists in the front end .mdb for the application.  That means that each time that the front-end is changed and deployed, the previous entries in this table will be lost.  That’s actually a good thing; we don’t want old error log messages hanging around for things that were fixed long ago.  If for some reason you wanted to preserve those messages, you would just need to make a copy of this table before you deploy a new front-end version.

Next, I created a VBA routine to do the actual error-handling.  I’ve seen many times where a developer puts the actual code to do the error handling in each error handling block in each procedure in their code.  Now you wouldn’t do that for your regular code, so why would you cut and paste repetitive code in your error handling?  I think it is better to have a routine that your code calls that handles things.  Then you only have one place to change your code when you want to change how you handle errors.

I created a new module, modErrorHandler, and one Sub in it, HandleError.  Here is the code listing:

Public Sub HandleError(Errnumber As Long, ErrDesc As String, ProcName As String, ModuleName As String)

   On Error GoTo HandleError_Error
    Dim strSQL As String
    
    strSQL = "INSERT INTO tblErrorLog ( ErrorNumber, ErrorDescription, ProcedureName, ModuleName ) " & _
    "VALUES (" & Errnumber & ", """ & ErrDesc & """, """ & ProcName & """, """ & ModuleName & """);"

    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True

    MsgBox "Error: " & Errnumber & " (" & ErrDesc & ") in procedure " & ProcName & " of " & _
    ModuleName & vbCrLf & vbCrLf & _
    "Please contact Inventory Support for assistance.", vbCritical & vbOKOnly, "Application Error"
    
HandleError_Exit:
   Exit Sub
 
HandleError_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & _
    ") in procedure HandleError of Module modErrorHandler."

    Resume HandleError_Exit

End Sub

The HandleError routine takes parameters for the error number and description, and the procedure and module where the error was thrown.  The procedure builds an inline SQL statement to insert a row into tblErrorLog and executes it.  It then shows the user a message box informing them of the error, and asking them to request support.  Notice that this routine has error handling itself.  What would happen if your error handling code failed for some reason?  You need some basic fall-back plan.  Here, we just show a message box with the error that happened in this procedure.

Next, I modified the error handling code template in the MZ-Tools add-in.  To do this, you click on the Options button in the far right of the tool bar, and select the Error Handler tab of the Options dialog box.

Modify the template code to resemble the picture above.  Notice that there are place holders for parts of your code e.g {PROCEDURE_BODY}.  The template creates an error handling section that calls the HandleError routine and then proceeds to the exit section.

Once you have the template ready, you can try it out on a procedure.  Here is a sample procedure that will cause an error.

Public Sub causeerror()
Dim i As Double

i = 1 / 0

End Sub

To add error handling, simply place the cursor anywhere in the procedure, and then click on the Add Error Handler button in the MZ-Tools add-in.  Now the code looks like this:

Public Sub causeerror()
Dim i As Double
   On Error GoTo causeerror_Error

i = 1 / 0
causeerror_Exit:
   On Error Resume Next
   Exit Sub
causeerror_Error:
    HandleError Err.Number, Err.Description,"causeerror", "Module: edtest"

    Resume causeerror_Exit

End Sub

When you run this, you should see a message box from HandleError.

And you should see a new record in the table.

Lastly, I created a simple Access report and placed a button that runs it in a convenient place for the users to run.  Then if necessary, they can run the report and send it to me so I can see the errors they are getting.

Notice in the template, there is an exit section that has On Error Resume Next followed by Exit Sub.  If you have any clean up code in your procedure, for example closing recordsets and connections, you should move that code here, between On Error Resume Next and Exit Sub.  That way, the clean up code will get run regardless of whether an error occurred and was processed.

This method of handling errors will work for you most of the time.  There may be specific places in your code where you want to do something different when an error occurs, ignore it or take some other particular action.  In those cases you can add the standard error handling code with the MZ-Tools add-in and then modify it as necessary.