Inventory Control (add,subtract,mult)

[Susan Allen Wyoming]

I am trying to build an inventory control database for our family business. Thus far it is going pretty well, but I'm stuck on some of the calculations. I was wondering if anybody had any insight regarding the following.

Summary: Trying to calculate the units on order for each order by supplier and the total number of units for each product

Field Names:

OrderQty = the number of cases on Order
SuppCQ = the number of units in each case
OrderQOO = OrderQty x SuppCQ (OrderQOO stands for Quantity on Order and refers to each line item in a Purchase Order)
ProdQOO = the running total for any OrderQOO from any Purchase Order that has the same Product ID

How I want it to work:

Purchase Order A
ProdID OrderQty SuppCQ OrderQOO ProdQOO
abc 1 12 12 12

Purchase OrderB
ProdID OrderQty SuppCQ OrderQOO ProdQOO
abc 2 12 24 36

Code Written:

Private Sub OrderQty_AfterUpdate()
Me!OrderQOO = Me!OrderQty * Me!SuppCQ
Me!ProdQOO = Me!OrderQOO + Me!ProdQOO

Private Sub OrderQty_Change()
Me!OrderQOO = (Me!OrderQty * Me!SuppCQ) - Me!OrderQOO.OldValue

Problem:
If I go back to a purchase order and change the OrderQty from a 2 to a 1, the OrderQOO is updating fine, but the ProdQOO just keeps adding and adding and adding.

I need the ProdQOO to keep a running total for all the units on order for any given product, but I also want it to subtract the old values from OrderQOO if I make a change to the purchase Order.

Any help would be much appreciated.

Susan Allen
Cheyenne, Wyoming

 

[Chris RR]

Well, I have an idea, but you won't like it (no one does the first time they here this one...)

Don't store any running totals. Ever.

Totals are something that you calculate. Even if you calculate them often, that's what computers are good at. If you recalculate the total each time you need to see it (on a form, in a query or on a report) it will always be correct.

If you store a total, you have a chance that somewhere, somehow, the records will change and the total will be incorrect.

It's less work and more accurate to recalculate your OrderQ00 and ProdQ00 each time you need them, than to try to maintain them properly.