error handling- writing to a table

mikeco555

Registered User.
Local time
Today, 16:30
Joined
Jan 23, 2004
Messages
22
I have implemented an error handling routine in all of my form, and it seems to work, except that when I try to write to a table I get an error message that states------syntax error(missing operator) in query expression "(the error message)' , '(user)',
#(date/time)#, '(Form name)'-----the debugger stops on the cnn.execute line of code.---- Everything but the error message is able to write to a table--- it is funny because the other strings are fine. Is it something to do with my form_error event handler??

here is my routine Sub ErrorRoutine()
Code:
'Declare my Variables
    Dim Cuser As String
    Dim strErrorDescription As String
    Dim lngErrorNum As Long
    Dim DateTime As Date
    Dim FormName As Variant
    
    'Set var's to current error
    lngErrorNum = Err.Number
    strErrorDescription = Err.Description
    Cuser = CurrentUser()
    DateTime = Now
    FormName = Me.Form.Caption
        
    'append the table tblErrorLog
    Dim cnn As ADODB.Connection
    Dim strSQL As String
    Set cnn = CurrentProject.Connection
    strSQL = "INSERT INTO tblErrorLog ( ErrorNum, ErrorString, CUser, CDateTime, FormName) "
    strSQL = strSQL & "Select " & lngErrorNum & ", '" & strErrorDescription & "', '" & Cuser & "', #" & DateTime & "#, '" & FormName & "' "
    
    MsgBox "Polaris Error Alert: Please document ! Your last action will be canceled---Error#: " & Err.Number & ": " & Err.Description, vbExclamation
    cnn.Execute strSQL, , adExecuteNoRecords  
    DoCmd.CancelEvent
    Exit Sub
End Sub

HELP PLEASE!!!!!!!
thanks,
Michael Oakes
 
Last edited by a moderator:
You can't just select any old value into a table the way you want.

i.e.

strSQL = strSQL & "Select " & lngErrorNum & ", '" & strErrorDescription & _


It needs to be a field name or a function's return value.

Your best option is to expand on that Connection you have, dimension an ADODB.Recordset and .AddNew the details into the relevant fields.
 
Don't know if this will make any difference, but the execute line should be:
cnn.Execute strSQL, adExecuteNoRecords, adCmdText

[nitpick]and why is your FormName variable a variant[/nitpick]
 
dcx693 said:
[nitpick]and why is your FormName variable a variant[/nitpick]

And another nitpick: FormName = Me.Form.Caption

Code:
Me.Caption
 
Thanks Mile,

but I am a little lost.
could you possibly post the code for the adodb.recordset,
and the .addNew method?????---As you can tell I am a new programmer fresh out of school!

Thanks so much....
 

Users who are viewing this thread

Back
Top Bottom