Purchase Order Price with the Microsoft Northwind Database

what is displayed is simply being looked up from the Products table.  If you want the ability to over-ride the price, then the AfterUpdate event of the product should fill the price and price needs to be a field in the underlying table that is a source for the form and the Control Source for the price -- so the form design needs to change.

Thanks Crystal, so on the Products table, navigate to the Table group and select AfterUpdate?

That would be the AfterUpdate event of the textbox where the new price is entered.  The code would check the price in the Products table, and update if needed (possibly with a confirmation, since you might not always want to update the price for everyone in future).

Hi Sean,

no the procedure that runs after the product is updated to fill in the price would be on the form that collects the information -- and the Purchase Order Details form already does that.  Use the form; don't enter information directly into the tables.

Thanks, I see that now. As for the options, there is nothing that really references update record.

Do I create a macro? If so, from what table?

VBA code is best, running from the AfterUpdate event of the text box with the price.

I assume you realize that if you update the value in the products table that any purchase orders with that product that exist will also be changed to the new value when run again.

It sounds like you need a table change that tracks when a price changes.

mlmcc

I assume you realize that if you update the value in the products table that any purchase orders with that product that exist will also be changed to the new value when run again.

Not true if you design the tables correctly.  Think of the price associated with the product as the "suggested retail value".  When you add an item to the order, this SRV is copied from the product table to the order detail table where it may be overridden or modified by a discount percentage for the client.  this is now the actual sale price and stays on the order forever without change.  Changes to the SRV only apply to new orders.  Or, if your business rules specify, you might run an update query that finds any unshipped orders and modify the price on those.

What I said is valid.  If you update the price in the products table it will change for every order.  

Your method of allowing the price to be edited then saving in the orders or orders detail table is a good way to allow different pricing for products.

mlmcc

If you update the price in the products table it will change for every order.  

It would change for any NEW order, but in the context described above existing orders would remain unchanged, since all that happened was the SRV was collected when the product was selected onto that order, and was then placed into a separate field. This is SOP for Sales Order/Invoicing system.

Sean,
Northwind is actually too simplistic for a real world implementation.  In my previous post I only addressed mlmcc's disagreement with the recommendations experts had already made.  But there are two issues at play.  One is the standard price for a product, which is what I think you are asking to track, and the other is the price on each order.  SOP for this type of application is to copy the price from the product table to the order details at the time the order is placed (I think Northwind does NOT do this).  The price on the order details is the actual sale price and it could be different from the suggested price which originates on the product record.  Once the SRV is copied to the order, it is no longer the SRV and can be overridden based on your business rules.  For example, if a certain customer always gets a 10% discount, you would apply the discount at the time you copy the price from the product table to the order details table.  So a $10 price on the product results in a $9 price on the order.  For tracking SRV history, you need a new table.  To do that, you would add code in the product maintenance form to create the history record.  The absolute simplest way to to this is to run an append query in the AfterUpdate event of the form.  This query copies the current record and adds it to the history table.  Then if you want to analyze SRV over time, you would query the history table.  If you want to analyze actual sales prices, you would use the order details table.

Ok, thank you all for the input. Keeping in mind that I am using this database to track office supply inventory. When we need to restock a supply (we use the Purchase Order feature) and the vendor raises their price, I want to keep tabs on the difference between the old price and new price for accounting purposes.

What modifications should I make to achieve this?

To actually use Northwinds in a real business, you need to make many changes to it.  It is merely a shell and it doesn't follow standard business practice.  My previous post offered one solution to where the change should be made and what you need to do.

Sorry Pat, I replied before I saw your reply.

This is my first Access project in a long time. Considering Northwinds is a web database, I can't create an append query like I would for a Access Client Database.

I get the part about an AfterUpdate, but how do I tell the query to push that data to a new table after?

I created a query called UpdateHistory and added the tables Products and PurchaseOrderDetails. I added the fields ID, ProductName, ListPrice from the Products table and UnitCost from the PurchaseOrderDetails table.

I created a table called PriceUpdateHistory with the same fields.

Not sure what to do next.

Perhaps you could post this task as a Gig.

I'd rather try and execute this myself.

I agree with Pat -- Access Web apps are virtually useless, lacking VBA, linked tables, and any kind of security.

Thank you for your help.

you're welcome ~ happy to help