I have a example query and report that shows how to do this.
Using the above query, then you can simply add details to a order, and the
stock reduces. and, if you add new stock records, then the stock increases.
The real beauty of the above approach is that you can simply build forms. If
you delete a detail record, or even delete a whole order, then the stock
levels automatically change. In fact, even if you modify a quantity in a
order from 25 to 26, then the stock levels also go down by 1. Often, the
code that runs when you modify something from 25 to 26 (for example) can be
very tricky, as you are modifying a existing order, and not actually
ordering 25, but only increasing the order by one. (this kind of code can be
messy).
I have not built any forms in the following example, but you can grab the
download here..and take a look at the report, and the query. On fact, with
that query, building the forms and reports will be a very easy, as you do
NOT need any code to increment, and decrement the stock values. You just
build forms, and add/delete records....
http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html