Results 1 to 14 of 14
  1. #1
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262

    Run time error 3144 Syntax error

    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.

    Code:
    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.

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,764
    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?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    ID is an autonumber whos only purpose is to maintain entry order.

  4. #4
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    Also, I am not totally familiar with SQL.

  5. #5
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,764
    Did you try the debugging method? It might highlight where the problem is.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    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.

  7. #7
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,764
    It might be helpful to future users if you post the corrected code.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    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.

  9. #9
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    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.

  10. #10
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    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

  11. #11
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    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.

  12. #12
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,764
    Ah, field names with the inadvisable spaces in them need to be bracketed:

    ", [Cage Code]='" & Me.txtCageCode & "'" & _
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    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!

  14. #14
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,764
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Error 3144: Syntax error in UPDATE statement??
    By Paintballlovr in forum Programming
    Replies: 7
    Last Post: 03-26-2014, 12:53 PM
  2. Replies: 3
    Last Post: 01-23-2014, 07:49 AM
  3. Replies: 6
    Last Post: 05-30-2012, 12:32 PM
  4. Syntax Error 3144 in SQL Update Query.
    By Phred in forum Programming
    Replies: 4
    Last Post: 03-02-2012, 02:39 PM
  5. Run Time Error 3144 for UPDATE
    By KrenzyRyan in forum Programming
    Replies: 12
    Last Post: 05-20-2011, 10:28 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums