Quantity and In Stock

[Guest]

I have a text box in my form where i input the quantity for the amount of
that item im selling, what i want is for that amount to automatically be
substracted out of the table so if i have 99 hard drives and i make an
invoice (my form) and sell 3 the table will read 96 the next time i go into
it to edit the inventory. how do i do this, i heard about something like an
update function but i dont know how to do it cuz ive been using access for
like a week.

 

[Guest]

Make an update query that updates the value of the stock on hand to its
current value less the value of the current transaction.

Better yet, simply keep track of sales & purchases and add these all
together (Sum of all Purchases minus Sum of all Sales equals Current
Inventory) using a select query (Totals turned on, Grouped by ItemNumber,
with Sum for Purchase & Sale) when you want to find your current inventory
balance.

 

[Guest]

i like the first idea better, im just sticking the way i got it cuz thats the
orders i get from the boss man. but how do i make this update query that
will change the in stock on the table in reference to the product selected in
my second drop down menu. i think it was something to do with the criteria
but i dont know what to put.

 

[Guest]

Sorry about the delay - I was out of town for a couple of days.

If the combo box on the form containing the product is called Product, then
the criteria entry for product in the query builder will look like this:

[Forms]![YourForm]![Product]

This will apply the update to only the product that is currently in the
Product box on YourForm. So...be sure to check that the box is populated
before running the update (If Isnull...)