Logging Errors

Djblois

Registered User.
Local time
Yesterday, 20:57
Joined
Jan 26, 2009
Messages
598
I am creating the ability to Log all my Users Errors. Here is the code I am using:

Code:
Sub LogError(strSub As String, lngErrCode As Integer, strErrDesc As String)

    Dim cnn As ADODB.Connection
    Dim strSQL As String
    
    Set cnn = CurrentProject.Connection
    
    
    strSQL = "INSERT INTO tblLog (ErrorNum, ErrMessage, UserName, ErrTime, BuildNum, CurrentSub) "
    
    strSQL = strSQL & "Select " & lngErrCode & ", '" & strErrDesc & "', " & strUserLogin _
        & ", #" & Date & "#, '" & DLookup("[VersionNum]", "tblVersion", "[VersionID] = 1") & _
        Format(DLookup("[VersionMinNum]", "tblVersion", "[VersionID] = 1"), ".00") & "', '" & strSub & "'"
        
    cnn.Execute strSQL, , adExecuteNoRecords

End Sub

However, when i get to the cnn.execute line I get an error stating "No Value given for one or more required parameters." But I keep strSQL into a watch window and they all come out.
 
You cannot use select...
If you use Select you have to have a "FROM" as well, which you dont...

The proper syntax your looking for is ...
Insert into tbl () VALUES ()
 
Thank you for your help. I tried this as you suggested:

Code:
Sub LogError(strSub As String, lngErrCode As Integer, strErrDesc As String)

    Dim cnn As ADODB.Connection
    Dim strSQL As String
    
    Set cnn = CurrentProject.Connection
    
    
    strSQL = "INSERT INTO tblLog (ErrorNum, ErrMessage, UserName, ErrTime, BuildNum, CurrentSub) "
    
    strSQL = strSQL & "Values " & lngErrCode & ", '" & strErrDesc & "', " & strUserLogin _
        & ", #" & Date & "#, " & DLookup("[VersionNum]", "tblVersion", "[VersionID] = 1") & _
        Format(DLookup("[VersionMinNum]", "tblVersion", "[VersionID] = 1"), ".00") & _
        Format(DLookup("[BuildNo]", "tblVersion", "[VersionID] = 1"), ".00") & ", '" & strSub & "'"
        
    cnn.Execute strSQL, , adExecuteNoRecords

End Sub

but i get a different error now. It says "Syntax error in INSERT INTO statement."
 
The proper syntax your looking for is ...
Insert into tbl () VALUES ()

Where are your () for the values?? You need to put () around the values... Like so...
Code:
    strSQL = ""
    strSQL = strSQL & "INSERT INTO tblLog (ErrorNum, ErrMessage, UserName, ErrTime, BuildNum, CurrentSub) "
    strSQL = strSQL & "Values ( " & lngErrCode & " "
    strSQL = strSQL & ", '" & strErrDesc & "' "
    strSQL = strSQL & ", " & strUserLogin & " "
    strSQL = strSQL & ", #" & Date & "# "
    strSQL = strSQL & ", " & DLookup("[VersionNum]", "tblVersion", "[VersionID] = 1")
    strSQL = strSQL & Format(DLookup("[VersionMinNum]", "tblVersion", "[VersionID] = 1"), ".00")
    strSQL = strSQL & Format(DLookup("[BuildNo]", "tblVersion", "[VersionID] = 1"), ".00")
    strSQL = strSQL & ", '" & strSub & "' ) "

Also the 3 DLookups are UGLY and highely Inefficient, though it probably dont matter at all as the tblVersion will be very very small.
Yes using a recordset and a select statemtent would be "much" better.

#2 thing to notice is I think your missing '' around strUserLogin as well as the BuildNum
 
