locked
Run-time error 3134, Syntax error INSERT INTO Statement RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
     CurrentDb.Execute "INSERT INTO tbl_RMU-GRN(stockcode, datereceived, RequestOrderNumber, materialname, uom, type, batchorlot, CSB, QTYReceived, QTYPerUOM, QTY, ipfcode, receivedby, department, MFD, expireddate, imagepath, supplier, Original, remarks)" & _
                "VALUES ('" & Me.txtcode & "','" & Me.txtreqno & "','" & Me.txtreqno & "','" & Me.txtmaterialname & "','" & Me.txtuom & "','" & Me.txttype & "','" & Me.txtbatch & "','" & Me.txtCSB & "','" & Me.txttotalqty & "','" & Me.txtqtyperuom & "','" & Me.txtqty & "','" & Me.txtIPFcode & "','" & Me.txtreceivedby & "','" & Me.txtdept & "','" & Me.txtmfd & "','" & Me.txtExpiredDate & "','" & Me.txtimagepath & "','" & Me.txtsupplier & "','" & Me.txtoriginal & "','" & Me.txtremarks & "')"
               
    Friday, February 3, 2017 9:43 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Your SQL statement has Me.txtreqno twice. The first occurrence is incorrect, it should provide the value for datereceived. And date values should be enclosed in #, not in '. For example:

    CurrentDb.Execute "INSERT INTO tbl_RMU-GRN(stockcode, datereceived, RequestOrderNumber, materialname, uom, type, batchorlot, CSB, QTYReceived, QTYPerUOM, QTY, ipfcode, receivedby, department, MFD, expireddate, imagepath, supplier, Original, remarks)" & _
                 "VALUES ('" & Me.txtcode & "',#" & Format(Me.txtDateReceived, "mm/dd/yyyy"( & "#,'" & Me.txtreqno & "','" & Me.txtmaterialname & "','" & Me.txtuom & "','" & Me.txttype & "','" & Me.txtbatch & "','" & Me.txtCSB & "','" & Me.txttotalqty & "','" & Me.txtqtyperuom & "','" & Me.txtqty & "','" & Me.txtIPFcode & "','" & Me.txtreceivedby & "','" & Me.txtdept & "','" & Me.txtmfd & "',#" & Format(Me.txtExpiredDate, "mm/dd/yyyy") & "#,'" & Me.txtimagepath & "','" & Me.txtsupplier & "','" & Me.txtoriginal & "','" & Me.txtremarks & "')"

    where txtDateReceived is the text box containing the value for datereceived.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, February 3, 2017 9:57 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I would highly recommend you use a QueryDef and Parameters to avoid these types of syntax issues.

    https://msdn.microsoft.com/en-us/library/office/ff193967.aspx

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, February 3, 2017 2:56 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    I would also recommend using a string variable to help you with troubleshooting. For example:

    Dim strSQL As String

    strSQL = "SELECT blah blah blah..."

    CurrentDb.Execute strSQL, dbFailOnError

    If you get an error, you can simply insert Debug.Print strSQL to examine what exactly is being executed and failing.

    Just my 2 cents...

    Friday, February 3, 2017 3:37 PM