automatic deducting quantity from table

[dumshmo]

Hello, can anyone tell me how i can update a field in a form automatically. i
have a small form, from a table with 4 fields, one field is called stock
quantity. i also have a sales form with name, address, item(s) sold, quantity
etc. i would like my stock quantity field in the stock form to be
automatically reduced when i enter an amount in the quantity field in the
sales form.

 

[Jeff Boyce]

It all starts with the data.

If your two forms point at the same source of data, they'll both see the
same value(s). You might need to refresh/requery your second form after
changing a value (via your first).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[dumshmo]

thanks for you reply, what i want to achieve is when i sell a product and
enter in the quantity, for example 3, i then want to see when i open my stock
control table 3 automatically deducted from the total amount. i.e i have 20
in my stock, enter a sale in my sale form, then when i open my stock control
table there is only 17 displayed. thanks again

 

[Bob Quintal]

thanks for you reply, what i want to achieve is when i sell a
product and enter in the quantity, for example 3, i then want to
see when i open my stock control table 3 automatically deducted
from the total amount. i.e i have 20 in my stock, enter a sale in
my sale form, then when i open my stock control table there is

Proper database design says you don't do it quite like you intend
to.

Instead of a stock table you have a replenishments table, where you
record additions to the stock.
It might look like this:
Item Qty_Rcvd Date_Rcvd
asdff 64 10/10/2006
asdff 42 12/12/2007
bcdede 55 06/05/2003
bcdede 20 06/06/2006

and you have your sales table

Item Qty_Sold Date_Sold
asdff 24 11/11/2006
asdff 12 12/10/2007
bcdede 2 07/07/2006


You then use a query to sum all the qty_Rcvd for each Item in the
received table and subtract that from the sum of Qty_Sold for the
same item in the sales table.

the math is
Item SumOfQty_Rcvd SumOfqty_Sold Stock
asdff 106 36 70

The reason for doing this with a query instead of updating your
stock table is that if anything should hickup during the update or
you go back and change a quantity in either the Rcvd or Sold columns
after the update, your stock number is wrong.