Price List Table and Discount Price Table Lookup

[mullank2]

I have 3 tables which I would like to create master price list (Report) for each Customer (Distributor) based on the country they resell our goods in. If the [tblCustomer.Country] matches both [tblCustomer.Country] AND [tblDiscount .ProductCode] exists in the discount table that [tblDiscount.Price] should override the [Report.Price] in the report otherwise show the standard [tblProducts.Price]


Table Structure:

Table 1 – tblCustomer

Country E.g. Spain (To get the Country Discounts)
CustomerId E.g. 100
CustomerName E.g. ABC Trading Co
Currency E.g. GBP / EUR / USD
CurrencyRate E.g. 1.000 / 1.250 / 1.650

Table 2 – tblProducts

Category
ProductCode E.g. ABC0001
Description
Size
Key
ShelfLife
Price E.g. 2.70


Table 3 – tblDiscounts
Country E.g. Spain (To get the Country Discounts)
ProductCode E.g. ABC0001
Price E.g. 2.50


Report:


CATEGORY
ProductCode Description Size Key ShelfLife Price
ABC0001 Goes here 1x10ml LS 24 months 2.50 (Match in tblDiscount)
ABC0002 Goes here 1x20ml LS 24 months 3.75 (No match so tblProducts)


I am creating a button on a customer form (to open the price list report) that has all of the tblCustomer fields named txtCountry etc... I need a “On_Click” VBA Code solution similar to this below to get this to work but I am stuck.... Please help many thanks!

Me.txtPrice = (Nz(DLookup("[Price]", "TblDiscounts", "[ProductCode] = '" & Me.txtProductCode & "' AND [Country] = " & Me.Parent.txtCountry), DLookup("[Price]", "TblProducts", "[ProductCode] = '" & Me.txtProductCode & "'"))) * (Me.Parent.txtCurrencyRate)

 

[Big Pat]

I suppose there could be several approaches to this but I would use a query to produce my report.

So, you create a query that calculates the "FinalPrice" (or whatever you want to call it) based on the same logic you've outlined. But in the criteria line of the query, you specify that you want only the customer currently displayed on your form.
Something like [Forms]![frmCustomer]![CustomerID]

With your form open, scroll to a chosen Customer and run the query manually to make sure it works OK. Now base your report on that query.

Back on the form, your Command button now just needs to open the report, without any complex code, as the query will handle all of that for you.

I hope this helps.