Hi all,
My goal is to update my product table to show the new stock levels after an item or items have been ordered.
Currently I have an order form, which includes a sub form where the user can select the products that he/she wants to order. The subform is in datasheet view.
Back to the order form. Here I have a button 'process order'. When a user clicks on this i would like the quantity(s) of the products ordered to be subtracted. I was previously doing this using an after update event on the sub form using the follwing code:
'Declare variable to hold SQL string
Dim mySQL As String
'Update the product stock levels in the stock table
mySQL = "UPDATE ProductTBL SET ProductStock = [ProductStock]-Forms!OrderFRM!OrderProductSUBFRM.Form!QuantityOrdered WHERE (((ProductTBL.ProductID)=Forms!OrderFRM!OrderProductSUBFRM.Form!ProductID))"
'Turn the on-screen SQL warning off
DoCmd.SetWarnings False
'Execute the above SQL statement
DoCmd.RunSQL mySQL
'Turn the warning back on
DoCmd.SetWarnings True
This worked fine, but is not an ideal solution. Eg, if the user types in a quantity the after update event will kick in and delete the quantity from the remaining stock. If the user realises that this is an incorrect qty and types a new qty in, yet more stock will be deducted. Not good.
So, you can see why the stock update needs to be one of the last things that is done, IE on the process order button.
This is where my problem lies. I'd imagine that i now need some sort of loop to go through each product ordered and subtract the number of items deleted from the remaining stock. The only problem is, i am no good with loops and dont know where to start.
So, any help would be greatly appreciated here.
Regards and thanks in advance.
My goal is to update my product table to show the new stock levels after an item or items have been ordered.
Currently I have an order form, which includes a sub form where the user can select the products that he/she wants to order. The subform is in datasheet view.
Back to the order form. Here I have a button 'process order'. When a user clicks on this i would like the quantity(s) of the products ordered to be subtracted. I was previously doing this using an after update event on the sub form using the follwing code:
'Declare variable to hold SQL string
Dim mySQL As String
'Update the product stock levels in the stock table
mySQL = "UPDATE ProductTBL SET ProductStock = [ProductStock]-Forms!OrderFRM!OrderProductSUBFRM.Form!QuantityOrdered WHERE (((ProductTBL.ProductID)=Forms!OrderFRM!OrderProductSUBFRM.Form!ProductID))"
'Turn the on-screen SQL warning off
DoCmd.SetWarnings False
'Execute the above SQL statement
DoCmd.RunSQL mySQL
'Turn the warning back on
DoCmd.SetWarnings True
This worked fine, but is not an ideal solution. Eg, if the user types in a quantity the after update event will kick in and delete the quantity from the remaining stock. If the user realises that this is an incorrect qty and types a new qty in, yet more stock will be deducted. Not good.
So, you can see why the stock update needs to be one of the last things that is done, IE on the process order button.
This is where my problem lies. I'd imagine that i now need some sort of loop to go through each product ordered and subtract the number of items deleted from the remaining stock. The only problem is, i am no good with loops and dont know where to start.
So, any help would be greatly appreciated here.
Regards and thanks in advance.
