Changing value in a table

[Chuck in Phoenix]

Hello
I am building a database with an inventory table. I want to adjust inventory level every time a new order is place
(shows up in the new orders table)
i've tried a macro but can't seem to get the inventory level to update.

 

[Lynn Trapp]

How are you trying to do this? Do you have a data entry form that is used to
enter new orders?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Hello,
I am building a database with an inventory table. I want to adjust

inventory level every time a new order is placed

 

[Chuck in Phoenix]

Lynn
yes, I enter order data in a new orders form similar to the Northwind form. I've tried several ideas but not being an Access guru makes it difficult.

 

[Lynn Trapp]

Chuck,
You are not going to be able to do this with a simple macro. You will need
to write some VBA code to accomplish it. Without knowing the exact layout of
your tables, it won't be easy to give you much more than a general break
down of the steps.

1. Add code to the BeforeUpdate event of your new orders form.
2. Include a call to a query that gets the current inventory level from
your inventory table.
3. Subtract the order amount from the current inventory level.
4. Call an update query that sets the value in the inventory table to the
value just calculated in step 3.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Lynn,
yes, I enter order data in a new orders form similar to the Northwind

form. I've tried several ideas but not being an Access guru makes it
difficult.

 

[Chuck in Phoenix]

Thanks Lynn. I kinda thought I'd need a macro...but thought it would be AfterUpdate. I'll try both.

 

[Lynn Trapp]

You might be able to use AfterUpdate for that, but you have less control
than you do with BeforeUpdate. If something goes wrong you can set the
Cancel paremeter in BeforeUpdate to True and stop the processing.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Thanks Lynn. I kinda thought I'd need a macro...but thought it would be

AfterUpdate. I'll try both.