Automatically generating handling of issues
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_mySUB10: Dim stDocName As String Dim stLinkCriteria As String20: stDocName = "MyDoc"30: DoCmd.openform stDocName, acFormDS, , stLinkCriteria Exit_mySUB: Exit SubErr_mySUB: MsgBox Err.Number & ": " & Err.Description & " (" & Erl & ")" Resume Exit_mySUBEnd Sub
My solution is the following:
- install MZ-Tools, a very interesting add-on for VBA. No they did not pay me to write this. Version 3 was free, but since version 8.0, the add-in is commercially sold.
- 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 0Exit {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 a hidden and undocumented function in the VBA standard library, '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:
- display it as a message on the screen
- Automatically insert a line in an error log file with the description of the error or
- 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?
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 procand 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