:D
Searched for an hour, looked over many error table logging functions.
Don't look any furhter! This is the best sample on the World Wide Web!
The err trap for the err function is a wonderful idea.
Suggest that everyone vote to give them credit for a great solution.
My code is only very slightly modified from the post above.
Only posting to help beginners understand how it might be modified.
Code:
Sub ErrorLog(strBuildVersion As String, _
                lngErrorNumber As Long, _
                strErrorDesc As String, _
                strModule As String, _
                strProcedure As String, _
                strVersion As String)
    Const strErrorTable     As String = "tbl_ErrorLog"
    Dim strSQL              As String
    Dim StrErrText          As String
    Dim strTitle            As String
 
    On Error GoTo ErrorPlace
 
    strTitle = vbNullString
    strTitle = "Error Notification  (" & strProcedure & ")"
    StrErrText = vbNullString
    StrErrText = StrErrText & "The following error occurred..."
    StrErrText = StrErrText & vbCrLf & vbCrLf
    StrErrText = StrErrText & "Error Number:  " & CStr(lngErrorNumber)
    StrErrText = StrErrText & vbCrLf
    StrErrText = StrErrText & "Error Description:  " & strErrorDesc
    StrErrText = StrErrText & vbCrLf & vbCrLf
    StrErrText = StrErrText & "In the procedure: " & strProcedure
    StrErrText = StrErrText & vbCrLf & vbCrLf
    StrErrText = StrErrText & "In the module: " & strModule
 
    'MsgBox StrErrText, vbOKCancel, StrTitle
    ' the global gvVersionDate = Build version      For now, I add the version number on the switchboard in a label and store it in the global variable on load event
    strSQL = "INSERT INTO " & strErrorTable & " (  UserID, BuildVersion, ErrorNumber,  ErrorDescription, ErrorTime, [Module], [Procedure],[Database] ) "
    strSQL = strSQL & " SELECT """ & Environ(35) & ""","""
    strSQL = strSQL & strBuildVersion & ""","                               ' build version
    strSQL = strSQL & lngErrorNumber & ", """                               ' VBA error number
    strSQL = strSQL & strErrorDesc & """, #"                                ' vba err.description
    strSQL = strSQL & Format(Now(), "mm/dd/yyyy hh:mm:ss") & "#, """        ' Time Stamp
    strSQL = strSQL & strModule & """, """                                  ' pass in module or Form name
    strSQL = strSQL & strProcedure & """, """                               ' pass in procedure name
    strSQL = strSQL & CurrentDb.Name & """ ;"                               ' get Database name
    With DoCmd
        .SetWarnings (False)
        .Hourglass (True)
        .RunSQL strSQL
        .SetWarnings (True)
        .Hourglass (False)
    End With
 
    MsgBox "An error '" & strErrorDesc & "' occurred in some code, things may not have run completely, The error has been Logged for evaluation"
 
    Exit Sub
 
ErrorPlace:
    'funnily enough you can *still* get errors here. e.g. you have no permissions to write to the errotable
    'MsgBox "Error in creating ErrorLog: " & Err.Description
    MsgBox "An error occurred in some code, things may not have run completely"
    DoCmd.SetWarnings (True)
    DoCmd.Hourglass (False)
End Sub
 
'Output of strsql
' To run - copy this Call into your immediate window   e.g.   ?  Call errlog("ver 12", .....
'call errorlog("ver 12", 3501 , "myerrdesc", "myform", "updatemodule", "dbversion")
'Then in the immediate window, run          Print strSQL
'INSERT INTO tbl_ErrorLog (  UserID, BuildVersion, ErrorNumber,  ErrorDescription, ErrorTime, [Module], [Procedure],[Database] )
'              SELECT "USERNAME=Rx", ver 12, "3501, "myerrdesc", #01/28/2010 11:05:39#, "myform", "updatemodule", "X:\IRS\DenverDB\Regulatory\Audit List\Next Version Enforcement\Audits_Denver.accdb" ;
'  Build a linked table tbl_ErrorLog (assuming you have a split database)
' with an autocounter field, plus the following fields : UserID, BuildVersion, ErrorNumber,  ErrorDescription, ErrorTime, Module, Procedure, Database
 
I have this code in my database with the table all ready to go, but I am still unsure how to make it happen when an error comes up.

Thanks
 
Here is the best resource I've ever used. It is well documented and provides notes to know how to use. The first part will tell you where to call the function and to trap the error.

HTH,
-dK
 

Users who are viewing this thread

Back
Top Bottom