Syntax error in INSERT (but only in one case!)




I have a public procedure in a standard module that updates a table, setting
variables depending on what form it is called from (CalledFrom passed as a

It works fine when called from the Distribution form (case Distribution)
but when called from the Contacts form, I get a syntax error on the INSERT
statement. What am I missing?

Public Sub LogEvent(LogRemarks As String, CalledFrom As String)
'This sub is called when we need to leave an audit trail.
' The calling function passes any remarks in LogRemarks
' and identifies what form calls it CalledForm so we can alter the query
by case.
'The fields in the LogEvents table are:
' LogEventID
' LogEventDateTime
' LogEventDocument
' LogEventRequestor
' LogEventContact
' LogEventRegDocSetID
' LogRemarks
' LogDocSent
' LogDocVersion
' LogDocTitle
' LogReceiptReceived
' LogEventCulprit
' LogEventCoordinator

'These variables are either set from the form fields or defined in the
case statement and passed to the LogEvent table
Dim LogEventDocument As Long
Dim LogEventCulprit As String
Dim LogEventRequestor As String
Dim LogEventContact As Long
Dim LogEventRegDocSetID As Long
Dim LogEventDocSent As Boolean
Dim LogEventDocVersion As Long
Dim LogEventDocTitle As String
Dim LogEventReceiptReceived As Date
Dim LogEventCoordinator As String

LogEventCulprit = "'" & UCase(Environ("UserName")) & "'"

'set variables depending on what form they're called from
Select Case CalledFrom
Case "Distribution" 'from the Distribution form

LogEventRequestor = "'" &
Forms!frmDistribution.Combo_Requestor.Value & "'"
LogEventContact = Forms!frmDistribution.Combo_Contact.Value
LogEventCoordinator =
LogEventRegDocSetID = RegDocSetID
' below fields don't exist on the distrib form, but leaving them
empty gives an insert error

LogEventDocument = 0
LogEventVersion = 0
LogEventTitle = ""
LogEventReceiptReceived = #1/1/100# 'this is our default blank

MsgBox "Case Distribution"

Case "Contacts" 'from the Contacts form
LogEventRequestor = ""
LogEventContact = Forms!frmContacts.PK_External_Contact_ID.Value
LogEventCoordinator = ""
LogEventRegDocSetID = 0
LogEventDocument = 0
LogEventVersion = 0
LogEventTitle = ""
LogEventReceiptReceived = #1/1/100# 'this is our default blank

MsgBox "Case Contacts: Requestor is" & "'" & LogEventRequestor &

Case "SendingDocs" 'from the Sending form
LogEventContact = PK_External_Contact_ID
LogEventRequestor = ""
LogEventCoordinator = ""
LogEventRegDocSetID = 0
LogEventDocument = 0
LogEventVersion = 0
LogEventTitle = ""
LogEventReceiptReceived = #1/1/100# 'this is our default blank

MsgBox "Case Sending"
Case Else
'If we don't know what form sending from, exit the procedure.
MsgBox "I don't know what form you're sending from: check your
case statement. Exiting Log Function without logging."
Exit Sub
End Select

Dim strQuery As String 'This is our SQL statement. You can create a
query and copy the SQL View into it
Dim cmdLog As New ADODB.Command 'A command will do our evil bidding

Set cmdLog.ActiveConnection = CurrentProject.Connection 'set the
connection property of the command

'this is our SQL
strQuery = "INSERT INTO tbl_LogEvents ( LogEventDateTime,
LogEventRegDocSetID, LogRemarks, LogEventDocument, LogEventDocVersion,
LogEventDocTitle, LogEventRequestor, LogEventContact, LogEventCoordinator,
LogEventDocSent, LogEventReceiptReceived, LogEventCulprit) " & _
" SELECT Now() , " & _
LogEventRegDocSetID & " ,' " & _
LogRemarks & "'," & _
LogEventDocument & " , " & _
LogEventDocVersion & " ,' " & _
LogEventDocTitle & "' , " & _
LogEventRequestor & " , " & _
LogEventContact & " ,' " & _
LogEventCoordinator & "' , " & _
LogEventDocSent & " , " & _
LogEventReceiptReceived & "," & _
LogEventCulprit & ";"

'Execute the command
cmdLog.CommandText = strQuery

'MsgBox strQuery

'Clean up
Set cmdLog = Nothing

End Sub

Michel Walsh

Between the two lines:

cmdLog.CommandText = strQuery


Debug.Print strQuery

Run the program, and look at the immediate (debug) window to see what SQL
command you are trying to execute. In theory, you should be able to cut and
paste it into a new query, in its SQL view, and then, once done, switch back
to the graphical view of the query editor to see what may be wrong.
Alternatively, if you don't see th eproblem, cut and paste the same SQL
satetement here, to see if someone can spot the error.

Vanderghast, Access MVP

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question