Using MS Access/linking contents of one table to another
I am designing a new database in Access 2010. I have three tables, "tblCity", "tblState", and, "tblCountry". All of these tables are linked to one another using a standard AutoNumber ID.
I then have a data input form for a customer, who has an address, with a city, state and country.
So, heres my question, how can I set up the form that if a user is inputting a customer from the USA, that only states which are in the USA can be selected in the state list.
John Doe, 12 somewhere, New York, NY, US.
If a user selects New York, how can I make sure a user cant input
John Doe, 12 somewhere, New York, VIC, France?
I want to make sure that if a user selects say Melbourne, that the only option available for state is VIC and the only option available for country is Australia.
The reason I have these three tables set up is because I need to have another form that a user can input new locations into, as they are needed.
I hope this makes sense. Thanks in advance.
Without seeing your table structure I can't be specific. But we are are talking about is a concept called cascading or synchronized comboboxes
. If you Bing those terms you will find plenty of info on how to do it.
Generally, you will have a foreign key in each table to indicate the higher level. So your tblState would look like this:
So the Rowsource of your State combo would look like this:
SELECT StateID, State, FROM tblState WHERE CountryID = Forms!formname!Controlname;
Formname is your form, controlname is your country combo.
In the After Update of the Country combo you Requery the State combo, so it is filtered for the country selected.
Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA