Return Current Price based on Quantity

[raziel3]

So I made some progress. I redesigned subfrmSales and put the code to give a breakdown of the total after the Quantity is updated. By default the Quantity is 1 as a new item is entered.

I think I can tidy up the code a little, really don't like all the IF statements.

 

[Minty]

I think you are making that overly complicated.
Normally you would simply look that price up based on the part code (UPC?) and the upper and lower price banding and possibly the effective date. If you keep an Lower and Upper qty figure you can use a query join on it to speed up the process, and it becomes a simple DLookup.

I wouldn't try and keep all the historic data and current pricing in one table. If you have 5000 items with 4 price break quantities, and update prices 4 times a year you will very quickly end up with 100,000's of records to search through.

If you need to keep a record of the historic pricing copy the data into a history table, before each update.

Assuming you store the history, and the price sold in the detail when you sell the item, there is no need for an effective date. The price table is always the current pricing.