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

[DH]

Hi,

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
string).

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 =
Forms!frmDistribution.Combo_Coordinator.Value
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
date

MsgBox "Case Distribution"

Case "Contacts" 'from the Contacts form
' NOT WORKING YET
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
date

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

Case "SendingDocs" 'from the Sending form
'NEEDS TO BE UPDATED ONCE SENDING FORM IS FINALIZED
LogEventContact = PK_External_Contact_ID
LogEventRequestor = ""
LogEventCoordinator = ""
LogEventRegDocSetID = 0
LogEventDocument = 0
LogEventVersion = 0
LogEventTitle = ""
LogEventReceiptReceived = #1/1/100# 'this is our default blank
date

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
cmdLog.Execute

'MsgBox strQuery

'Clean up
Set cmdLog = Nothing


End Sub

 

[Michel Walsh]

Between the two lines:

cmdLog.CommandText = strQuery
cmdLog.Execute


insert

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