Pull price based on another record

[skwilliams]

I'll try to explain in as much detail as possible.

If I have a table with 5 fields "Item, Qty, Price, AddlQty, AddlPrice" with thousands of items listed. Each item is listed numerous times. Below is a example of one item in the table to give an idea.

Item | Qty | Price | AddlQty | AddlPrice
AP453 25 43.20 0 0
AP453 50 62.00 0 0
AP453 75 87.90 0 0
AP453 100 111.60 0 0
AP453 999 111.60 25 27.80

The Qty 999 is just used to indicate an additional quantity.

What I have so far is a form with a Needed text box, Item combo box, Qty combo box, Price text box, AddlQty text box, and AddlPrice text box.

What I need is if the Item selected is AP453 and the amount entered in the Needed field is larger than 100 then the AddlQty will populate with 25 and the AddlPrice with 27.80. The AddlQty , AddlPrice, and Maximum quantity will vary by item numbers. For example, this item number has a maximum quantity of 100 before using additional quantity and price. But another item number may have a maximum quantity of 125 or 150 before using additionals.

I've been struggling with this issue for a while now. Any help is greatly appreciated.

Thanks.
Steve

 

[Ithilien]

One way to do this is to write an event procedure for the AfterUpdate event for the "Needed" field. Like:

Private Sub Needed_AfterUpdate()

If Me![Item] = AP453 Then
If Me![Needed] > 100 Then
Me![AddlQty] = 25
Me![AddlPrice] = 27.8
Else
End If

Danny

 

[Ithilien]

What I understand Steve's situation to be is a data entry form, where he wants values filled in automatically based on entered values in other fields, where the data being entered refers to orders being placed, and where "Item" is a product ID. So you can have many orders for the same item.

Have I understood correctly, Steve?

Is that what you are referring to, Pat?

Danny

 

[skwilliams]

Your assumption is correct and thanks for the code, but the other part of the problem is different items have different additional quantities and prices.

For example, the AP453 can be ordered in additional quantities of 25 @ $27.80 but another item may have an additional quantity of 50 @55.00

I hope that I haven't totally confused you. I almost confused myself.

Thanks for all your help.

 

[skwilliams]

After some painful trial and error, I think that I've come up with a solution.

Do you still have one small problem. When selecting from the Quantity combo box it lists each quantity multiple times.

Here's the SQL statement I used in the row source.

SELECT DISTINCT [Quantity], [Price] FROM tblItemPrice WHERE ([tblItemPrice].[Item])=[Forms]![frmQuote]![frmItemCharge].Form!Item;

Any ideas why DISTINCT isn't working on this procedure?

Thanks.

 

[Ithilien]

Is it because you are retrieving both Quantity and Price, and so there will be multiple *combinations* of those values, each combo still being distinct?

If that is the case, I don't know off the top of my head how else you would construct the query.

As to the item price and AddlQty prices, you could use the Select Case statement, if the AddlQty prices are some multiple of the original price - like 5% discount, 10% discount - then that would be easily managed with the Select Case statement.

Danny