MS-Access, VBA and error handling

This is more an observation than a real question: MS-Access (and VBA in general) is desperately missing a tool where error handling code can be generated automatically, and where the line number can be displayed when an error occurs. Did you find a solution? What is it? I just realized how many hundreds of hours I spared since I found the right answer to this basic problem a few years ago, and I'd like to see what are your ideas and solutions on this very important issue.

From stackoverflow

• Well there are a couple of tools that will do what you ask MZ Tools and FMS Inc come to mind.

  Basically they involve adding an:

  On Error GoTo ErrorHandler
  

  to the top of each proc and at the end they put an:

  ErrorHandler:
    Call MyErrorhandler Err.Number, Err.Description, Err.LineNumber
  

  label with usually a call to a global error handler where you can display and log custom error messages

  Philippe Grondier : You took the words out of my mouth!

• My solution is the following:

  1. install MZ-Tools, a very interesting add-on for VBA. No they did not pay me, anyway it is free.
  2. program a standard error handler code such as this one (see MZ tools menu/Options/Error handler):

  ---

  On Error GoTo {PROCEDURE_NAME}_Error
  {PROCEDURE_BODY}
  On Error GoTo 0
  Exit {PROCEDURE_TYPE}
  
  {PROCEDURE_NAME}_Error:
  debug.print "#" & Err.Number, Err.description, "l#" & erl, "{PROCEDURE_NAME}", "{MODULE_NAME}"
  

  ---

  This standard error code can be then automatically added to all of your procs and function by clicking on the corresponding button in the MZ-Tools menu. You'll notice that we refer here to an undocumented value/property of VBA (2003 edition), 'erl', which stands for 'error line'. You got it! If you ask MZ-Tools to automatically number your lines of code, 'erl' will then give you the number of the line where the error occured. You will have a complete description of the error in your immediate window, such as:

  #91, Object variable or With block variable not set, l# 30, addNewField, Utilities
  

  Of course, once you realize the interest of the system, you can think of a more sophisticated error handler, that will not only display the data in the debug window but will also:

  1. display it as a message on the screen
  2. Automatically insert a line in an error log file with the description of the error or
  3. if you are working with Access or if you are connected to a database, automatically add a record to a Tbl_Error table!

  meaning that each error generated at the user level can be stored either in a file or a table, somewhere on the machine or the network. Are we talking about building an automated error reporting system working with VBA?

  David-W-Fenton : Good post, but I am critical of the practice of having your error handler and exit routine not have a uniform name, e.g., errHandler and exitRoutine. Because of label scope there is no reason to make them specific to the particular sub. Makes cutting and pasting a helluva lot easier.

  Philippe Grondier : You are right: no need to have a specific name for the error routine. But it doesn't really matter as you will not make copy/pastes from 1 proc to the other but rather use the "insert error code" button, that generates the needed lines according to predefined format.

• What about using "Erl", it will display the last label before the error (e.g., 10, 20, or 30)?

  Private Sub mySUB()
  On Error GoTo Err_mySUB
  10:
      Dim stDocName As String
      Dim stLinkCriteria As String
  20:
      stDocName = "MyDoc"
  30:
      DoCmd.openform stDocName, acFormDS, , stLinkCriteria    
  Exit_mySUB:
      Exit Sub
  Err_mySUB:
      MsgBox Err.Number & ": " & Err.Description & " (" & Erl & ")"
      Resume Exit_mySUB
  End Sub