Update Record from record in other table

[Dave]

Hi,

Can you assist,

I have two tables, one called Stock, listing material code & available
quantity, the 2nd called Order, listing order no's, material code, and qty
required for that order.

Is there a way of checking each line on the order table to the stock table,
and then reducing the available qty for that material code before checking
the next line, basically there could be 10 pieces available, with 6 orders of
2 on each. I would like to be able to run an extract of the orders that will
not be satisfied.

Cheers
David

 

[Steve Schapel]

David,

It would generally be regarded as not a good idea to have a "available
quantity" field in a table. Instead, you should have provision for
entering when items are added to stock. This can be in the Order table,
preferably. Then you can calculate the available quantity whenever
required. There is some good information on this problem at
http://www.allenbrowne.com/AppInventory.html

In this scheme, you could then use a suitable event, for example the
Before Update event of your order details form, and display a message
box or some such to the user if there is insufficient stock to fulfill
that order line.