Sorry! Couldn't think of the right title.
I have a database for a "store". The main form contains the order information and the subform contains the order details including the products and the quantities "sold."
I have a "Complete Order" button that finishes up that order and clears to a new blank order. In addition I would like this form to update the data in two separate tables to reflect the change in the "Stock" (how many are in the store).
My initial attempt (which I should have known wouldn't work) was to run an update query to make the changes. The result (as should be predicted I suppose) is that the quantity that is deducted off is always the value of the quantity control in the bottom most record (the one I'm moving off of when I go to the button). I of course want the qty related to that specific product to be what is subtracted.
I believe that this is best possible using an ado recordset and doing some sort of for each looping type structure but I've never tried that before and have no idea where to begin.
I have included my SQL statement below because it indicates the relevant table fields and the relationships between those tables.
I have a database for a "store". The main form contains the order information and the subform contains the order details including the products and the quantities "sold."
I have a "Complete Order" button that finishes up that order and clears to a new blank order. In addition I would like this form to update the data in two separate tables to reflect the change in the "Stock" (how many are in the store).
My initial attempt (which I should have known wouldn't work) was to run an update query to make the changes. The result (as should be predicted I suppose) is that the quantity that is deducted off is always the value of the quantity control in the bottom most record (the one I'm moving off of when I go to the button). I of course want the qty related to that specific product to be what is subtracted.
I believe that this is best possible using an ado recordset and doing some sort of for each looping type structure but I've never tried that before and have no idea where to begin.
I have included my SQL statement below because it indicates the relevant table fields and the relationships between those tables.
Code:
strQP = "UPDATE tblProducts INNER JOIN tblOrderDetails ON tblProducts.ID =tblOrderDetails.Item" _
& " SET tblProducts.Stock=[stock]-(" & Me.subfrmOrderLines.Form!txtQty.Value & ")" _
& " WHERE ((tblOrderDetails.[Order#])=" & strWHERE & ")"
DoCmd.RunSQL strQP
