Archive for October 7th, 2010

October 7th, 2010

MS Access – VBA – Error Handling

by Daniel Pineault

Before delving into actual programming functions and sub-routine, we must first establish an error handler. An error handler is a bit of code which will do pre-defined actions whenever an error occurs. For instance, generate a message to the user or developper describing the nature of the error. For an error handler to be useful, it must provide a minimum of information in its message to the user. Below is a typical example of an error handler I use. Modify it in any way to suit your exact needs.

On Error GoTo Error_Handler
    'Your code will go here
    
Error_Handler_Exit:
    On Error Resume Next
    Exit {PROCEDURE_TYPE}
 
Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: {PROCEDURE_NAME}/{MODULE_NAME}" & vbCrLf & _
           "Error Description: " & Err.Description, vbCritical, _
           "An Error has Occured!"
    Resume Error_Handler_Exit

A Concrete Example

Sub HelloWorld()
On Error GoTo Error_Handler
 
    MsgBox "Hello Word!"
 
Error_Handler_Exit:
    On Error Resume Next
    Exit Sub
 
Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: YourModuleName/HelloWorld" & vbCrLf & _
           "Error Description: " & Err.Description, vbCritical, _
           "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

Note: Although VBA provides programmers with the possibility of using the err.source statement, it sadly does not help truly identify the culprit of the current error. This is why you must manually enter in the {MODULE_NAME} / {PROCEDURE_NAME} for each error handler. Trust me, although it may take a few extra seconds to do, it will same you loads of troubleshooting time later on (I’m talking from experience)!!!

Also, if you are going to be doing some serious vba (MS Access, Word, Excel, …) work and not just a little tinkering, you should most probably seriously consider looking into the Mz-Tools add-in (free with no strings attached) in conjunction with implementing Allen Browne Error Log (for database developers). These two tools/approaches will greatly simplify and standardize your work!!!

Share and Enjoy

  • Google Plus
  • Facebook
  • LinkedIn
  • Twitter
  • Email
  • Print