Keeping currentQuantity up to date

[wj8801]

I have two fields currentQuantity and orderQuantity.

currentQuantity holds the current quantity for a particular item

orderQuantity holds the quantity for an order about to be taken



What I'm having trouble with is decrementing the current quantity after an order is made I think I need to do a dynamic SQL statement but I'm not sure how, any help would be appreciated.



Thanks






Jase

[OBP]

Hello Jase, the easiest way is to just use some simple VBA in the form where you enter the Order Quantity.
Put something like this in the Order Quantity's After Update Event Procedure
me.currentQuantity = me.currentQuantity - me.orderQuantity

[wj8801]

ah i did that with the me.

thanks

[mattj]

You should not store the current qty in the database - especially not if your tracking inventory levels. It is a calculated value and should be re-calc'd as necessary. Inventory is one of the trickier things to do in Access - take a look at the Northwinds database that is installed with Access....

[OBP]

Sorry to disagree mattj, but I have just looked at the Northwind Database and it does store Stock Levels in the Table.

[mattj]

Does it really?? cranial flatulence popping up today... I guess I should have looked again before I posted...

Either way, when maintaining inventory levels, I believe that a more reliable manner of tracking on hand would be to simply calculate it from quantities that have been either added or subtracted. By maintaing an "On Hand" qty, you must perform an additional calc to update the field. Also, it requires that you store a calculated field, which is only necessary (IMHO) in very rare cases, most often when the variables used to calculate the field will be changing and a historical record is required.
For an inventory db, storing the on hand qty is not necessary.

See here: http://allenbrowne.com/AppInventory.html

Just my 2?

Matt