Run time error 3144 Syntax error

[gaker10]

Title pretty much says it all. Here's the code:

Depending on whether I try to edit the main part or an alternate part, its the areas in red.

Private Sub butAdd_Click()
'Add entry to list


'Insert vs Update options


'If the part is not in the list
    If Me.ID.Tag & "" = "" Then


'add the part to the list
    
    'Add main part
    CurrentDb.Execute "INSERT INTO BOM(Assembly, Component, Description, AssemblyQty, UOM, CageCode, UnitPriceA, VendorA, UnitPriceB, VendorB, UnitPriceC, VendorC) " & _
    " VALUES ('" & Me.txtAssembly & "','" & Me.txtComponent & "','" & Me.txtDescription & "','" & Me.numAssemblyQty & "','" & Me.txtUOM & "','" & _
     Me.txtCageCode & "','" & Me.txtUnitPriceA & "','" & Me.txtVendorA & "','" & Me.txtUnitPriceB & "','" & Me.txtVendorB & "','" & Me.txtUnitPriceC & "','" & Me.txtVendorC & "')"
    
    
    'If there is an AlternateA, Add AlternateA
    If Not (Me.txtAlternateA & "" = "" And Me.txtCageCodeA & "" = "") Then
    CurrentDb.Execute "INSERT INTO BOM(Component, Description, UOM, CageCode) " & _
    " VALUES ('" & Me.txtAlternateA & "','" & Me.txtAltADescription & " \ALT" & "','" & Me.txtAltAUOM & "','" & Me.txtCageCodeA & "')"
    End If
    
    'If there is an AlternateB, Add AlternateB
    If Not (Me.txtAlternateB & "" = "" And Me.txtCageCodeB & "" = "") Then
    CurrentDb.Execute "INSERT INTO BOM(Component, Description, UOM, CageCode) " & _
    " VALUES ('" & Me.txtAlternateB & "','" & Me.txtAltBDescription & " \ALT" & "','" & Me.txtAltBUOM & "','" & Me.txtCageCodeB & "')"
    End If
    
'update an already existing part


   ElseIf InStr(Me.txtDescription, " \ALT") > 0 Then
   
        CurrentDb.Execute "UPDATE BOM " & _
        " SET Component='" & Me.txtComponent & "'" & _
        ", Description='" & Me.txtDescription & "'" & _
        ", UOM='" & Me.txtUOM & "'" & _
        ", Cage Code='" & Me.txtCageCode & "'" & _
        " WHERE ID=" & Me.frmEntrySub.Form.ID

        Else

    CurrentDb.Execute "UPDATE BOM " & _
        " SET Assembly='" & Me.txtAssembly & "'" & _
        ", Component='" & Me.txtComponent & "'" & _
        ", Description='" & Me.txtDescription & "'" & _
        ", AssemblyQty='" & Me.numAssemblyQty & "'" & _
        ", UOM='" & Me.txtUOM & "'" & _
        ", Cage Code='" & Me.txtCageCode & "'" & _
        ", UnitPriceA='" & Me.txtUnitPriceA & "'" & _
        ", VendorA='" & Me.txtVendorA & "'" & _
        ", UnitPriceB='" & Me.txtUnitPriceB & "'" & _
        ", VendorB='" & Me.txtVendorB & "'" & _
        ", UnitPriceC='" & Me.txtUnitPriceC & "'" & _
        ", VendorC='" & Me.txtVendorC & "'" & _
        " WHERE ID=" & Me.frmEntrySub.Form.ID
            
            End If
    
'Clear form after add/update
    butClear_Click


'Refresh form
    frmEntrySub.Form.Requery


End Sub

Areas in blue are where I think the error is occurring, but I'm not sure.




Something interesting is when I try to reset my ID count by Compacting and Repairing, it does not reset anymore.

[pbaldy]

I'd use a variable and use this method to see how the finished SQL is coming out:

http://www.baldyweb.com/ImmediateWindow.htm

What is the data type of that field?

[gaker10]

ID is an autonumber whos only purpose is to maintain entry order.

[gaker10]

Also, I am not totally familiar with SQL.

[pbaldy]

Did you try the debugging method? It might highlight where the problem is.

[gaker10]

I got the SQL statement figured out. Everything is showing up where it's supposed to.

Yes, I always debug. The red passages are the ones that are highlighted in the code above.

[pbaldy]

It might be helpful to future users if you post the corrected code.

[gaker10]

I haven't corrected any code. I don't know how to correct it. I tried several ways of referencing the same thing and I get the same error.

[gaker10]

As an amendment to post #6, I understand the SQL debugging statement, and according to this debugging method, all of my fields have the correct information, but I am STILL getting the same runtime error.

[John_G]

Hi -

Two things -

1) Are you sure that Me.frmEntrySub.Form.ID actually has a value when you execute the statements?

2) Might any of your text fields contain an apostrophe ( = single quote), e.g. Rick's Supply ?

Either one of those will result in a syntax error.

John

[gaker10]

1) Yes it does have a value via this SQL statement:

UPDATE BOM SET Assembly='', Component='TMS-3/32-N021-9', Description='MARKER, REF: 10503373 \ALT', AssemblyQty='2', UOM='EA', Cage Code='13254', UnitPriceA='', VendorA='', UnitPriceB='', VendorB='', UnitPriceC='', VendorC='' WHERE ID=6

2) When I imported our database into access, I replaced all single quotes for this very reason. So, no, none of my text fields contain an apostrophe.

[pbaldy]

Ah, field names with the inadvisable spaces in them need to be bracketed:

", [Cage Code]='" & Me.txtCageCode & "'" & _

[gaker10]

Oh geez :\ Your post made me realize that my code and my table field names don't match. I forgot I changed my table field name to CageCode because of the space thing. It was still Cage Code in my code >_< Thank you!

[pbaldy]

Happy to help!