[Resolved]Runtime Error 3134 - Syntax Error in INSERT INTO Statement (1 Viewer)

[syahrulf]

Hi,

i new in programming and just trying to make 1 access program following from youtube, to add, edit, search, but i got stuck on "add" step

my idea is using text box to input fileds i just created before i created table PR as follows :

PRnumber ->number
created ->date/time
desc->text
user->text
price -> currency
qty ->Number
PO->Number
Recv->Date/time

i use text box to insert to those fields,and added some command button to triger the command below is my VB script

Private Sub cmdAdd_Click() -> add command button
'add data to table
CurrentDb.Execute "INSERT INTO PR(PRNumber, Created, Desc, user, price, qty, vendor ,PO, Recv)" & _
" VALUES (" & Me.txtprnumber & ",'" & Me.txtprcreated & "','" & Me.txtdesc & "','" & Me.txtusr & "','" _
& Me.txtusr & "'," & Me.txtprice & "," & Me.txtqty & ",'" & Me.txtvendor & "'," & Me.txtpo & ",'" & Me.txtrcv & "')"

'clear form
cmdClear_Click
'refresh data in list on form
frmPRSub.Form.Requery

End Sub

Private Sub cmdClear_Click() -> clear command button
Me.txtprnumber = ""
Me.txtprcreated = ""
Me.txtdesc = ""
Me.txtusr = ""
Me.txtprice = ""
Me.txtqty = ""
Me.txtvendor = ""
Me.txtpo = ""
Me.txtrcv = ""

'focus on PR Number text box
Me.txtprnumber.SetFocus

End Sub

Private Sub cmdClose_Click() -> close command button
DoCmd.Close
End Sub


and everytime i click add it always shows Runtime "Error 3134 - Syntax Error in INSERT INTO Statement"
and click debug access is hilighting :

CurrentDb.Execute "INSERT INTO PR(PRNumber, Created, Desc, user, price, qty, vendor ,PO, Recv)" & _
" VALUES (" & Me.txtprnumber & ",'" & Me.txtprcreated & "','" & Me.txtdesc & "','" & Me.txtusr & "','" _
& Me.txtusr & "'," & Me.txtprice & "," & Me.txtqty & ",'" & Me.txtvendor & "'," & Me.txtpo & ",'" & Me.txtrcv & "')"

i don't know where is the issue, i will be vey thankfull for any enlightment

thanks
syahrul

 

[MajP]

If a field is text it needs to get ' added. 'sometext'

 

[arnelgp]

if you are sure that the field/textbox names are correct, try enclosing them in square([]) brackets.
the issue is that "Desc" is a reserved word in MSA.

Private Sub cmdAdd_Click()
    Dim SQL As String
    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    SQL = "INSERT INTO PR ([PRNumber], [Created], [Desc], [user], [price], [qty], [vendor] ,[PO], [Recv])" & _
          " SELECT P0, P1, P2, P3, P4, P5, P6, P7, P8;"
    Set db = Currentdb
    Set qd = db.CreateQueryDef("", SQL)
    With qd
        .Parameters(0) = Me.txtprnumber
        .Parameters(1) = Me.txtprcreated
        .Parameters(2) = Me.txtdesc
        .Parameters(3) = Me.txtusr
        .Parameters(4) = Me.txtprice
        .Parameters(5) = Me.txtqty
        .Parameters(6) = Me.txtvendor
        .Parameters(7) = Me.txtpo
        .Parameters(8) = Me.txtrcv

        .Execute
    End With
    Set qd = Nothing
    Set db = Nothing
End Sub

 

[namliam]

Please keep code readable, it is the Number 1! reason why you cannot debug your code !

CurrentDb.Execute "INSERT INTO PR(PRNumber, Created, Desc, user, price, qty, vendor ,PO, Recv)" & _
" VALUES (" & Me.txtprnumber & "" & _
       ",'" & Me.txtprcreated & "'" & _
       ",'" & Me.txtdesc & "'" & _
       ",'" & Me.txtusr & "'" & _
       ",'" & Me.txtusr & "'" & _
       ", " & Me.txtprice & "" & _
       ", " & Me.txtqty & "" & _
       ",'" & Me.txtvendor & "'" & _
       ", " & Me.txtpo & "" & _
       ",'" & Me.txtrcv & "')"

