Syntex Error

[aj]

Would someone please tell me what is wrong with this code?:

CurrentDb.Execute "INSERT INTO tblEntry (CreditorID, CategoryID, DueDate,
BillDesc, BillAmount, BillNotes, PaymentAmount," & _
"PaymentDate, PaymentMethod, CheckNumber, PaymentNotes, TotalDue
) " & _
"VALUES (" & cboCreditors.Column(0) & ", " & _
cboCategory.Column(0) & ", " & _
Format(txtDueDate, "\#yyyy\-mm\-dd\#") & ", " & _
txtBillDesc & ", " & _
Chr$(34) & txtBillAmount & Chr$(34) & ", " & _
txtBillNotes & "," & _
Chr$(34) & txtBillAmount & Chr$(34) & ", " & _
Format(txtPaymentDate, "\#yyyy\-mm\-dd\#") & ", " & _
cboPaymentMethod.Column(0) & ", " & _
txtCheckNumber & ", " & _
txtPaymentNotes & ", " & _
Chr$(34) & txtTotalDue & Chr$(34) & ")"

 

[Dirk Goldgar]

Would someone please tell me what is wrong with this code?:

CurrentDb.Execute "INSERT INTO tblEntry (CreditorID, CategoryID, DueDate,
BillDesc, BillAmount, BillNotes, PaymentAmount," & _
"PaymentDate, PaymentMethod, CheckNumber, PaymentNotes, TotalDue
) " & _
"VALUES (" & cboCreditors.Column(0) & ", " & _
cboCategory.Column(0) & ", " & _
Format(txtDueDate, "\#yyyy\-mm\-dd\#") & ", " & _
txtBillDesc & ", " & _
Chr$(34) & txtBillAmount & Chr$(34) & ", " & _
txtBillNotes & "," & _
Chr$(34) & txtBillAmount & Chr$(34) & ", " & _
Format(txtPaymentDate, "\#yyyy\-mm\-dd\#") & ", " & _
cboPaymentMethod.Column(0) & ", " & _
txtCheckNumber & ", " & _
txtPaymentNotes & ", " & _
Chr$(34) & txtTotalDue & Chr$(34) & ")"

It's a lot easier to diagnose when you tell what sort of error you get when
you run the code. I can only guess what your field types are, but it looks
to me like the values of txtBillDesc and txtBillNotes may be text, and ought
to be enclosed in quotes in the SQL string. Also, I would expect fields
with names like BillAmount PaymentAmount, and TotalDue to be Currency, and
therefore am surprised to see you enclosing the values of txtBillAmount and
txtTotal in quotes.

As an aid in debuggin, you may want to first build the SQL statement in a
string variable, and then display the value of that string variable to see
what you have built and whether it looks correct.

 

[aj]

Hi Dirk,

My apologies!! I get a "run-time error 3134: Syntax error in INSERT INTO
statement." message. Truth be told, I am copying old code, and wasn't sure
what that code meant, but I have removed it. Thank you!

 

[Larry Linson]

My apologies!! I get a "run-time error 3134:
Syntax error in INSERT INTO statement."
message. Truth be told, I am copying old
code, and wasn't sure what that code meant,
but I have removed it. Thank you!

It can be very helpful to have someone else's code to study and figure out
what they were doing. But, if you are going to copy and paste code without
"knowing what it is doing", you'd best be doing it only on test data -- next
time, it might clear all your tables, and if you were using your production
data, you'd be up the proverbial creek.

Larry Linson, Microsoft Access MVP

 

[aj]

Hi Larry,

Thanks for the advise! Thankfully, this is a learning database for me so
that I will not be up that "creek" when I need to really know how to do this!
I was able to get the code to work on another form. I just thought I would
expand my understanding of the code.