automatic deducting quantity from table

  • Thread starter Thread starter dumshmo
  • Start date Start date
D

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.
 
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
 
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
 
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
Click to expand...
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.
 
Back
Top