"Syntax error in INSERT INTO statement." 2

[newguy86]

I am trying to create a log that will automatically record any changes to data in a table. But whenever I try to run the logger I keep getting the error message "Syntax error in INSERT INTO statement". I have tried to see what each value is before the SQL statement runs to make sure that the code can read the data correctly and each item comes back perfectly fine. Here is the code if anyone would be able to help out in resolving this.

Public Function WriteAudit(frm As Form, lngID As Long) As Boolean
On Error GoTo err_WriteAudit

    Dim ctlC As Control
    Dim fldF As Field
    Dim strSQL As String
    Dim bOK As Boolean
    Dim strFormID As String
    
    bOK = False
    strFormID = "05"
    DoCmd.SetWarnings False
    
    ' For each control.
    For Each ctlC In frm.Controls
        If TypeOf ctlC Is TextBox Or TypeOf ctlC Is ComboBox Then
            If ctlC.Value <> ctlC.OldValue Or IsNull(ctlC.OldValue) Then
                If Not IsNull(ctlC.Value) Then
                    strSQL = "INSERT INTO tblDbLog (RecordID, UserID, Db-FrmID, ActivityID, FieldChanged, FieldChangedFrom, FieldChangedTo, DateofHit) " & _
                           "SELECT" & "'" & Form_Complaints.Incident_Number & "', " & _
                           "'" & GetUserName_TSB() & "', " & _
                           "'" & "04" & strFormID & "', " & _
                           "'" & "01" & "', " & _
                           "'" & ctlC.Name & "', " & _
                           "'" & ctlC.OldValue & "', " & _
                           "'" & ctlC.Value & "', " & _
                           "'" & Now() & "'"
                    'Debug.Print strSQL
                    DoCmd.RunSQL strSQL
                End If
            End If
        End If
    Next ctlC
    
    WriteAudit = bOK
    
exit_WriteAudit:
    DoCmd.SetWarnings True
    Exit Function
    
err_WriteAudit:
    MsgBox Err.Description
    Resume exit_WriteAudit
    
End Function

Travis

http://www.enterprise.com

 

[PWise]

is dateofhit a string or a date field if it is a date change to

   "SELECT" & "'" & Form_Complaints.Incident_Number & "', " & _
                           "'" & GetUserName_TSB() & "', " & _
                           "'" & "04" & strFormID & "', " & _
                           "'" & "01" & "', " & _
                           "'" & ctlC.Name & "', " & _
                           "'" & ctlC.OldValue & "', " & _
                           "'" & ctlC.Value & "', " & _
                        [COLOR=red]   "#" & Now() & "#" [/color]

 

[PHV]

If all the fields are defined as text in tblDbLog, I'd try this:

strSQL = "INSERT INTO tblDbLog (RecordID, UserID, [Db-FrmID], ActivityID, FieldChanged, FieldChangedFrom, FieldChangedTo, DateofHit) " & _
  "VALUES ('" & Form_Complaints.Incident_Number & "', " & _
  "'" & GetUserName_TSB() & "', " & _
  "'04" & strFormID & "', " & _
  "'01', " & _
  "'" & ctlC.Name & "', " & _
  "'" & ctlC.OldValue & "', " & _
  "'" & ctlC.Value & "', " & _
  "'" & Now() & "')"
Debug.Print strSQL

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[newguy86]

I have tried both solutions and neither have worked for me.

Another thing that I wanted to add was that the fields in 'tblDbLog' are setup with a Data Type of Memo, if that helps any.

Travis

http://www.enterprise.com

 

[PHV]

What was the output of the Debug.Print strSQL instruction ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[newguy86]

That actually is a piece of code that I need to take out. I left as a comment because I wanted to remove it but if I decided to include it in later than I would have the code right there to use. So in other words it is an unnecessary piece of code that will be taken out once I get this program working.

Travis

http://www.enterprise.com

 

[PHV]

it is an unnecessary piece of code
Certainly not as is it the way to discover the syntax error in your SQL code !

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[newguy86]

Well everytime I try to use it I can never get the code to print anything.

Travis

http://www.enterprise.com

 

[newguy86]

Thank you to all who responded. It turns out that the "-" in one of my field names is what was causing the issue with the log. I have removed it and now everything is working great.

Travis

http://www.enterprise.com