discounting of prices line items combo select

[Lesg]

Hi Guys, I have a combo box on an invoice line item subform and use:

 strFilter = "ProductID=" & Me!cboProducts
 Me!price = DLookup("price", "products", strFilter)

To pull the price into the line item, however, each customer can have a different discount level, 5%, 6.25% etc so I need a way to apply the discount to the price.

The parent form has the discount amount available: Forms![frmOrderPlacement]![txtDiscount] so wondered if there was a way to calculate the final price within the combo selection process like:

Me!price = DLookup("price*Forms![frmOrderPlacement]![txtDiscount] ", "products", strFilter)

but assume as its a Dlookup it simply wants to get the value of price and cannot do a calculation at this point?



So any suggestions would be appreciate.

I don't want to create a temp table, as the users are using a multiuser virtual desktop, so temp table don't work.

Thanks in advance.

[Bob Fitz]

Create a calculated filed in the Query used for the combo box. You should be able to reference the discount on the main form from the query.
If you still have difficulties, post a copy of the db with just a few bogus records for us to play with.

[Lesg]

Hi Bob, thanks for that feedback, I have just had a moment of clarity and had added:

Me.rate = Forms!frmOrderPlacement!txtDiscount
Me.price = price - (price * rate / 100)

which has resolved the problem

I spent hours yesterday then 5 minutes after posting this question, it came obvious to me!

[Bob Fitz]

Hi Bob, thanks for that feedback, I have just had a moment of clarity and had added:

Me.rate = Forms!frmOrderPlacement!txtDiscount
Me.price = price - (price * rate / 100)

which has resolved the problem

I spent hours yesterday then 5 minutes after posting this question, it came obvious to me!

Glad you have a solution to your problem.