jwcolby
jwcolby at colbyconsulting.com
Tue Jan 20 08:36:19 CST 2009
Sorry for the delay getting back to you.
NOTE: This stuff will not work in Access 97 due to the fact
that 97 did not have the VBA Editor.
To use my error logging system you need to download and
install MZ-Tools 3.0.
http://www.mztools.com/v3/mztools3.aspx
MZ-Tools provides a method of inserting an error handler
into code. Unfortunately there is no method of just
blasting it into a bunch of functions, or a bunch of
different modules so you are faced with a function by
function retrofit. However for new code this is not an
issue of course.
Once you have MZ-Tools 3.0 installed, open Access and open
any code module. The MZ-Tools tool bar should now appear in
the code editor window.
At the extreme right of that toolbar there is an icon
containing a crossed hammer and wrench. Click that icon and
an Options dialog should open. This is where you set up
MZ-Tools to your liking.
Click on the Error Handler tab and insert the following code
(watch for line wraps):
On Error GoTo Err_{PROCEDURE_NAME}
{PROCEDURE_BODY}
Exit_{PROCEDURE_NAME}:
On Error resume next
Exit {PROCEDURE_TYPE}
Err_{PROCEDURE_NAME}:
Select Case Err
Case 0 '.insert Errors you wish to ignore here
Resume Next
Case Else '.All other errors will trap
Beep
LogErr Err.Number, Err.Description, Erl,
cstrModule, "{PROCEDURE_NAME}"
Resume Exit_{PROCEDURE_NAME}
End Select
Resume 0 '.FOR TROUBLESHOOTING
Click on the Module Header tab and insert the following code:
'---------------------------------------------------------------------------------------
' Module : {MODULE_NAME}
' Author : {AUTHOR}
' Date : {DATE}
' Purpose :
'---------------------------------------------------------------------------------------
Const cstrModule As String = "{MODULE_NAME}"
Click the OK button to close the Options Dialog and save the
changes.
In the VBA editor create a new module. Save the module and
name it basErrLog or something similar. Watch for line wraps.
Private Declare Function GetComputerName _
Lib "kernel32" Alias "GetComputerNameA" ( _
ByVal lpBuffer As String, nSize As Long) As Long
Private Const MAX_COMPUTERNAME_LENGTH As Long = 15&
'Type the following procedure:
Public Function CurrentMachineName() As String
On Error GoTo Err_CurrentMachineName
Dim lSize As Long
Dim sBuffer As String
sBuffer = Space$(MAX_COMPUTERNAME_LENGTH + 1)
lSize = Len(sBuffer)
If GetComputerName(sBuffer, lSize) Then
CurrentMachineName = Left$(sBuffer, lSize)
End If
Exit_CurrentMachineName:
Exit Function
Err_CurrentMachineName:
Select Case err
Case 0 '.insert Errors you wish to ignore here
Resume Next
Case Else '.All other errors will trap
Beep
MsgBox err.Description, , "Error in Function
basWorkStationName.CurrentMachineName"
Resume Exit_CurrentMachineName
End Select
Resume 0 '.FOR TROUBLESHOOTING
End Function
'To test this function, type the following line in the Debug
window, and then press ENTER:
'Print CurrentMachineName()
In this new module insert the following code:
'---------------------------------------------------------------------------------------
' Procedure : LogErr2
' Author : jwcolby
' Date : 8/4/2008
' Purpose :
'---------------------------------------------------------------------------------------
'
Function LogErr(intErrNo As Integer, strErr As String,
Optional lngLineNo As Long = 0, Optional strModule As String
= "", Optional strFunction As String = "", Optional
strExtraExplanation As String = "", Optional blnSilentLog As
Boolean = False, Optional strSilentLogMsg As String = "") As
Integer
Dim rs As ADODB.Recordset
On Error GoTo Err_LogErr
Dim lstrErr As String
Dim lstrErrMsg As String
LogErr = intErrNo
lstrErrMsg = strErr
lstrErrMsg = Replace(lstrErrMsg, "'", "")
lstrErr = lstrErrMsg
If Len(strExtraExplanation) > 0 Then
lstrErr = lstrErr & vbCrLf & vbCrLf &
strExtraExplanation
End If
Set rs = New ADODB.Recordset
With rs
On Error Resume Next
.Open "usystblErrLog", gcnn, adOpenKeyset,
adLockPessimistic
If err = 0 Then
.AddNew
!ErrLog_No = intErrNo
!ErrLog_Msg = lstrErr
!ErrLog_Module = strModule
!ErrLog_Function = strFunction
!ErrLog_LineNo = lngLineNo
!ErrLog_WSName = CurrentMachineName()
!ErrLog_IDUser = gclsFW.LWSCls.UserCls.UserPEID()
!ErrLog_FEVersion = gclsFW.pFEVersion
.Update
.Close
Else
MsgBox "No error log table available to log
errors in"
End If
End With
Set rs = Nothing
Exit_LogErr:
On Error Resume Next
rs.Close
Set rs = Nothing
Exit Function
Err_LogErr:
Select Case err
Case 0 '.insert Errors you wish to ignore here
Resume Next
Case Else '.All other errors will trap
Beep
LogErr err.Number, err.Description, Erl,
cstrModule, "LogErr"
Resume Exit_LogErr
End Select
Resume 0 '.FOR TROUBLESHOOTING
End Function
Notice that my framework provides a currently logged in user
ID as well as a FE version number. You need to replace
those function calls with similar code from your own system
or just replace them with a hard coded number (0 for the ID)
and string ("") for the FE version number.
//************************************************************************
Next you need to create a table in your BE, and link it into
your FE. The table needs to have the following fields:
ErrLog_ID Autonumber
ErrLog_No Long Holds the Err.No
ErrLog_Msg Memo Holds the error message
ErrLog_Module Text Holds the name of the module
ErrLog_Function Text Name of the function
ErrLog_LineNo long Error line number
ErrLog_WSName text Workstation name
ErrLog_IDUser long Logged in user ID
ErrLog_Dte dateTime Date of error
ErrLog_Time datetime Time of error
ErrLog_ExtraInfo Memo Other info about error
ErrLog_FEVersion text FE version creating error
To use, in the code editor, place your cursor into any
function with no error handler. In the MZ-Tools toolbar
click the Add Error Handler button. An error handler will
be inserted into that function. Also click the Add Module
Header button (once per module) to insert a constant at the
top of the module which is the name of the module.
Repeat for any function that needs an error handler.
The one thing I miss about my old error handler insertion
wizard was the ability to do this to an entire project but
alas, it does nto exist and the author is not doing any
enhancements to MZ-Tools. However it works and with an hour
or two of work you can retrofit an entire application.
Let me know if I missed anything, or there is anything you
can't figure out.
John W. Colby
www.ColbyConsulting.com
Rocky Smolin at Beach Access Software wrote:
> John:
>
> I'd be interested. I have a client who was looking for something like this
> for an app one function of which runs unattended from Windows scheduler at
> night. Supposed to email reports to his clients. Any glitch and the thing
> stops and he comes in in the morning to find an error message on the screen.
>
> TIA
>
>
> Rocky Smolin
> Beach Access Software
> 858-259-4334
> www.e-z-mrp.com
> www.bchacc.com