Set update query to reduce single field by user defined number

  • Thread starter Thread starter Anders
  • Start date Start date
A

Anders

Greetings,

I am looking for the best way to update my inventory on hand for a specific
product after I make a sale. I have a customer form with an order details
subform where I enter my customer/order data. The OD subform has a product
ID as a FK which is the PK of my product table (where the product description
and amount in inventory are held). After I enter an order, I'm looking for
the best way to update the amount in inventory, preferably with an action
button on the same form.

I'm currently trying to create an update query to do this, but 1. want to be
sure this is the best way, 2. if it is, figure out why mine isn't working.

If there is a better way to do this, I'm all ears.

Here is what I've tried with my update query. I set it to ask which product
I'm updating(b/c I have more than one product) (product ID is my PK) and then
I wanted it to ask how many units to reduce the # in inventory column by, but
this isn't working.

UPDATE Products SET Products.[# in Inventory] = [Products]![# in
Inventory]-[enter number of units sold]
WHERE (((Products.ProductID)=[product id]));

Everytime I try to run it, I get nothing but an error in the bottom message
bar "The action or event has been blocked by Disable Mode" - I followed the
help directions for 'trusting' the location of the folder to no avail. So
I'm guessing my formula is wrong?

Thanks in advance.

Anders
 
Assumptions:
[Enter Number of Units Sold] and [Product ID] are parameters

If so, try defining the types of the parameters. AND use . not ! as the
separator between the table name and the field name.

Parameters [Enter Number of Units Sold] Short,
[Product ID] Text (255);
UPDATE Products SET Products.[# in Inventory] =
[Products].[# in Inventory]-[enter number of units sold]
WHERE Products.ProductID=[product id]

Hopefully this will make the query work.

A problem with doing this is that you can end up with an inventory
amount that is inaccurate - Run the query more than once, enter the
wrong amount, make an error in product id, or forget to run the query
and your figures are wrong.

A better solution might be to calculate the amount available using a
query when you need that value. You might choose to update the base
number on hand periodically (monthly or annually) and save the balance
as of the specific date. You can then add/subtract all changes since
that date in a query.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Thanks John, that worked!

So if an order is for more than one product, I can just add rows to the
query and further define the input descriptions?

Also, on the monthly or when needed idea, are you saying I could sum all of
the quantity numbers on the order details table for a specific product,
subtract that from a value in the products page (say original order was 1000)
and have it displayed in a report as inventory still in stock?

Say I ordered 1000 and have that in the products page cell as amount in
inventory, and 3 months worth of sales data totaling 350 units sold, I could
create a query to sum those sales, and display 650 without appending the
value in the products table? I think that would work for me, I just want to
be able to know what I have left, I don't necessarily need to append data.
How would I do that?

Thanks again!

Anders
--
I''''ve encountered an error and I need to close for the weekend :)


John Spencer said:
Assumptions:
[Enter Number of Units Sold] and [Product ID] are parameters

If so, try defining the types of the parameters. AND use . not ! as the
separator between the table name and the field name.

Parameters [Enter Number of Units Sold] Short,
[Product ID] Text (255);
UPDATE Products SET Products.[# in Inventory] =
[Products].[# in Inventory]-[enter number of units sold]
WHERE Products.ProductID=[product id]

Hopefully this will make the query work.

A problem with doing this is that you can end up with an inventory
amount that is inaccurate - Run the query more than once, enter the
wrong amount, make an error in product id, or forget to run the query
and your figures are wrong.

A better solution might be to calculate the amount available using a
query when you need that value. You might choose to update the base
number on hand periodically (monthly or annually) and save the balance
as of the specific date. You can then add/subtract all changes since
that date in a query.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Greetings,

I am looking for the best way to update my inventory on hand for a specific
product after I make a sale. I have a customer form with an order details
subform where I enter my customer/order data. The OD subform has a product
ID as a FK which is the PK of my product table (where the product description
and amount in inventory are held). After I enter an order, I'm looking for
the best way to update the amount in inventory, preferably with an action
button on the same form.

I'm currently trying to create an update query to do this, but 1. want to be
sure this is the best way, 2. if it is, figure out why mine isn't working.

If there is a better way to do this, I'm all ears.

Here is what I've tried with my update query. I set it to ask which product
I'm updating(b/c I have more than one product) (product ID is my PK) and then
I wanted it to ask how many units to reduce the # in inventory column by, but
this isn't working.

UPDATE Products SET Products.[# in Inventory] = [Products]![# in
Inventory]-[enter number of units sold]
WHERE (((Products.ProductID)=[product id]));

Everytime I try to run it, I get nothing but an error in the bottom message
bar "The action or event has been blocked by Disable Mode" - I followed the
help directions for 'trusting' the location of the folder to no avail. So
I'm guessing my formula is wrong?

Thanks in advance.

Anders
Click to expand...
Click to expand...
 
Back
Top