HI,
I have an inventory table with a on hand field and an orders table with quantity ordered. What I would like to happen on the form (product table) is when the subform (orders table) is completed, the field with the on hand will be updated by a calculation that minus's the quantity ordered from the on hand field. Is this at all possible.
Geno

You should Not store the quantity on hand by way of a calculation only as the result of a stock take. Use a query or just use the calculated control you already have to display the quantity.

If you're talking about a transaction that affects stock, it is quite valid to update a stock-on-hand quantity. I certainly wouldn't want to keep calculating the current stock position from [Opening stock]+/-[All transactions keyed since] indefinitely, as it will get very slow once you have a few hundred thousand transaction records.

You should be able to do this with an update query, joining your products table to your keyed transaction table by ProductID (or something like this) and updating stock on hand to stock on hand minus (or plus) keyed quantity from the keyed transaction table.

You need to make sure that the process can't be run twice, or this would result in the stock being adjusted twice, the easiest way I've found to achieve this is, in the onclick event of the 'apply transaction' button,immediately move the focus to another control, set the enabled property of the button to false, run the update query, clear out the temporary transaction table, re-enable the 'apply' button.

Hope this helps

Mike

You don't have to keep updating stock on hand from the begining of the table only from the last stock take, how do you account for or even detect shrinkage by storing the calculated value?

Hmmm

I'm not sure if we're talking about the same things here;

I have a table of products, each product has a field containing the units on hand value, when I sell one, the program subtracts 1, when I book one in, it adds 1.

I detect shrinkage by counting the stock and comparing against the units on hand figure, when I close the stocktake(when I'm happy with it), the system applies the differences between the counted and notional stock as adjustments, effectively making the units on hand match the counted stock.

I write a journal record every time a transaction changes the stock.

I realise it's not the only approach, perhaps not the best either, but it does mean that I have my current onhand stock units stored as a value in a table for quick and easy access. I prefer this method over the Fixed-historical-point plus/minus transactions approach.

Mike

[This message has been edited by Mike Gurman (edited 03-14-2001).]