Updating Prices (Access97/SR2)

Topic

New Reply

I have a DB that we will be using to produce invoices for customers. I have a table set up with the prices of all items. The problem is that those prices will change in the future. We want to keep the old invoices available for reporting.

How do I “lock” in a price for an invoice produced in September, for example, if the price of an item on that invoice is changed in December. Any help would be appreciated. Thanks.

Reply | Quote

Replies

>>How do I “lock” in a price for an invoice produced in September, for example, if the price of an item on that invoice is changed in December. Any help would be appreciated. Thanks<<

I'm assuming an invoice can have many Detail records. You need a price/unit field in the detail record. During data entry of a new record, you can get the current price for an item as soon as the user enters it, and fill the Detail Price field with this value. (up to you to allow changes) This is perfectly reasonable, and doesn't violate any Rules of Normalization, since you are recording the price of a specific item that was sold.

Reply | Quote

Mark,

Thanks for the quick response. What you described is what I want to do, I just don’t know how to do it.

The user has a form to enter the information, this form is based on a query, which pulls information from the Order Table and the Price Table. The user is not allowed to input the price, only the quantities. How do I pull in that price and make it “stick”. The way I have it right now, if I create an invoice then change the unit price on an item, the invoice changes to match the new unit price.

I’m sure it’s simple, that’s the problem, so am I. Thanks for your help.

Reply | Quote

Don,

I think what Mark was saying was that your Order table also has to have a price field (to record the price copied over from the Price table when the order was created).

On your order form show the Order table price field but lock it from editing. In the After_Update event for the quantity field include something like Me!ctlPrice = Me!Qty * DLookup(“Price”, “Price Table”, “[ItemID] = ” & Me!ItemID). Note that Me!ctlPrice is bound to the Order table price field.

Hope this gets you going. If you need more detail, then holler — I’m sure someone will jump in and help!

Tom

Reply | Quote

I was pulled off of this, and now finally got back. This worked out very well, than you so much. Of course, I had a strange problem before I could get it to work. I am posting this so that it may be helpful to others.

This worked to pull in my price from the price table, and then post the correct price to the Order table but for some reason, it was rounding the number to the nearest dollar. I couldn’t figure out what was wrong, the format for the control on the form and my price table were both set to Currency, 3 decimal places. I then checked the Order table and the price was set as a number with a currency format, once I changed it to Currency, everything worked fine. Odd problem, again thanks for all the help.

Reply | Quote

You can automatically insert the price in a couple of ways. Of course, you have to have a price field in your Order table. On your Order Entry form, you must have a control for Product. This is probably a combo box.

One way is to insert a line of code in the AfterUpdate event of this combo box (let’s call it cboProduct), something like this:

txtProductPrice = DLookup("Price","PriceTable","Product=" _
   & chr(34) & cboProduct & chr(34) )

The chr(34) are there in case your product is not numeric.

Alternatively, you can have the price as a column in your product combo box (whether or not it is displayed). Let’s say the price is the 3rd column, then you would have this in your AfterUpdate event:

txtProductPrice = cboProduct.column(2)

Reply | Quote