Enter parameter value

Question

I'm using MS Access 2007. I have a query called qryUPDATESTOCKS that reduces the stocks from table products based from the quantity from table order.

Query: UPDATE tblPRODUCTS SET tblPRODUCTS.Stock = tblPRODUCTS.Stock-tblORDER.Quantity WHERE tblORDER.ProductID=tblPRODUCTS.ProductID;

To run this query when an order is saved, I added an action OpenQuery into my save button that runs the query.

But when I click the save order button, a dialog box appears saying "Enter Parameter Value". How do I prevent it from popping up?

You need to do it in VBA, or if you want to do it in Query, you need to pass int the order Number and use a SubQuery to get the Number of items to take away. — PaulFrancis
@PaulFrancis i want to do it in query, how to use a SubQuery? — francisjessie
Based on the Logic, you will be changing the Stock value for all products with the same ID. There is something fishy about the code. Check the answer, see if that would help ! — PaulFrancis

PaulFrancis PaulFrancis · Accepted Answer · 2015-03-10T16:05:16

You need to run the Action Query in VBA, something like

Private Sub orderSaveButtonName_Click()
    Dim totAvailable As Long

    totAvailable = Nz(DSum("Stock", "tblPRODUCTS", "tblPRODUCTS.ProductID = '" & _
                            Me.ProductID & "'"), 0)

    CurrentDB.Execute "UPDATE tblProducts SET Stock = " & totAvailable - Me.Quantity & _
                      " WHERE tblPRODUCTS.ProductID = '" & Me.ProductID & "'"
End Sub

Or if you want to do it in Query, you need to pass the order Number and use a SubQuery to get the Number of items to take away. This could be complicated, using VBA is the straight forward solution.

Enter parameter value

1 Answers