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 automatically, 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!!!

 

[DJS2]

Presumably the datasource for the products form is a grouping query based on the transactions table. With this type of application, I tend to have just one form open at a time so that, when you open the products form, it will recalculate. The other option would be to requery the form in the "on activate" event, but this is not as reliable.

To Summarise:

The Customers table holds all customer details with a unique ID for each customer

The Products table holds all product details with a unique ID for each product

The Transactions table lists all transactions, including product purchases

The datasource for the Products form is a grouping query that will add all transactions for any given product

If this is requeried each time that the page is activated, it will always show the current balance.