Look up unit price for each product based on unit total

[JferWfer]

Ok, I am fairly new at complex coding, this seems to be simple but I just can't think of a process for it. Any suggestions would be greatly appreciated.

I have a subform called "OrderDetails" and a main form called "Orders". They are linked by OrderID. In the "OrderDetails" subform you select the products (From a products table) for the order and then enter the quantity. Products ship in drums. Product pricing varies by drum quantity. It take into account the whole order (mix and match).

Example of Pricing:
Product Name 1-10 Drums 11-20 Drums
Product X $2.00 each $1.00 each
Product B $3.00 each $2.00 each

So if a customer orders 5 drums of X and 11 drums of B they will get pricing based on the total drum quantity (16 drums). I have a field on "OrderDetails" that calculates the total number of drums ordered. I need to now update the cost field based on this total. For this example I would need it to find Product X, look for the proper pricing column based on the total quantity then insert the price into the Cost field. Any ideas? Unfortunately there is no systematic reduction in pricing like my example shows. Thanks for all of your help.

Jennifer

 

[Isadore]

Jennifer -

This should be straightforward. In the subform, on the Afterupdate of the number of drums textbox, put in a DLookUp statement and retrieve the cost.

The code would look something like:

If txtQty > 0 and txtQty < 11 Then
txtCost = DLookUp(&quot;[1-20 Drums]&quot;,&quot;tblProducts&quot;,&quot;[ProductID] = Forms![frmMyForm].Form![frmSubForm]![Product_ID]&quot
ElseIf txtQty > 11 and txtQty < 21 Then
txtCost = DLookUp(&quot;[11-20 Drums]&quot;,&quot;tblProducts&quot;,&quot;[ProductID] = Forms![frmMyForm].Form![frmSubForm]![Product_ID]&quot
ElseIf.... 'continue if/then
End If

...that's one way. Alternatively, you could have nested If and Only If statements. IIF (condition, return this, return that). Just a couple of ideas here for you. Post back if you need additional help. Someone may drop by with a simpler approach. But the problem is straightforward. You choose a quantity. You now need to &quot;look up&quot; the price. Using a DLookUp for this would work.

If you need to adjust the price, e.g., multiply qty by unit price, just add...

txtCost = txtCost * Qty (simple algebra)

 

[JferWfer]

Thanks, I had to adjust the code a bit and attach it to a different field but I did get it to work. Thanks for pointing me in the right direction.