Ms access 2010 insert into syntax error

[accessTenderfoot]

I'm practically starting to use MS Access and simply copied a code from the internet. I think I've checked it thoroughly but still getting syntax error message when the command is ran. Below is the code I have.

CurrentDb.Execute "INSERT INTO tblAql(area, woNo, pn, qtyRec, qtyAcc, qtyRej, source, optr, insp, dmrNo, aqlPct, def1, def2, def3, machine, comment, dateTime, noSmpls, dispo) " & _
"VALUES(" & Me.cboArea & ",'" & Me.txtWO & "','" & Me.txtPN & "','" & Me.intRec & "','" & Me.intAcc & "','" & _
Me.intRej & "','" & Me.cboSource & "','" & Me.txtOptr & "','" & Me.txtInsp & "','" & Me.txtDMR & "','" & Me.cboAqlPct & "','" & _
Me.cboDef1 & "','" & Me.cboDef2 & "','" & Me.cboDef3 & "','" & Me.txtMachine & "','" & Me.txtComment & "','" & nowDate & "','" & Me.cboDispo & "')"




Can anyone help me out?

Thanks.


accessTenderfoot

[June7]

The number of fields and values don't match.

Date fields require # delimiter instead of apostrophe. Number fields do not require delimiter.

[accessTenderfoot]

Thanks for the quick response Moderator. You are right, I was missing another field. As for the date field (nowDate), do I still have to put # though it's a variable? Below is the complete code for the command buttons.

Option Compare Database
Private Sub cmdAdd_Click()
nowDate = Now()
CurrentDb.Execute "INSERT INTO tblAql(area, woNo, pn, qtyRec, qtyAcc, qtyRej, source, optr, insp, dmrNo, aqlPct, def1, def2, def3, machine, comment, dateTime, noSmpls, dispo) " & _
"VALUES(" & Me.cboArea & ",'" & Me.txtWO & "','" & Me.txtPN & "','" & Me.intRec & "','" & Me.intAcc & "','" & _
Me.intRej & "','" & Me.cboSource & "','" & Me.txtOptr & "','" & Me.txtInsp & "','" & Me.txtDMR & "','" & Me.cboAqlPct & "','" & _
Me.cboDef1 & "','" & Me.cboDef2 & "','" & Me.cboDef3 & "','" & Me.txtMachine & "','" & Me.txtComment & "','" & nowDate & "','" & Me.intNoSamples & "','" & Me.cboDispo & "')"


'cmdClear_Click
frmAQLsub.Form.Requery
End Sub
Private Sub cmdClose_Click()
DoCmd.Close
End Sub

[June7]

Try with the # but definitely remove the apostrophe.

Don't really need that variable, just use Now() in the concatenation.

So why do you need to use INSERT to create record? Why not a bound form?

[orange]

See the materials here and get some understanding of database concepts.

[accessTenderfoot]

I hate to admit it but I'm confused with this single apostrophe and double apostrophes; not really sure which is being enclosed by which apostrophe. Would you mind marking up this code to where the # goes? Thanks.

CurrentDb.Execute "INSERT INTO tblAql(area, woNo, pn, qtyRec, qtyAcc, qtyRej, source, optr, insp, dmrNo, aqlPct, def1, def2, def3, machine, comment, dateTime, noSmpls, dispo) " & _
"VALUES(" & Me.cboArea & ",'" & Me.txtWO & "','" & Me.txtPN & "','" & Me.intRec & "','" & Me.intAcc & "','" & _
Me.intRej & "','" & Me.cboSource & "','" & Me.txtOptr & "','" & Me.txtInsp & "','" & Me.txtDMR & "','" & Me.cboAqlPct & "','" & _
Me.cboDef1 & "','" & Me.cboDef2 & "','" & Me.cboDef3 & "','" & Me.txtMachine & "','" & Me.txtComment & ",#" & Now() & "#," & Me.intNoSamples & "','" & Me.cboDispo & "')"

[June7]

Missing the second apostrophe for Me.txtComment.

"','" & Me.txtComment & "',#"

Want to answer my questions from earlier post?

[accessTenderfoot]

Sorry, didn't see the questions.

I'm trying to make a database wherein several people (at different computer stations) would be using so I'm trying to make a simple custom-made form where users simply enter the data and click on the ADD command button.

[June7]

But if form is bound to table and controls bound to fields, data entered into controls would pass directly to table and no code would be needed. This is the appeal of Access - it does the heavy lifting for you.

[accessTenderfoot]

Still getting syntax error message...

CurrentDb.Execute "INSERT INTO tblAql(area, woNo, pn, qtyRec, qtyAcc, qtyRej, source, optr, insp, dmrNo, aqlPct, def1, def2, def3, machine, comment, dateTime, noSmpls, dispo) " & _
"VALUES(" & Me.cboArea & ",'" & Me.txtWO & "','" & Me.txtPN & "','" & Me.intRec & "','" & Me.intAcc & "','" & _
Me.intRej & "','" & Me.cboSource & "','" & Me.txtOptr & "','" & Me.txtInsp & "','" & Me.txtDMR & "','" & Me.cboAqlPct & "','" & _
Me.cboDef1 & "','" & Me.cboDef2 & "','" & Me.cboDef3 & "','" & Me.txtMachine & "','" & Me.txtComment & "',#" & Now() & "#," & Me.intNoSamples & "','" & Me.cboDispo & "')"

[June7]

What error message?

If intRec, intAcc, intRej pass numbers to number type fields - as stated, number fields don't use delimiters.

Did you read my previous post?

I still don't understand why you are using this approach.

[accessTenderfoot]

Ok, I think I'll try it. Thanks again for all the help.

[accessTenderfoot]

Thanks Orange for the references.

[Micron]

Whatever the reason for doing what you're doing, I don't agree with this: #" & Now() & "#.
In my not always enlightened opinion, Now() and Date() are functions that return dates, so date delimiters are not only not required, they can cause the error. If you create a query and put < Date() or <Now() in the design grid and look at the sql, Access does not put delimiters around the functions and the query will run (assuming all else is OK).

One thing you can do is, with the form open as normal, put a debug.print line right after the last line that builds your sql string, copy it from the immediate window and dump it into the sql view of a new query. Change all ocurrences of Me to an explicit form reference (Forms!yourFormName), and set the query view to datasheet view. If it balks, it might highlight the offending piece of the sql.