Controlling debugs

A

ashg657

Hey,
As much as I hate to admit it, I am prone to mistakes at times in
programming VBA. What I would like to be able to do is when a debug error
occurs, make a note of the runtime error number, display a user-friendly
message box asking to contact me for assistance. This would prevent the user
from ever seeing the VBA window or backend when an error occurs. Is there any
possible function/procedure to achieve this?

Many thanks.
 
J

John Spencer

Allen Browne has a sample of handling errors at
http://allenbrowne.com/ser-23a.html

This may be more complex than you want. The basic is that you must add an
error handler to each procedure for which you want to handle errors.

Outline of handling an error:
Public Sub DoSomething()

On Error GoTo ERROR_DoSomething

<<< YOUR CODE HERE>>

EXIT_DoSomething:
<<Any code that must be run whenever you exit the procedure>>

Exit Sub '<< Don't forget this or the error handler will always run

ERROR_DoSomething:
MsgBox Err.Number & ": " & Err.Description,,"Error in DoSomething"
'Optional line Resume Next or
'Resume EXIT_DoSomething
End Sub

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
D

Daniel

You might want to look at http://www.mztools.com/ add-in. with it you can
setup a standard format for your error handler and then simply implement it
at the click of a button. It's free!
--
Hope this helps,

Daniel P
 
A

ashg657

Thanks for the replies, they both worked like a dream.
One more thing though...does anyone know where I can obtain a list of all
runtime errors and their meanings? This would be useful so when a user phones
up and states their error number i can quickly look it up in a table and
quickly diagnose and solve the problem.
Many thanks again,

Ash.
 
D

Daniel

Here some code I was given a while back, paste it into a module and the run
it once to create and populate the table

'Author: Doug Steele, Microsoft Access MVP
'The following procedure (from the Help file) creates a table containing many
'of the error codes and strings used or reserved by Microsoft Access and by
'the Microsoft Jet database engine. Not all error codes are included in the
'resulting table, as some exist outside the range of error codes evaluated by
'this procedure (0 to 4500). Note that it's actually creating a table
'(AccessAndJetErrors) and storing the details in that table.

Function AccessAndJetErrorsTable() As Boolean
Dim dbs As Database, tdf As TableDef, fld As Field
Dim rst As DAO.Recordset, lngCode As Long
Dim strAccessErr As String
Const conAppObjectError = "Application-defined or object-defined error"

On Error GoTo Error_AccessAndJetErrorsTable
' Create Errors table with ErrorNumber and ErrorDescription fields.
Set dbs = CurrentDb
Set tdf = dbs.CreateTableDef("AccessAndJetErrors")
Set fld = tdf.CreateField("ErrorCode", dbLong)

tdf.Fields.Append fld
Set fld = tdf.CreateField("ErrorString", dbMemo)
tdf.Fields.Append fld

dbs.TableDefs.Append tdf
' Open recordset on Errors table.
Set rst = dbs.OpenRecordset("AccessAndJetErrors")
' Loop through error codes.
For lngCode = 0 To 3500
On Error Resume Next
' Raise each error.
strAccessErr = AccessError(lngCode)
DoCmd.Hourglass True
' Skip error numbers without associated strings.
If strAccessErr <> "" Then

' Skip codes that generate application or object-defined errors.
If strAccessErr <> conAppObjectError Then
' Add each error code and string to Errors table.
rst.AddNew
rst!ErrorCode = lngCode
' Append string to memo field.
rst!ErrorString.AppendChunk strAccessErr
rst.Update
End If
End If
Next lngCode
' Close recordset.
rst.Close
DoCmd.Hourglass False
RefreshDatabaseWindow
MsgBox "Access and Jet errors table created."

AccessAndJetErrorsTable = True

Exit_AccessAndJetErrorsTable:
Exit Function

Error_AccessAndJetErrorsTable:
MsgBox Err & ": " & Err.Description
AccessAndJetErrorsTable = False
Resume Exit_AccessAndJetErrorsTable
End Function
--
Hope this helps,

Daniel P
 
Top