Much like strings being quoted with '' like you are doing.
You need to fix dates with ##

Also note the double usage of Me.txtusr ! Which makes your values have 9 columns instead of 8 for the table.

A more common way of inserting data like this is to use a recordset:

Dim rs as dao.recordset
Set rs = currentdb.openrecordset("yourtable")
rs.addnew
rs!PRNumber = Me.txtprnumber
rs.update
rs.close
set rs = nothing

This helps in preventing problems in
- Quotes and what not
- readabilty
- debugging

General tip; consider your column naming; Created is not really clear what is is, CreatedDate on the other hand much more so.
much like rcv and PO not clear
(re)Consider the use of (business) abbreviations like PO, likely PurchaseOrder much clearer.
Tables consider using a proper naming convention prefixing tables by tbl and queries qry or vw , forms frm, etc.

You do this in your form: frmPRSub
And in your commands cmdAdd_Click()
But you seem inconsistant in the application of it.

 

[Gasman]

Are you aware that you can just bind the form to the table/query and enter data directly.?

 

[syahrulf]

If a field is text it needs to get ' added. 'sometext'

i'm sorry, i don't understand, is it on code or on table ?

thanks

 

[syahrulf]

if you are sure that the field/textbox names are correct, try enclosing them in square([]) brackets.
the issue is that "Desc" is a reserved word in MSA.

Private Sub cmdAdd_Click()
    Dim SQL As String
    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    SQL = "INSERT INTO PR ([PRNumber], [Created], [Desc], [user], [price], [qty], [vendor] ,[PO], [Recv])" & _
          " SELECT P0, P1, P2, P3, P4, P5, P6, P7, P8;"
    Set db = Currentdb
    Set qd = db.CreateQueryDef("", SQL)
    With qd
        .Parameters(0) = Me.txtprnumber
        .Parameters(1) = Me.txtprcreated
        .Parameters(2) = Me.txtdesc
        .Parameters(3) = Me.txtusr
        .Parameters(4) = Me.txtprice
        .Parameters(5) = Me.txtqty
        .Parameters(6) = Me.txtvendor
        .Parameters(7) = Me.txtpo
        .Parameters(8) = Me.txtrcv

        .Execute
    End With
    Set qd = Nothing
    Set db = Nothing
End Sub

hi arnelgp,

it works with your code, thanks..i will continue with the search and edit

thanks again

 

[syahrulf]

Please keep code readable, it is the Number 1! reason why you cannot debug your code !

CurrentDb.Execute "INSERT INTO PR(PRNumber, Created, Desc, user, price, qty, vendor ,PO, Recv)" & _
" VALUES (" & Me.txtprnumber & "" & _
       ",'" & Me.txtprcreated & "'" & _
       ",'" & Me.txtdesc & "'" & _
       ",'" & Me.txtusr & "'" & _
       ",'" & Me.txtusr & "'" & _
       ", " & Me.txtprice & "" & _
       ", " & Me.txtqty & "" & _
       ",'" & Me.txtvendor & "'" & _
       ", " & Me.txtpo & "" & _
       ",'" & Me.txtrcv & "')"

Much like strings being quoted with '' like you are doing.
You need to fix dates with ##

Also note the double usage of Me.txtusr ! Which makes your values have 9 columns instead of 8 for the table.

A more common way of inserting data like this is to use a recordset:

Dim rs as dao.recordset
Set rs = currentdb.openrecordset("yourtable")
rs.addnew
rs!PRNumber = Me.txtprnumber
rs.update
rs.close
set rs = nothing

This helps in preventing problems in
- Quotes and what not
- readabilty
- debugging

General tip; consider your column naming; Created is not really clear what is is, CreatedDate on the other hand much more so.
much like rcv and PO not clear
(re)Consider the use of (business) abbreviations like PO, likely PurchaseOrder much clearer.
Tables consider using a proper naming convention prefixing tables by tbl and queries qry or vw , forms frm, etc.

You do this in your form: frmPRSub
And in your commands cmdAdd_Click()
But you seem inconsistant in the application of it.

Hi namliam,

Thanks for the advice and clue, i'm quite new in this..so this knowledge will very2 helpfull..

 

[syahrulf]

Are you aware that you can just bind the form to the table/query and enter data directly.?

hi,

i'm just folowing those code from youtube..