LookupField1 filters selections for lookupField2?

[AccessAccess]

Hey everyone,

I consider myself new to access having only created some simple databases.

I have come across this question many times but it hasn't been a requirement for any databases yet.
Now, I am creating a database and for simplicity we will say it has 3 tables, 2 of which are lookup tables for the customer table:

Customer Table
=Fields=
-CustomerName
-CustomerProvince (lookup: CustomerProvince table - CustomerProvince)
-CustomerCity (lookup: CustomerCity table - CustomerCity)


CustomerProvince Table
=Fields=
-ProvinceCode
-CustomerProvince

CustomerCity Table
=Fields=
-CustomerCity
-ProvinceCode



My question is, on my Customer table when I select a value for CustomerProvince field, how do I filter the selections now available in the CustomerCity field on the customer table, to only show cities that are in that province?

My goal is to make it simple as possible for the user who will be inputting new customers with forms. Below is an attachment of a pic with a simple ERD.


Any help is appreciated.
-M

[June7]

Sounds like dependent (cascading) comboboxes. Review http://datapigtechnologies.com/flash...combobox2.html

[AccessAccess]

I have seen this example of Cascading comboboxes.
I just do not understand what relationships/tables to make or what queries or tables/fields to make my form off of.

My customer form would include:

CustomerName
CustomerAddress
CustomerCity
CustomerProvince
...etc.

where City and province would be the cascading comboboxes.

[June7]

You need a table that has records to associate City and Province and maybe also mail code.

tblCityProvinceMC
CityName
ProvinceName
ProvinceCode
MailCode

[AccessAccess]

is there a relationship required from that table(tblCityProvinceMC) to the Customer table?

[June7]

Only if you want to save an ID value for the CityProvinceMC data instead of saving each component individually in the Customer table.

[AccessAccess]

This may be a "dumb" question... but what is the difference?

[June7]

If you save the ID then will always have to retrieve the City/Province/MC related info by joining tables. Saving the ID can be made to work with only 1 combobox by having user enter the city and make choice from the rows that match the entered city. This assumes you have full address info on the new customer.

If you save the components then no table join required but have to enter/select 3 pieces of data instead of one.