Results 1 to 5 of 5
  1. #1
    Vanz is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2021
    Posts
    12

    Syntax error 3134

    Hi all,



    I am using the code below to trigger the button on my form to save the data in these field onto my database. However, i encountered syntax error 3134 when clicking on the button. I am not sure where goes wrong. Seek your kind help. Thanks.

    Code:
    If Me.cbopcat = "Adult T-Shirt" Then
     CurrentDb.Execute "Insert into [Adult T-Shirt Instore](ReceivedDate,ProductDescription,Design,Size,Colour,ReceivedQuantity,RejectedQuantity,Currency,UnitPrice,TotalAmount,Remarks)" & _
     "Values ('" & Me.txtrcvdate & "','" & Me.cbopdes & "','" & Me.cbodesign & "','" & Me.cbosize & "','" & Me.cbocolor & "','" & Me.txtrcvquantity & "','" & Me.txtrejqty & "','" & Me.txtcurrency & "','" & Me.txtunitprice & "','" & Me.txtttlamount & "','" & Me.txtremarks & "')"
    ElseIf Me.cbopcat = "Kids T-Shirt" Then
      CurrentDb.Execute "Insert into [Kids T-Shirt Instore](ReceivedDate,ProductDescription,Design,Size,Colour,ReceivedQuantity,RejectedQuantity,Currency, UnitPrice,TotalAmount,Remarks)" & _
     "Values(" & Me.txtrcvdate & "','" & Me.cbopdes & "','" & Me.cbodesign & "','" & Me.cbosize & "','" & Me.cbocolor & "','" & Me.txtrcvquantity & "','" & Me.txtrejqty & "','" & Me.txtcurrency & "','" & Me.txtunitprice & "','" & Me.txtttlamount & "','" & txtremarks & "')"
    End If

  2. #2
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Size and Currency are reserved words and should not be used as field names.
    You might want to combine the 2 tables [Adult T-Shirt Instore] and [Kids T-Shirt Instore] and use a field to distinguish between the two. I'd also take the spaces out of the name as well as special characters.

    the first thing you should do to trouble shoot this is use debug.print to check the syntax of your insert string.
    It'll be easier to spot mistakes. You may need a space before values and you missed the first delimiter after the (
    Code:
    " Values('"
    I just reformatted your code below but didnt make any changes to the Sql.

    Code:
    Dim StrSql As String
    
    
        If Me.cbopcat = "Adult T-Shirt" Then
        
            StrSql = "Insert into [Adult T-Shirt Instore](ReceivedDate,ProductDescription,Design,Size,Colour,ReceivedQuantity,RejectedQuantity,Currency,UnitPrice,TotalAmount,Remarks)" & _
                              "Values ('" & Me.txtrcvdate & "','" & Me.cbopdes & "','" & Me.cbodesign & "','" & Me.cbosize & "','" & Me.cbocolor & "','" & Me.txtrcvquantity & "','" & Me.txtrejqty & "','" & Me.txtcurrency & "','" & Me.txtunitprice & "','" & Me.txtttlamount & "','" & Me.txtremarks & "')"
        
        ElseIf Me.cbopcat = "Kids T-Shirt" Then
        
            StrSql = "Insert into [Kids T-Shirt Instore](ReceivedDate,ProductDescription,Design,Size,Colour,ReceivedQuantity,RejectedQuantity,Currency, UnitPrice,TotalAmount,Remarks)" & _
                              "Values(" & Me.txtrcvdate & "','" & Me.cbopdes & "','" & Me.cbodesign & "','" & Me.cbosize & "','" & Me.cbocolor & "','" & Me.txtrcvquantity & "','" & Me.txtrejqty & "','" & Me.txtcurrency & "','" & Me.txtunitprice & "','" & Me.txtttlamount & "','" & txtremarks & "')"
        End If
        
        Debug.Print StrSql
        
        'CurrentDb.Execute stsql, dbFailOnError
    Does any of your data have apostrophes? That could throw off your text delimiters in your sql string.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    See red changes. Dates require '#' delimiters. When lines continue, be careful to include a space.
    Some other variables appear to be numeric. If so, no delimiter required.
    Setting the code to the sSQL variable allows you to debug.print the variable for inspection.

    Code:
    Dim sSQL as string
    If Me.cbopcat = "Adult T-Shirt" Then
     sSQL= "Insert into [Adult T-Shirt Instore] (ReceivedDate,ProductDescription,Design,Size,Colour,ReceivedQuantity,RejectedQuantity,Currency,UnitPrice,TotalAmount,Remarks) " & _
     "Values (#" & Me.txtrcvdate & "#,'" & Me.cbopdes & "','" & Me.cbodesign & "','" & Me.cbosize & "','" & Me.cbocolor & "','" & Me.txtrcvquantity & "','" & Me.txtrejqty & "','" & Me.txtcurrency & "','" & Me.txtunitprice & "','" & Me.txtttlamount & "','" & Me.txtremarks & "')"
    ElseIf Me.cbopcat = "Kids T-Shirt" Then
      sSQL= "Insert into [Kids T-Shirt Instore] (ReceivedDate,ProductDescription,Design,Size,Colour,ReceivedQuantity,RejectedQuantity,Currency, UnitPrice,TotalAmount,Remarks) " & _
     "Values (#" & Me.txtrcvdate & "#,'" & Me.cbopdes & "','" & Me.cbodesign & "','" & Me.cbosize & "','" & Me.cbocolor & "','" & Me.txtrcvquantity & "','" & Me.txtrejqty & "','" & Me.txtcurrency & "','" & Me.txtunitprice & "','" & Me.txtttlamount & "','" & Me.txtremarks & "')"
    End If
     Debug.Print sSQL
      Currentdb.Execute sSQL, dbFailOnError
    Last edited by davegri; 02-15-2021 at 11:45 AM. Reason: syntax

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    further comment - dates need to be in the format mm/dd/yyyy. If you don't use this format as a default not then a date such as 10/2/2021 will be interpreted as 2nd October, not 10th February

    Values (#" & format(txtrcvdate,"mm/dd/yyyy") & "#,......

  5. #5
    Vanz is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2021
    Posts
    12
    Dear All,

    thank you for the help! My error is resolved.

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

Similar Threads

  1. Replies: 4
    Last Post: 03-26-2019, 02:40 PM
  2. CurrentDb.Execute INSERT syntax error 3134
    By GraeagleBill in forum Programming
    Replies: 8
    Last Post: 12-19-2015, 09:45 PM
  3. runtime error 3134
    By TheLazyEngineer in forum Programming
    Replies: 2
    Last Post: 03-03-2015, 02:55 PM
  4. Syntax error 3134 for Add function
    By gaker10 in forum Programming
    Replies: 9
    Last Post: 06-16-2014, 12:06 PM
  5. Run-time Error '3134'
    By avitale in forum Access
    Replies: 2
    Last Post: 05-20-2012, 07:40 PM

Tags for this Thread

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