Drop down menu selection

[Guest]

If I have data - for example as:

Country City
USA NYC, Boston etc.
Switzerland Basel, Zurich etc.

Now if I make a form and put a (Combo box - drop down menu) for country and
than another one for City. I would like the first selection to automatically
select the list for the second one. That is If one selects Country = USA;
than when the User clicks on the City combo box the list should only display
NYC & Boston. Is this possible? if it is how can one go about doing it?.

Thanks for your assistance.
MSA

 

[Allen Browne]

Use the AfterUpdate event of the first combo to set the RowSource of the
second one.

Details in:
Limit content of combo/list boxes
at:
http://www.mvps.org/access/forms/frm0028.htm

 

[Guest]

Hi Allen,

Thanks for your input. The only problem is that in your case you are
referring to the table header for the first combo box. Wheras I would like it
to read a table column (e.g. Country) and then based on the selection (i.e.
USA) it should make available the US cities (another column in a table) only
for the 2nd combo box.

Look forward to your response, thx.
MSA

 

[Allen Browne]

I don't think I understand the problem here.

On your form, you have 2 combos for Country and City.
When someone selects a Country in the first combo, you want to limit the
City combo so it shows only cities from the selected combo.

Presumably you have a table of Cities, with 2 columns:
Country
City
Country repeats so it should really be related to a table of countries, but
if not, presumably the first combo has a RowSource such as:
SELECT DISTINCT Country
FROM Cities
WHERE Country Is Not Null
ORDER BY Country;

Now you set the AfterUpdate property of this Country combo to:
[Event Procedure]
Click the Build button (...) beside this.
Access opens the code window.
Set up the code so it looks like this:

Private Sub Country_AfterUpdate()
Dim strSql As String
Const strcStub = "SELECT DISTINCT City FROM Cities WHERE ("
Const strcTail = ") ORDER BY City;"
If IsNull(Me.Country) Then
strSql = strcStub & "False" & strcTail
Else
strSql = strcStub & "Country = """ & Me.Country & """" &
strcTail
End If
'Debug.Print strSql
Me.City.RowSource = strSql
End Sub

The actual code you need will depend on your table and field names, and the
data type of the combo's bound column. To get an example of the string you
need to create, mock up a query typing any value you like into the Criteria.
Then switch to SQL View (View menu in query design.)