Access Insert Statement Runtime error '3134'

[ElSteveO]

Hello,
I have searched the forums and can't find an answer. I get a runtime error '3134' with the following insert statement:

Private Sub cmdSaveQ_Click()


DoCmd.RunSQL " insert into tblquotes" & _
"(tickname, tickprice, wfsize, bordername, borderht, borderprice, qdate, style, cust, tcost, fcost, qcost, kcost, qmu)," & _
"values" & _
"('txttickname', txttickprice, txtwfsize, 'txtbordername', txtborderht, txtborderprice, txtdate, 'txtstyle', 'txtcust', txttcost, txtfcost, txtqcost, txtkcost, txtmu);"

DoCmd.Close

End Sub

Can you help me find my error?
Thanks,
Steve

 

[ElSteveO]

I'm going to provide my own answer. I removed the comma after the first closing parenthesis and it worked.

DoCmd.RunSQL " insert into tblquotes" & _
"(tickname, tickprice, wfsize, bordername, borderht, borderprice, qdate, style, cust, tcost, fcost, qcost, kcost, qmu)[highlight #A40000],[/highlight]" & _

 

[Andrzejek]

You may try something like this in the future

Private Sub cmdSaveQ_Click()[blue]
Dim strSQL As String

strSQL = [/blue]" insert into tblquotes" & _
"(tickname, tickprice, wfsize, bordername, borderht, borderprice, qdate, style, cust, tcost, fcost, qcost, kcost, qmu) " & _
"values" & _
"('txttickname', txttickprice, txtwfsize, 'txtbordername', txtborderht, txtborderprice, txtdate, 'txtstyle', 'txtcust', txttcost, txtfcost, txtqcost, txtkcost, txtmu);"
[blue]
Debug.Print strSQL
[/blue]
DoCmd.RunSQL [blue]strSQL[/blue]
DoCmd.Close

End Sub

This way you can see what you send to your data base, and it is a lot easier to spot problems with any SQL

Just the suggestion....

Have fun.

---- Andy

 

[ElSteveO]

Andy,
I appreciate your help! I will definitely try your method.
All the best,
Steve

 

[PHV]

And won't you replace this:
[tt]"('txttickname', txttickprice, ...[/tt]
with this ?
[tt]"('" & txttickname & "'," & txttickprice & ", ...[/tt]

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

 

[ElSteveO]

Andy,
I replaced my code with your code but didn't notice anything different. Then I purposely put the comma back in the statement to induce the same error. I thought the debug.print statement would show my error in the immediate window, but, again, I didn't notice a difference. It printed my exact statement in the immediate window. How would I use your suggested code to better spot errors? Thanks for the input.

PH, I appreciate your suggestion.

Thanks,
Steve

 

[Andrzejek]

[tt]Debug.Print[/tt] will NOT show you any errors, you will NOT "notice anything different". ALL what it does is give you the easy way to see the SQL that you are sending to your data base. That's all.

Sorry if I miss-lead you to believe Debug will improve your code in any way or fix any errors :-(

Have fun.

---- Andy

 

[PHV]

PH, I appreciate your suggestion
Did you understand it and follow it ?

 

[ElSteveO]

Thanks Andy.

PHV - I didn't follow it. Perhaps I don't understand it. I believe you are saying the single quotes go around text, but not all my fields are text fields. Any input is appreciated. Thanks for your help.

Steve

 

[PHV]

In the SQL string, you want the values, not the variables names.
DoCmd.RunSQL " INSERT INTO tblquotes " & _
"(tickname,tickprice,wfsize,bordername,borderht,borderprice,qdate,style,cust,tcost,fcost,qcost,kcost,qmu) " & _
"VALUES " & _
"('" & txttickname & "'," & txttickprice & "," & txtwfsize & ",'" & txtbordername & "'," & txtborderht & _
"," & txtborderprice & "," & txtdate & ",'" & txtstyle & "','" & txtcust & "'," & txttcost & "," & _
txtfcost & "," & txtqcost & "," & txtkcost & "," & txtmu & ")"


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

 

[ElSteveO]

PH, the way I wrote it works. It appends the table with the proper data. I'm not sure I understand how your suggestion is different. I'm not saying my way is better, just that it works. Regardless, I appreciate the feedback.
steve

 

[PHV]

the way I wrote it works
Which way ?
I highly doubt the code you posted does what you want.

 

[ElSteveO]

Here's the current code in my database:

Private Sub cmdSaveQ_Click()
Dim strSQL As String

strSQL = " insert into tblquotes" & _
"(tickname, tickprice, wfsize, bordername, borderht, borderprice, qdate, style, cust, tcost, fcost, qcost, kcost, qmu, notes)" & _
"values" & _
"('txttickname', txttickprice, txtwfsize, 'txtbordername', txtborderht, txtborderprice, txtdate, 'txtstyle', 'txtcust', txttcost, txtfcost, txtqcost, txtkcost, txtmu, txtnotes);"
Debug.Print strSQL
DoCmd.RunSQL strSQL
End Sub

This code works properly as far as I can tell.

 

[dhookom]

I'm with PH in that I can't understand how that code would work. Can you provide the results of the Debug.Print strSQL?

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

You really get the values you wanted to append in tblquotes ?

 

[ElSteveO]

yes, I do.

 

[Andrzejek]

I had the very same discussion before on this Forum (well, on TT anyway), and apparently Access is smart enough to convert the string like ElSteveO have and substitute parts of it with actual values of controls mentioned !

I couldn’t believe it myself, but… what do I know?


Have fun.

---- Andy

 

[ElSteveO]

So it sounds like I didn't use the proper format but Access converted it for me. As long as it works I'm happy, but I will try and use the proper format in the future.