Referencing many tables and combo boxes to get the answer

[Manc]

Dear Demigods

I have three tables

Table one: tblCountry
CountryID
Country (Every country in the world or most of them)
CountryZone (The world is split into ten zones)

Table two: tblWeight
WeightID
Weight (Weight range is from 1 to 200)

Table three: tblPrice
PriceID
1
2
3
4
5....(etc etc until 200 - represents the Weight from tblWeight)


tblPrice contains ten records (representing the CountryZone from tblCountry) and each of the ten records is populated with prices for 1 to 200.

The idea is this. frmCourier control source is tblCountry and contains an unbound combo box where the user selects the country from the list. Using a little VBA, txtCountryZone is populated with the corresponding Zone. The user then selects the weight from the cboWeight combo box.
I can get this far (wow, I hear you cry!)

What I would like to now happen is for the txtPrice textbox to display the correct price depending on the Zone and Weight selected.

Any ideas / suggestions / reading material, will be greatly appreciated, as always

Kind regards
manc

 

[John Big Booty]

Perhaps the DLookup() function or one of the other Domain Aggregation functions will produce the result you are after.

 

[Galaxiom]

Your table structure is incorrect. There should not be a field a field in tblPrice for each WeightID.

tblPrice should have fields for CountryZone, WeightID and Price and have 2000 records.

This structure will greatly simplify your problem.