syntax error in INSERT statement - 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

 

[DH]

When I have the query output as a text box, requestor shows as null, despite
being set in the case. I think that's the problem, but I'm not sure how to
fix it.

 

[Steve Sanford]

DH,

I looked at your code and noticed several errors:

-- You didn't have a few variables declared. You should have these two lines
at the beginning of *every module* :

Option Compare Database
Option Explicit

In the IDE, click on TOOLS/OPTIONS. In the EDITOR tab, all check boxes
should be checked except the first one: "Auto Syntax Check".

-- In the code you posted, you are missing the "Select Case" line

-- There are two basic forms of the SQL "INSERT INTO" command. I modified
the code to use the single record append format.

-- You need to concatenate the values into the string.

-- Proper delimiters must be used.

Below is the modified code - with comments (enough, I hope)
-watch for line wrap-

'---------code beg------------
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

'#### I added missing declarations #####
Dim LogEventVersion As Long
Dim LogEventTitle As String
'########

LogEventCulprit = UCase(Environ("UserName"))

' below fields don't exist on the distrib form, but leaving them empty
gives an insert error

' below fields common to the three case options
' can be changed in the case option if needed
LogEventRequestor = ""
LogEventCoordinator = ""
LogEventRegDocSetID = 0

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

'#### didn't see where 'LogEventDocTitle' was assigned a value
LogEventDocTitle = ""
'#######

'###### missing Select Case here #################
' my guess is:
'
Select Case CalledFrom '??????????????
'
'
'########################################

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

LogEventRequestor = Forms!frmDistribution.Combo_Requestor
LogEventContact = Forms!frmDistribution.Combo_Contact
LogEventCoordinator = Forms!frmDistribution.Combo_Coordinator
LogEventRegDocSetID = RegDocSetID

MsgBox "Case Distribution"

Case "Contacts" 'from the Contacts form
' NOT WORKING YET
LogEventContact = Forms!frmContacts.PK_External_Contact_ID

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

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

'This is our SQL statement. You can create a query and copy the SQL View
into it
Dim strQuery As String

'A command will do our evil bidding
Dim cmdLog As New ADODB.Command

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


'================================
'USE this form of the "INSERT INTO.." SQL command
' INSERT INTO Table1 (Column1, Column2, Column3…)
' VALUES (Value1, Value2, Value3…)
'

'!!! You need to concatenate the values into the string
' using the proper delimiters !!!
'Example:
' INSERT INTO Customers (FirstName, LastName, Email, DOB, Phone)
' VALUES ('Peter', 'Hunt', '(e-mail address removed)', '1/1/1974', '626
888-8888')
'==================================

'this is our SQL -- reformatted for ease of reading--
strQuery = "INSERT INTO tbl_LogEvents ( LogEventDateTime,
LogEventRegDocSetID,"
strQuery = strQuery & " LogRemarks, LogEventDocument,"
strQuery = strQuery & " LogEventDocVersion, LogEventDocTitle,"
strQuery = strQuery & " LogEventRequestor, LogEventContact,"
strQuery = strQuery & " LogEventCoordinator, LogEventDocSent,"
strQuery = strQuery & " LogEventReceiptReceived, LogEventCulprit)"

strQuery = strQuery & " Values (#" & Now() & "#, " & LogEventRegDocSetID
strQuery = strQuery & ", '" & LogRemarks & "', " & LogEventDocument
strQuery = strQuery & ", " & LogEventDocVersion & ", '" & LogEventDocTitle
strQuery = strQuery & "', '" & LogEventRequestor & "', " & LogEventContact
strQuery = strQuery & ", '" & LogEventCoordinator & "', " & LogEventDocSent
strQuery = strQuery & ", #" & LogEventReceiptReceived & "#, '" &
LogEventCulprit & "';"

MsgBox strQuery

'Execute the command
cmdLog.CommandText = strQuery
cmdLog.Execute


'Clean up
Set cmdLog = Nothing


End Sub
'---------code end------------


HTH