none
acess error message:"too few parameters. Expect 1." RRS feed

  • Question

  • Hi my friends:

    I am new in office access. I am developing a finished product inventory by using access 2013. I want to use a table to show the real time stock level of finished product. If the product is newly produced, it will create a row for this product otherwise the quantity of this product will add to the existed item.

    this is my code

    Private Sub Command92_Click()
    Dim RC As Integer
    RC = DCount("[Finished_Product]", "tbl_Finished_Product_Inventory", "[Finished_Product]= " & Me!cboFinishedProduct & " AND [LotNo]= '" & Me!cboFinishedProduct.Column(5) & " '")
    If RC = 0 Then
    CurrentDb.Execute " INSERT INTO tbl_Finished_Product_Inventory(Finished_Product, LotNo, Quarantined_Qty, Stock_Unit, LastUpdate) VALUES (" & Me!cboFinishedProduct & ", '" & Me!cboFinishedProduct.Column(5) & "', " & CStr(Me!txtLiberatedQty) & ",'" & Me!txtUnit & "', Now())"
    Else
    CurrentDb.Execute " UPDATE tbl_Finished_Product_Inventory SET Quarantined_Qty = Qurantined_Qty + " & CStr(Me!txtLiberatedQty) & ", LastUpdate = Now() WHERE Finished_Product = " & Me!cboFinishedProduct & " AND LotNo = '" & Me!txtLot & "' "
    End If
    MsgBox (" You have successfully updated ONE RECORD in tbl_Finished_Product_Inventory")
    End Sub

    If it is a product with new lot this code works well. But If is an existed lot, I got an error message saying "too few parameters? Expected 1" . Please help me and give me an ideal where I am wrong.

    Thanks

    Friday, May 12, 2017 4:01 AM

All replies

  • I would debug this as follows:

    If RC = 0 Then
     dim sql as string
     sql =
    " INSERT INTO tbl_Finished_Product_Inventory(Finished_Product, LotNo, Quarantined_Qty, Stock_Unit, LastUpdate) VALUES (" & Me!cboFinishedProduct & ", '" & Me!cboFinishedProduct.Column(5) & "', " & CStr(Me!txtLiberatedQty) & ",'" & Me!txtUnit & "', Now())"
    CurrentDb.Execute sql, dbFailOnError

    Then put a breakpoint on the Execute line and in the Immediate window:
    ?sql
    Do you see it?
    There are actually two errors: mismatched single quotes and datetime values need to be wrapped in #-signs.
    I also added dbFailOnError; it's exceedingly rare that you do not want a runtime error if an error occurs.


    -Tom. Microsoft Access MVP

    Friday, May 12, 2017 4:51 AM
  • Check your SQL statement for misspelled words. In your UPDATE query example, Qurantined_Qty looks like a possible culprit.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, May 12, 2017 1:13 PM