jiangzhou He jiangzhou He - 5 months ago 8
SQL Question

Insert into syntax error in access

I am trying to add textbox information to a table by sql insert into statement. Unfortunately, I received an syntax error message. Please help me check the format.
My code is:

Private Sub cmdSaveRecord_Click()
Dim StrSql As String
StrSql = "Insert Into test2 (PurchaseDate, SupplierCompany, PurchaseItem, Unit, PurchaseQuantity, UnitCost, ExtendedPrice)" & _
" VALUES(#" & Format(Me!txtOrderDate, "yyyy/mm/dd") & "#, '" & Me!cboSupplierCompany & "', '" & Me!cboPurchaseItem1 & "', '" & Me!txtUnit1 & "'," & CStr(Me!txtQty1) & ", ," & CStr(Me!TxtPrice1) & ", " & CStr(Me!TxtTotal1) & " )"
CurrentDb.Execute (StrSql)
MsgBox " You have successfuly add one record to PurchaseOrderDetail table."


End Sub

Answer

You have a empty column in values

  "Insert Into test2 (
    PurchaseDate
    , SupplierCompany
    , PurchaseItem
    , Unit
    , PurchaseQuantity
    , UnitCost
    , ExtendedPrice
    )" & _
      " VALUES(
      #" & Format(Me!txtOrderDate, "yyyy/mm/dd") & "#
      , '" & Me!cboSupplierCompany & "'
      , '" & Me!cboPurchaseItem1 & "'
      , '" & Me!txtUnit1 & "'
      ," & CStr(Me!txtQty1) & "
      ,                       <----- here
      ," & CStr(Me!TxtPrice1) & "
      , " & CStr(Me!TxtTotal1) & " )"

remove it

  "Insert Into test2 (
    PurchaseDate
    , SupplierCompany
    , PurchaseItem
    , Unit
    , PurchaseQuantity
    , UnitCost
    , ExtendedPrice
    )" & _
      " VALUES(
      #" & Format(Me!txtOrderDate, "yyyy/mm/dd") & "#
      , '" & Me!cboSupplierCompany & "'
      , '" & Me!cboPurchaseItem1 & "'
      , '" & Me!txtUnit1 & "'
      ," & CStr(Me!txtQty1) & "
      ," & CStr(Me!TxtPrice1) & "
      , " & CStr(Me!TxtTotal1) & " )"