I need some help updating a field each time an order is placed in my database. We will have six people taking orders on about 100 products which have codes such as A6000. I have created my database called Database9. I have two tables which are: Product which holds the product codes with descriptions and prices along with starting available for each product.
When a product is entered and sold I need for the available balance remaining on each product to update. This updated available balance needs to be used for each product when another order is placed on a product.
For example: I have A6000 with 1000 units available for sale. We sale 10 units so the new available balance to use for the next order placed on the product will be 990.
I have created an Update Query, but how can I develop an input form to take orders with what I got created. I need for the two tables: Product and product Orders to link with Code as being a Primary Key. I want the user to just enter say A6000 and the product description will come in automatically. I have created this query, called ProductOrdersRec.
Now,I can't use drop down boxes or lists becuase there will be too many codes and descriptions. In fact over 100 products. I need an input form which will allow the user to just enter Code and Qty. The Description of the product should come as should price. The Total which is QTY*Price must be calculated. Also, QTYBalance should calculate too which is Starting Available Balance - Qty each time. If I use a query as a subform for Input, All of the fields can be made and created, but when the update query runs each time, the available balance gets off and is incorrect. So when another sale is made on the product, I have an incorrect starting balance available for sale.
My update query will update the product table with the available balance to start with. I want to use the ProductOrdersRec as a subform and enter orders directly in this subform, but when I do enter products and run the Update query, the available balance is right until I open the form again, when I do the QTYBalance ends up with an incorrect balance available for use next time when an order is placed on the code becuase the query is running each time and the update occurs more than once giving incorrect results.
Any suggestions will be very helpful.
I am very close. Please find my database9 attached.