"Syntax Error in INSERT INTO statement"...

  • Thread starter Thread starter Ron
  • Start date Start date
R

Ron

Hello,

I have some code (commented out for now) that I am trying
to add on to the following:

Private Sub SaveButton_Click()
On Error GoTo Err_SaveButton_Click

' Add observe record
' Determine call id of most recently added observe record
' Add error records
' Reset unbound fields except observation date and reviewer

DoCmd.SetWarnings (False)

SQLStr1 = "INSERT INTO [Call Table] (CSAID,
ObservationDate"
SQLStr2 = [Forms]![Phone Observe Adds].[CSAID] & ", "
SQLStr3 = "#" & [Forms]![Phone Observe Adds].
[ObservationDate] & "#, "

If ("" = [Forms]![Phone Observe Adds].[ReviewerName])
Then
SQLStr4 = ""
Else
SQLStr1 = SQLStr1 & ", ReviewerName"
SQLStr4 = "'" & [Forms]![Phone Observe Adds].
[ReviewerName] & "', "
End If

If ("" = [Forms]![Phone Observe Adds].[VOCCode]) Then
SQLStr5 = ""
Else
SQLStr1 = SQLStr1 & ", VOCCode"
SQLStr5 = "'" & [Forms]![Phone Observe Adds].
[VOCCode] & "', "
End If

If ("" = [Forms]![Phone Observe Adds].[GroupNbr]) Then
SQLStr6 = ""
Else
SQLStr1 = SQLStr1 & ", GroupNbr"
SQLStr6 = "'" & [Forms]![Phone Observe Adds].
[GroupNbr] & "', "
End If

If ("" = [Forms]![Phone Observe Adds].[Product]) Then
SQLStr7 = ""
Else
SQLStr1 = SQLStr1 & ", Product"
SQLStr7 = "'" & [Forms]![Phone Observe Adds].
[Product] & "', "
End If

If ("" = [Forms]![Phone Observe Adds].[CallBack]) Then
SQLStr1 = SQLStr1 & ", CallBack) VALUES ("
SQLStr8 = "'No');"
Else
SQLStr1 = SQLStr1 & ", CallBack) VALUES ("
SQLStr8 = "'" & [Forms]![Phone Observe Adds].
[CallBack] & "');"
End If

