Prev: Combobox and error 3314
Next: MS ACCESS 2007 - FIND AND REPLACE NOT WORKING
From: aj on 30 May 2010 18:28
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) & ")"
From: Dirk Goldgar on 30 May 2010 18:43
"aj" <aj(a)discussions.microsoft.com> wrote in message
news:F3FD6624-CE04-4AD7-88A7-D7F3E04999DA(a)microsoft.com...
> 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.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

From: aj on 30 May 2010 20:05
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!

"Dirk Goldgar" wrote:

> "aj" <aj(a)discussions.microsoft.com> wrote in message
> news:F3FD6624-CE04-4AD7-88A7-D7F3E04999DA(a)microsoft.com...
> > 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.
>
> --
> Dirk Goldgar, MS Access MVP
> Access tips: www.datagnostics.com/tips.html
>
> (please reply to the newsgroup)
>
From: Larry Linson on 30 May 2010 20:29
"aj" <aj(a)discussions.microsoft.com> wrote

> 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


From: aj on 30 May 2010 21:19
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.

"Larry Linson" wrote:

> "aj" <aj(a)discussions.microsoft.com> wrote
>
> > 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
>
>
> .
>
 | 
Pages: 1
Prev: Combobox and error 3314
Next: MS ACCESS 2007 - FIND AND REPLACE NOT WORKING