Calculate discount out of combination customer ID and product ID

[farris]

I have made an invoice system in Access which works perfectly. I have the following tables:
Customers, Invoices, Invoice Details, Products, Discounts
the following queries:
Invoice Details (combination of Invoice Details and Products tables) and Discountquery (just Discount table, does basically nothing actually right now).

Now, I have a lot of forms where the most important one is the 'New Invoice' form (to enter the Invoice ID, Customer ID, Date, Invoice Notes) with a subform 'Subform Invoice Details' (to select the products). The source of the subform is the invoice details query. The field Invoice ID from the New Invoice form and the subform is connected so the right details are matched with the right invoice ID.

However, I want some extra stuff in my program. When a customer is selected in the main form (New Invoice) and a product is added to the invoice(by the subform), I want a new field in my subform with 'Discount' that automatically connects the Customer ID (from the main form) and Product ID (from the same record in subform) to display the correct discount.

But there's no idea how I can do that. I tried to add the table 'Discounts' to the query 'Invoice Details' with the criteria [Forms]![New Invoice]![Customer ID] but this results in a subform that doesn't work anymore. When I remove the table, it's working again.

So, my question is, how can I achieve what I want? Is it possible with only the query 'Invoice Details' or do I need to make some seperate queries to come up with the right result?

[pbaldy]

I think you're going to want a field in the invoice details table, since the discount could presumably change over time. In the after update event of the product combo, use a recordset or DLookup() to find the appropriate discount and place it in that field.

[farris]

Hmm, that's a nice suggestion..haven't thought of that. Will try to do so!

[pbaldy]

No problem; post back if you get stuck.