Update Stock Level in Transaction Form

[PhotoFrang]

Hello,
I 've been trying a lot to find a solution to my problem, but I am always in a dead end. Here's my problem.

I have three tables, and each one has its form. I have a customers, a products table and a transactions table. In the transactions table, is shown all the orders of each customer.
My customer table doesn't play any part in my question, so just ignored it. In the products, I have there name, which is unique, their barcode, stock level, reorder level etc. In the transaction form, I get the customers, and the products they order. Each customer can order up to 4 different products each time. So I have a text box for each of the four products, which is there name. I also have for each product a text of the ordered quantity and the price.

The problem is that I can't update the stock level of each product automaticaly, when a transaction is done. So what I want, is to identify each of the 4 products from the transaction form, and update the stock level in the products form, by subtracting form the stock level of the products the quantity that the customer has bought.

I don't know what to do, and I wish for your help, I hope you will be able to find an answer to my problem.

Thanks for your help!!!

 

[AccessAce]

I would suggest putting a button on your form that finishes the transaction. When that button is pushed, have some code check each of your product boxes. If the product box is not null, meaning a product has been selected, then create a SQL statement that will UPDATE your products table.

strSQL = "UPDATE Products SET Qty = [Qty] - " & _
me.txtQtyOrdered & " " & _
"WHERE ProductName = '" & me.cmbProdName & "'"
docmd.runSQL strSQL

This should decrement the quantity like you want.