You are here:

Using MS Access/linking contents of one table to another

Advertisement


Question
Scott,

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.

E.G.

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.

Answer
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:

StateID (PK)
CountryID (FK)
State

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,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.