'If ("" = [Forms]![Phone Observe Adds].[Comments]) Then
' SQLStr1 = SQLStr1 & ", Comments) VALUES ("
' SQLStr9 = "' ');"
'Else
' SQLStr1 = SQLStr1 & ", Comments) VALUES ("
' SQLStr9 = """" & [Forms]![Phone Observe Adds].
[Comments] & """);"
'End If

SQLStr10 = SQLStr1 & SQLStr2 & SQLStr3 & SQLStr4 &
SQLStr5 & SQLStr6 & SQLStr7 & SQLStr8 '& SQLStr9
DoCmd.RunSQL SQLStr10

Access gives me the error message, "Syntax Error in INSERT
INTO statement"... when trying to enter comments on the
form. Can someone help decipher what Access is not liking
with my code?

Thank you!

Ron
 
I believe the problem is here..

SQLStr9 = """" & [Forms]![Phone Observe Adds].[Comments]
& """);"

First Access SQL uses " intead of ' (used in SQL) to
surround Text

Also For Quotes I use Chr(34)

Try this Example:
SQLStr9 = Chr(34) & [Forms]![Phone Observe Adds].
[Comments] & Chr(34) & ");"

This should work or I believe lead you the right track.

Jeff
 
Hello Jeff,

I just tried that in my code and it still did not work.
I'd like to know what exactly it's erroring on. Here is
the code as originally written:

If ("" = [Forms]![Phone Observe Adds].[Comments]) Then
SQLStr1 = SQLStr1 & ", Comments) VALUES ("SQLStr9
= "' ');"
Else
SQLStr1 = SQLStr1 & ", Comments) VALUES ("
SQLStr9 = """" & [Forms]![Phone Observe Adds].
[Comments] & """);"
End If
 
Ron said:
Hello,

I have some code (commented out for now) that I am trying
to add on to the following:

Private Sub SaveButton_Click()
On Error GoTo Err_SaveButton_Click

' Add observe record
' Determine call id of most recently added observe record
' Add error records
' Reset unbound fields except observation date and reviewer

DoCmd.SetWarnings (False)

SQLStr1 = "INSERT INTO [Call Table] (CSAID,
ObservationDate"
SQLStr2 = [Forms]![Phone Observe Adds].[CSAID] & ", "
SQLStr3 = "#" & [Forms]![Phone Observe Adds].
[ObservationDate] & "#, "

If ("" = [Forms]![Phone Observe Adds].[ReviewerName])
Then
SQLStr4 = ""
Else
SQLStr1 = SQLStr1 & ", ReviewerName"
SQLStr4 = "'" & [Forms]![Phone Observe Adds].
[ReviewerName] & "', "
End If

If ("" = [Forms]![Phone Observe Adds].[VOCCode]) Then
SQLStr5 = ""
Else
SQLStr1 = SQLStr1 & ", VOCCode"
SQLStr5 = "'" & [Forms]![Phone Observe Adds].
[VOCCode] & "', "
End If

If ("" = [Forms]![Phone Observe Adds].[GroupNbr]) Then
SQLStr6 = ""
Else
SQLStr1 = SQLStr1 & ", GroupNbr"
SQLStr6 = "'" & [Forms]![Phone Observe Adds].
[GroupNbr] & "', "
End If

If ("" = [Forms]![Phone Observe Adds].[Product]) Then
SQLStr7 = ""
Else
SQLStr1 = SQLStr1 & ", Product"
SQLStr7 = "'" & [Forms]![Phone Observe Adds].
[Product] & "', "
End If

If ("" = [Forms]![Phone Observe Adds].[CallBack]) Then
SQLStr1 = SQLStr1 & ", CallBack) VALUES ("
SQLStr8 = "'No');"
Else
SQLStr1 = SQLStr1 & ", CallBack) VALUES ("
SQLStr8 = "'" & [Forms]![Phone Observe Adds].
[CallBack] & "');"
End If

'If ("" = [Forms]![Phone Observe Adds].[Comments]) Then
' SQLStr1 = SQLStr1 & ", Comments) VALUES ("
' SQLStr9 = "' ');"
'Else
' SQLStr1 = SQLStr1 & ", Comments) VALUES ("
' SQLStr9 = """" & [Forms]![Phone Observe Adds].
[Comments] & """);"
'End If

SQLStr10 = SQLStr1 & SQLStr2 & SQLStr3 & SQLStr4 &
SQLStr5 & SQLStr6 & SQLStr7 & SQLStr8 '& SQLStr9
DoCmd.RunSQL SQLStr10

Access gives me the error message, "Syntax Error in INSERT
INTO statement"... when trying to enter comments on the
form. Can someone help decipher what Access is not liking
with my code?

Thank you!

Ron
Click to expand...
Just befor you run the Insert, add a message box.

msgbox SQLStr10,48
This will help you find any spacing you need and if you are missing any
' or " or ,

Ron
 
What was the code for SQLStr8 when you had this new code.

Kelvin

Ron said:
Hello Jeff,

I just tried that in my code and it still did not work.
I'd like to know what exactly it's erroring on. Here is
the code as originally written:

If ("" = [Forms]![Phone Observe Adds].[Comments]) Then
SQLStr1 = SQLStr1 & ", Comments) VALUES ("SQLStr9
= "' ');"
Else
SQLStr1 = SQLStr1 & ", Comments) VALUES ("
SQLStr9 = """" & [Forms]![Phone Observe Adds].
[Comments] & """);"
End If
-----Original Message-----
I believe the problem is here..

SQLStr9 = """" & [Forms]![Phone Observe Adds].[Comments]
& """);"

First Access SQL uses " intead of ' (used in SQL) to
surround Text

Also For Quotes I use Chr(34)

Try this Example:
SQLStr9 = Chr(34) & [Forms]![Phone Observe Adds].
[Comments] & Chr(34) & ");"

This should work or I believe lead you the right track.

Jeff
Click to expand...
Click to expand...
 
Hi Kelvin,

Here is my current code as it stands now. All I was trying
to do is the following:

1) Add a "Comments" field (memo type) to my form
2) Add that "Comments field to my table [Call Table]
3) Insert that "Comments" field into my Call Table

See if you can understand this code:

Private Sub SaveButton_Click()
On Error GoTo Err_SaveButton_Click

' Add observe record
' Determine call id of most recently added observe record
' Add error records
' Reset unbound fields except observation date and reviewer

DoCmd.SetWarnings (False)

SQLStr1 = "INSERT INTO [Call Table] (CSAID,
ObservationDate"
SQLStr2 = [Forms]![Phone Observe Adds].[CSAID] & ", "
SQLStr3 = "#" & [Forms]![Phone Observe Adds].
[ObservationDate] & "#, "
'SQLStr9 = Chr(34) & [Forms]![Phone Observe Adds].
[Comments] & Chr(34) & ");"

If ("" = [Forms]![Phone Observe Adds].[ReviewerName])
Then
SQLStr4 = ""
Else
SQLStr1 = SQLStr1 & ", ReviewerName"
SQLStr4 = "'" & [Forms]![Phone Observe Adds].
[ReviewerName] & "', "
End If

If ("" = [Forms]![Phone Observe Adds].[VOCCode]) Then
SQLStr5 = ""
Else
SQLStr1 = SQLStr1 & ", VOCCode"
SQLStr5 = "'" & [Forms]![Phone Observe Adds].
[VOCCode] & "', "
End If

If ("" = [Forms]![Phone Observe Adds].[GroupNbr]) Then
SQLStr6 = ""
Else
SQLStr1 = SQLStr1 & ", GroupNbr"
SQLStr6 = "'" & [Forms]![Phone Observe Adds].
[GroupNbr] & "', "
End If

If ("" = [Forms]![Phone Observe Adds].[Product]) Then
SQLStr7 = ""
Else
SQLStr1 = SQLStr1 & ", Product"
SQLStr7 = "'" & [Forms]![Phone Observe Adds].
[Product] & "', "
End If

If ("" = [Forms]![Phone Observe Adds].[CallBack]) Then
SQLStr1 = SQLStr1 & ", CallBack) VALUES ("
SQLStr8 = "'No');"
Else
SQLStr1 = SQLStr1 & ", CallBack) VALUES ("
SQLStr8 = "'" & [Forms]![Phone Observe Adds].
[CallBack] & "');"
End If

'If ("" = [Forms]![Phone Observe Adds].[Comments]) Then
' SQLStr1 = SQLStr1 & ", Comments) VALUES ("
' SQLStr9 = "' ');"
'Else
' SQLStr1 = SQLStr1 & ", Comments) VALUES ("
' SQLStr9 = Chr(34) & [Forms]![Phone Observe Adds].
[Comments] & Chr(34) & ");"
'End If

SQLStr9 = SQLStr1 & SQLStr2 & SQLStr3 & SQLStr4 &
SQLStr5 & SQLStr6 & SQLStr7 & SQLStr8 & SQLStr9
DoCmd.RunSQL SQLStr9

SQLStr10 = "INSERT INTO [Call Table] (Comments) VALUES
("
SQLStr11 = SQLStr10
DoCmd.RunSQL SQLStr11

-----Original Message-----
What was the code for SQLStr8 when you had this new code.

Kelvin

Ron said:
Hello Jeff,

I just tried that in my code and it still did not work.
I'd like to know what exactly it's erroring on. Here is
the code as originally written:

If ("" = [Forms]![Phone Observe Adds].[Comments]) Then
SQLStr1 = SQLStr1 & ", Comments) VALUES ("SQLStr9
= "' ');"
Else
SQLStr1 = SQLStr1 & ", Comments) VALUES ("
SQLStr9 = """" & [Forms]![Phone Observe Adds].
[Comments] & """);"
End If
-----Original Message-----
I believe the problem is here..

SQLStr9 = """" & [Forms]![Phone Observe Adds]. [Comments]
& """);"

First Access SQL uses " intead of ' (used in SQL) to
surround Text

Also For Quotes I use Chr(34)

Try this Example:
SQLStr9 = Chr(34) & [Forms]![Phone Observe Adds].
[Comments] & Chr(34) & ");"

This should work or I believe lead you the right track.

Jeff
Click to expand...
Click to expand...


.
Click to expand...
 
Hi Ron

Here is my code and the results. For some
reason, "Comments" is not being included in the INSERT
statement right after the variable "CallBack" and I don't
know why not.

Can you see what I'm missing here?

Thank you!

Results:

INSERT INTO [Call Table] (CSAID, ObservationDate,
ReviewerName, VOCCode,
GroupNbr, Product, CallBack) VALUES (,Comments) VALUES
(440, #2/25/2004#,
'Carrie','12345','67890','Classic
Blue','No');"fasassadasassadfds");

Private Sub SearchCSA_Click()
On Error GoTo Err_SearchCSA_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Phone Observe Adds"

stLinkCriteria = "[CSAId]=" & Me![SearchCSA]
DoCmd.OpenForm stDocName, , , stLinkCriteria

ClearAllFields

Exit_SearchCSA_Click:
Exit Sub

Err_SearchCSA_Click:
MsgBox Err.Description
Resume Exit_SearchCSA_Click
End Sub

Private Sub SaveButton_Click()
On Error GoTo Err_SaveButton_Click

' Add observe record
' Determine call id of most recently added observe record
' Add error records
' Reset unbound fields except observation date and reviewer

DoCmd.SetWarnings (False)

SQLStr1 = "INSERT INTO [Call Table] (CSAID,
ObservationDate"
SQLStr2 = [Forms]![Phone Observe Adds].[CSAID] & ", "
SQLStr3 = "#" & [Forms]![Phone Observe Adds].
[ObservationDate] & "#, "

If ("" = [Forms]![Phone Observe Adds].[ReviewerName])
Then
SQLStr4 = ""
Else
SQLStr1 = SQLStr1 & ", ReviewerName"
SQLStr4 = "'" & [Forms]![Phone Observe Adds].
[ReviewerName] & "', "
End If

If ("" = [Forms]![Phone Observe Adds].[VOCCode]) Then
SQLStr5 = ""
Else
SQLStr1 = SQLStr1 & ", VOCCode"
SQLStr5 = "'" & [Forms]![Phone Observe Adds].
[VOCCode] & "', "
End If

If ("" = [Forms]![Phone Observe Adds].[GroupNbr]) Then
SQLStr6 = ""
Else
SQLStr1 = SQLStr1 & ", GroupNbr"
SQLStr6 = "'" & [Forms]![Phone Observe Adds].
[GroupNbr] & "', "
End If

If ("" = [Forms]![Phone Observe Adds].[Product]) Then
SQLStr7 = ""
Else
SQLStr1 = SQLStr1 & ", Product"
SQLStr7 = "'" & [Forms]![Phone Observe Adds].
[Product] & "', "
End If

If ("" = [Forms]![Phone Observe Adds].[CallBack]) Then
SQLStr1 = SQLStr1 & ", CallBack) VALUES ("
SQLStr8 = "'No');"
Else
SQLStr1 = SQLStr1 & ", CallBack) VALUES ("
SQLStr8 = "'" & [Forms]![Phone Observe Adds].
[CallBack] & "');"
End If

If ("" = [Forms]![Phone Observe Adds].[Comments]) Then
SQLStr1 = SQLStr1 & ", Comments) VALUES ("
SQLStr9 = "' ');"
Else
SQLStr1 = SQLStr1 & ", Comments) VALUES ("
SQLStr9 = """" & [Forms]![Phone Observe Adds].
[Comments] & """ );"
End If

SQLStr10 = SQLStr1 & SQLStr2 & SQLStr3 & SQLStr4 &
SQLStr5 & SQLStr6 & SQLStr7 & SQLStr8 & SQLStr9
MsgBox SQLStr10, 48
DoCmd.RunSQL SQLStr10
 
Ron, your code is very complicated, try the following instead. Also you are
using the word VALUE. In Access, it should be SELECT. I beleive VALUE is
only used if you are running the code from a SQL server. Since it is
working, I'm assuming you are running SQL server or some equivalent.

-Begin Code-
Private Sub SaveButton_Click()
On Error GoTo Err_SaveButton_Click

SQLStr1 = "INSERT INTO [Call Table] (CSAID, ObservationDate"
SQLStr2 = "VALUE (" & [Forms]![Phone Observe Adds].[CSAID]
SQLStr2 = SQLStr2 & ", #" & [Forms]![Phone Observe Adds].[ObservationDate] &
"#"

If ([Forms]![Phone Observe Adds].[ReviewerName]<>"") Then
SQLStr1 = SQLStr1 & ", ReviewerName"
SQLStr2 = SQLStr2 & ", '" & [Forms]![Phone Observe Adds].[ReviewerName]
& "'"
End If

If ([Forms]![Phone Observe Adds].[VOCCode]<>"") Then
SQLStr1 = SQLStr1 & ", VOCCode"
SQLStr2 = SQLStr2 & ", '" & [Forms]![Phone Observe Adds].[VOCCode] & "'"
End If

If ([Forms]![Phone Observe Adds].[GroupNbr]<>"") Then
SQLStr1 = SQLStr1 & ", GroupNbr"
SQLStr2 = SQLStr2 & ", '" & [Forms]![Phone Observe Adds].[GroupNbr] &
"'"
End If

If ([Forms]![Phone Observe Adds].[Product]<>"") Then
SQLStr1 = SQLStr1 & ", Product"
SQLStr2 = SQLStr2 & ", '" & [Forms]![Phone Observe Adds].[Product] & "'"
End If

If ([Forms]![Phone Observe Adds].[CallBack]="") Then
SQLStr1 = SQLStr1 & ", CallBack"
SQLStr2 = SQLStr2 & ", 'No'" ' If you don't need this,
remove this section and change the code to match the rest.
Else
SQLStr1 = SQLStr1 & ", CallBack"
SQLStr2 = SQLStr2 & ", '" & [Forms]![Phone Observe Adds].[CallBack] &
"'"
End If

If ([Forms]![Phone Observe Adds].[Comments]<>"") Then
SQLStr1 = SQLStr1 & ", Comments"
SQLStr2 = SQLStr2 & ", " & Chr(34) & [Forms]![Phone Observe
Adds].[Comments] & Chr(34)
End If

SQLStr1 = SQLStr1 & ") "
SQLStr2 = SQLStr2 & ");"

SQLStr3 = SQLStr1 & SQLStr2
DoCmd.RunSQL SQLStr3

- End Code -
SQLStr10 = "INSERT INTO [Call Table] (Comments) VALUE ("
SQLStr11 = SQLStr10
DoCmd.RunSQL SQLStr11
Click to expand...

This part doesn't make sense, just leave it out. I'm guessing you forgot
something.

Kelvin
 
Sorry Kelvin,

Your code did not work. This is the screenshot I get
before it attempts to enter fields into the Call Table.

Results:

INSERT INTO [Call Table] (CSAID, ObservationDate,
ReviewerName, VOCCode,
GroupNbr, Product, CallBack) VALUES (,Comments) VALUES
(440, #2/25/2004#,
'Carrie','12345','67890','Classic
Blue','No');"fasassadasassadfds");

Notice what happens after the field CallBack?

There should be a "," and the word "Comments" following
that but there isn't.

Can you see why it's not including Comments into the list
of fields?

Thank you!

Ron
 
Are you sure you tried my code as I posted. Your code would cause this
problem becasue of the follwing code you had.

If ("" = [Forms]![Phone Observe Adds].[CallBack]) Then
SQLStr1 = SQLStr1 & ", CallBack) VALUES ("
SQLStr8 = "'No');"
Else
SQLStr1 = SQLStr1 & ", CallBack) VALUES ("
SQLStr8 = "'" & [Forms]![Phone Observe Adds].
[CallBack] & "');"
End If

'If ("" = [Forms]![Phone Observe Adds].[Comments]) Then
' SQLStr1 = SQLStr1 & ", Comments) VALUES ("
' SQLStr9 = "' ');"
'Else
' SQLStr1 = SQLStr1 & ", Comments) VALUES ("
' SQLStr9 = Chr(34) & [Forms]![Phone Observe Adds].
[Comments] & Chr(34) & ");"
'End If

When you concat them at the end SQLStr9 = SQLStr8 & SQLStr9, you get "...
,Call Back) VALUES, Comments) VALUES (". The code I posted should result
with SQLStr1 being "... , CallBack, Comments) " and SQLStr2 would start
"VALUES (...". You should not get VALUES twice.

Kelvin
 
Hi Kelvin,

I did retype your code. Here is what I got:

INSERT INTO [Call Table] (CSAID, ObservationDate,
ReviewerName, VOCCode,
GroupNbr, Product, CallBack, Comments) VALUE
(446,#2/26/2004#, 'Carrie',
'13213126546', '98798654645', 'Alliance
Select', 'No', "afasfasdfdsafdas");

Syntax error in INSERT INTO statement.

Everything looks right except it is STILL giving me that
syntax error. Access just doesn't want to add those
comments to my table.

Ron
-----Original Message-----
Are you sure you tried my code as I posted. Your code would cause this
problem becasue of the follwing code you had.

If ("" = [Forms]![Phone Observe Adds].[CallBack]) Then
SQLStr1 = SQLStr1 & ", CallBack) VALUES ("
SQLStr8 = "'No');"
Else
SQLStr1 = SQLStr1 & ", CallBack) VALUES ("
SQLStr8 = "'" & [Forms]![Phone Observe Adds].
[CallBack] & "');"
End If

'If ("" = [Forms]![Phone Observe Adds].[Comments]) Then
' SQLStr1 = SQLStr1 & ", Comments) VALUES ("
' SQLStr9 = "' ');"
'Else
' SQLStr1 = SQLStr1 & ", Comments) VALUES ("
' SQLStr9 = Chr(34) & [Forms]![Phone Observe Adds].
[Comments] & Chr(34) & ");"
'End If

When you concat them at the end SQLStr9 = SQLStr8 & SQLStr9, you get "...
,Call Back) VALUES, Comments) VALUES (". The code I posted should result
with SQLStr1 being "... , CallBack, Comments) " and SQLStr2 would start
"VALUES (...". You should not get VALUES twice.

Kelvin

Sorry Kelvin,

Your code did not work. This is the screenshot I get
before it attempts to enter fields into the Call Table.

Results:

INSERT INTO [Call Table] (CSAID, ObservationDate,
ReviewerName, VOCCode,
GroupNbr, Product, CallBack) VALUES (,Comments) VALUES
(440, #2/25/2004#,
'Carrie','12345','67890','Classic
Blue','No');"fasassadasassadfds");

Notice what happens after the field CallBack?

There should be a "," and the word "Comments" following
that but there isn't.

Can you see why it's not including Comments into the list
of fields?

Thank you!

Ron
Click to expand...


.
Click to expand...
 
Hi again...

Correction.. I noticed the word "VALUE" and changed it
to "VALUES" and it worked. Thank you very much for your
assistance. This was very puzzling for me.

Thanks!

Ron
-----Original Message-----
Are you sure you tried my code as I posted. Your code would cause this
problem becasue of the follwing code you had.

If ("" = [Forms]![Phone Observe Adds].[CallBack]) Then
SQLStr1 = SQLStr1 & ", CallBack) VALUES ("
SQLStr8 = "'No');"
Else
SQLStr1 = SQLStr1 & ", CallBack) VALUES ("
SQLStr8 = "'" & [Forms]![Phone Observe Adds].
[CallBack] & "');"
End If

'If ("" = [Forms]![Phone Observe Adds].[Comments]) Then
' SQLStr1 = SQLStr1 & ", Comments) VALUES ("
' SQLStr9 = "' ');"
'Else
' SQLStr1 = SQLStr1 & ", Comments) VALUES ("
' SQLStr9 = Chr(34) & [Forms]![Phone Observe Adds].
[Comments] & Chr(34) & ");"
'End If

When you concat them at the end SQLStr9 = SQLStr8 & SQLStr9, you get "...
,Call Back) VALUES, Comments) VALUES (". The code I posted should result
with SQLStr1 being "... , CallBack, Comments) " and SQLStr2 would start
"VALUES (...". You should not get VALUES twice.

Kelvin

Sorry Kelvin,

Your code did not work. This is the screenshot I get
before it attempts to enter fields into the Call Table.

Results:

INSERT INTO [Call Table] (CSAID, ObservationDate,
ReviewerName, VOCCode,
GroupNbr, Product, CallBack) VALUES (,Comments) VALUES
(440, #2/25/2004#,
'Carrie','12345','67890','Classic
Blue','No');"fasassadasassadfds");

Notice what happens after the field CallBack?

There should be a "," and the word "Comments" following
that but there isn't.

Can you see why it's not including Comments into the list
of fields?

Thank you!

Ron
Click to expand...


.
Click to expand...
 
Back
Top