Debugging Access VBA Currentdb.Execute

Question

It says invalid insert statement when I run this code.

CurrentDb.Execute "insert into invoices (Stateid, companyid, PaymentYear,InvoiceDate,Type,MethodofPayment,date,amount) " & _
"values('" & Me.StateID & "','cOOKIE','" & Me.PaymentYear & "', '" & Me.InvoiceDate & "'," _
& "'" & Me.Type & "','" & Me.MethodofPayment & "','" & Me.Date & "','" & Me.Amount & "');"

How to debug dynamic SQL in VBA

Andre
– Andre

2018-06-20 16:04:04 +00:00
Commented Jun 20, 2018 at 16:04 — Andre
– Andre, Commented Jun 20, 2018 at 16:04

Gustav · Accepted Answer · 2018-06-20 16:14:18Z

1

As a start, provide valid string expressions for the values - not everything is text - and respect reserved words:

CurrentDb.Execute "insert into invoices (Stateid, companyid, PaymentYear, InvoiceDate, [Type], MethodofPayment, [date], amount) " & _
"Values('" & Me.StateID & "','cOOKIE'," & Me.PaymentYear & ", #" & Format(Me.InvoiceDate, "yyyy\/mm\/dd") & "#, " & _
"'" & Me.Type & "','" & Me.MethodofPayment & "',#" & Format(Me.Date, "yyyy\/mm\/dd") & "#," & Str(Me.Amount) & ");"

answered Jun 20, 2018 at 16:14

Gustav

56.5k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

cookiemonster Over a year ago

Thank you. I realized it later on.

Gustav Over a year ago

Or use my CSql function here.

Collectives™ on Stack Overflow

Debugging Access VBA Currentdb.Execute

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related