Syntax error 3134 for Add function

[gaker10]

Getting "Run-time error '3134': Syntax error in INSERT INTO statement":

Private Sub butAdd_Click()


'Insert vs Update options


    If Me.txtComponent.Tag & "" = "" Then


'add, clear form, and refresh
    
    CurrentDb.Execute "INSERT INTO EntryFormTable(Assembly, Component, Description, AssemblyQty, UOM, QtyA, QtyB, QtyC, PriceA, PriceB, PriceC) " & _
    " VALUES('" & Me.txtAssembly & "','" & Me.txtComponent & "','" & Me.txtDescription & "'," & Me.numAssemblyQty & ",'" & Me.txtUOM & "'," & Me.numQtyA & "," & _
    Me.numQtyB & "," & Me.numQtyC & "," & Me.dbPriceA & "," & Me.dbPriceB & "," & Me.dbPriceC & ")"

    Else
    CurrentDb.Execute "UPDATE EntryFormTable " & _
        " SET Assembly='" & Me.txtAssembly & "'" & _
        ", Component='" & Me.txtComponent & "'" & _
        ", Description='" & Me.txtDescription & "'" & _
        ", AssemblyQty=" & Me.numAssemblyQty & "" & _
        ", UOM='" & Me.txtUOM & "'" & _
        ", QtyA=" & Me.numQtyA & "" & _
        ", QtyB=" & Me.numQtyB & "" & _
        ", QtyC=" & Me.numQtyC & "" & _
        ", PriceA=" & Me.dbPriceA & "" & _
        ", PriceB=" & Me.dbPriceB & "" & _
        ", PriceC=" & Me.dbPriceC & "" & _
        " WHERE Component='" & Me.frmEntrySub!Component & "'"
    End If
    
    butClear_Click
    
    frmEntrySub.Form.Requery


End Sub

[rpeare]

txtAssembly, txtComponent, txtDescription, txtUOM are text fields

Everything else is a number field?


Have you tried composing the SQL string separately then using the debug.print statement to see where the error may be , for instance there is no space between VALUES and the opening bracket of the values VALUES( instead of VALUES ( I am not sure that would make a difference for that one item but I know spaces or lack thereof in SQL strings can be troublesome.

try something like

dim sSQL
ssql = <build your ssql string here>
debug.print ssql

Then cut open your immediate window and cut and paste the SQL string to a query builder window, then try to run it, you will likely get far more useful information about what exactly is going wrong. (For instance trying to insert a text value into a number field).

[pbaldy]

A visual of what rpeare is suggesting:

BaldyWeb-Immediate window

[gaker10]

Inserting the space between "VALUES" and "(" still caused the same error.

I apologize, but I am unfamiliar with that kind of syntax :\ I just noticed though that when I don't enter data into my Qty and Price fields is when I get the error. If I enter numbers into all of them, it adds just fine.

Realistically, though, numbers will not always be in these fields for every entry. Should I add text delimiters to accept a non-entry or blank space?

[rpeare]

dim sSQL

ssql = "INSERT INTO EntryFormTable(Assembly, Component, Description, AssemblyQty, UOM, QtyA, QtyB, QtyC, PriceA, PriceB, PriceC) VALUES ("
ssql = ssql & "'" & Me.txtAssembly & "',"
ssql = ssql & "'" & Me.txtComponent & "',"
ssql = ssql & "'" & Me.txtDescription & "',"
if(isnull(Me.numAssemblyQty) then
ssql = ssql & "null,"
else
ssql = ssql & me.numassemblyqty & ","
endif

and so on
then use db.execute (ssql) in

[gaker10]

I'm not too familiar with sql so I tried to use the simplest solution possible. So, I added single quotes around my number fields to accept blank spaces. As of right now, it works without issue.

Private Sub butAdd_Click()


'Insert vs Update options


    If Me.txtComponent.Tag & "" = "" Then


'add, clear form, and refresh
    
    CurrentDb.Execute "INSERT INTO EntryFormTable(Assembly, Component, Description, AssemblyQty, UOM, QtyA, QtyB, QtyC, PriceA, PriceB, PriceC) " & _
    " VALUES ('" & Me.txtAssembly & "','" & Me.txtComponent & "','" & Me.txtDescription & "','" & Me.numAssemblyQty & "','" & Me.txtUOM & "','" & Me.numQtyA & "','" & _
    Me.numQtyB & "','" & Me.numQtyC & "','" & Me.dbPriceA & "','" & Me.dbPriceB & "','" & Me.dbPriceC & "')"

    Else
    CurrentDb.Execute "UPDATE EntryFormTable " & _
        " SET Assembly='" & Me.txtAssembly & "'" & _
        ", Component='" & Me.txtComponent & "'" & _
        ", Description='" & Me.txtDescription & "'" & _
        ", AssemblyQty='" & Me.numAssemblyQty & "'" & _
        ", UOM='" & Me.txtUOM & "'" & _
        ", QtyA='" & Me.numQtyA & "'" & _
        ", QtyB='" & Me.numQtyB & "'" & _
        ", QtyC='" & Me.numQtyC & "'" & _
        ", PriceA='" & Me.dbPriceA & "'" & _
        ", PriceB='" & Me.dbPriceB & "'" & _
        ", PriceC='" & Me.dbPriceC & "'" & _
        " WHERE Component='" & Me.frmEntrySub!Component & "'"
    End If
    
    butClear_Click
    
    frmEntrySub.Form.Requery


End Sub

[gaker10]

I don't know what I did, but I was testing data entry and all of a sudden my entries are added to the top of my subform. I need them added to the bottom. What did I press? lol

[gaker10]

I don't know what I did, but I was testing data entry and all of a sudden my entries are added to the top of my subform. I need them added to the bottom. What did I press? lol

So I closed MS access after saving my test data, and now it adds entries to the bottom after re-opening. No idea what happened.

[barbarareynolds]

Hi, I am trying to add a record to the table and am getting Runtime error 3134 – Syntax Error INSERT INTO Statement Here are my fields datatypes

Onilne Casino australia

[pbaldy]

I think we've looped back to posts 2 and 3 (and you didn't include the fields and data types).