Linked Combo Boxes

  • Thread starter Thread starter ricky
  • Start date Start date
R

ricky

Hi

I am trying to create a pair of Linked Combo boxes, but cannot seem to
achieve this, is there a way I can do this. Both Combo are populated by a
query, but I would like the first combo to filter the second combo.

i.e
First Combo = Country
Second Combo = City

I would like the first combo to select a country and then the second combo
would only show cities in that country?

Is this possible?

Kind Regards

Rikesh
 
Yes; very possible.

Your first combo box (I'll call it comboCountry) should have an underlying
query something like:

SELECT [tblCountries].[CountryName] FROM [tblCountries]

And your second combo box (I'll call it comboCity) should have an underlying
query something like:

SELECT [tblCities].[CityName] FROM [tblCities]
WHERE [tblCities].[CountryName] LIKE (Forms![formName].comboCountry)

The only other thing you should need is: in comboCountry's AfterUpdate
subroutine, you need to code:

Me.comboCity.Requery

This is so that comboCity will reflect the correct cities after any change
to comboCountry.

Hope that helps. Good luck.

-ndalton
 
This is the second most asked question in the Access News Groups.
It is pretty simple, actually. The rowsource for the second combo needs to
include filtering based on the current value in the first combo. In the
After Update event of the first combo, requery the second combo.
 
I am having this same issue except the fields for the 2 combo boxes are not
on the same subform. The 1st combo box (cboModel) is on subform fsubOrder
and the 2nd combo box (cboRoom) is on subform fsubOrderDetail. Both subforms
are part of a main form frmBuyer.

The main tables that these forms use are:

tblBuyer: BUY_ID (PK)
tblOrder: ORD_ID (PK), BUY_ID (FK), MOD_ID (FK)
tblModel: MOD_ID (PK), Model_Name

tblRoom: ROO_ID (PK), MOD_ID (FK), Room_Name
tblOrderDetail: ODE_ID (PK), ORD_ID (FK), BUY_ID (FK)

For main form frmBuyer I am using a query based on tblBuyer

For subform fsubOrder (a subform of frmBuyer) I am using a query based on
tblOrder, tblBuyer and tblModel. The link Master/Child field is BUY_ID

For subform fsubOrderDetail (which is a subform of fsubOrder) I am using a
query based on tblOrderDetail, tblOrder. The link Master/Child field is
ORD_ID

This is how I'm trying to set this up.

A buyer (tblBuyer) can buy 1-to-many Models (tblModel). A model can have
1-to-many Rooms (tblRooms).

On the main form (frmBuyer), I have the subform (fsubOrder) which lists the
Models that the buyer has bought. On the subform (fsubOrder) I have the
subform (fsubOrderDetail) where I want to list all the rooms associated with
each Model the Buyer has purchased. Since a Model can have many rooms (lets
say 10 rooms) I want all 10 rooms displayed (10 rows on datasheet view) on
the subform (fsubOrderDetail) for each model on subform (fsubOrder).

FYI - I previously posted this scenario on 6/16/06 (Subject: Getting a
subform to automatically display info from a table). I tried the response I
received from "Sprinks" but that did not work either. I don't know why this
has to be such a difficult thing to accomplish :(


ndalton said:
Yes; very possible.

Your first combo box (I'll call it comboCountry) should have an underlying
query something like:

SELECT [tblCountries].[CountryName] FROM [tblCountries]

And your second combo box (I'll call it comboCity) should have an underlying
query something like:

SELECT [tblCities].[CityName] FROM [tblCities]
WHERE [tblCities].[CountryName] LIKE (Forms![formName].comboCountry)

The only other thing you should need is: in comboCountry's AfterUpdate
subroutine, you need to code:

Me.comboCity.Requery

This is so that comboCity will reflect the correct cities after any change
to comboCountry.

Hope that helps. Good luck.

-ndalton


ricky said:
Hi

I am trying to create a pair of Linked Combo boxes, but cannot seem to
achieve this, is there a way I can do this. Both Combo are populated by a
query, but I would like the first combo to filter the second combo.

i.e
First Combo = Country
Second Combo = City

I would like the first combo to select a country and then the second combo
would only show cities in that country?

Is this possible?

Kind Regards

Rikesh
Click to expand...
Click to expand...
 
Hi

Thanks for the postings chaps. I have tried this, although I am not sure if
I've done something wrong, but I blanked out the Row Source Type and Row
Source, from the properties box, and in the code, I have placed the
following:

Private Sub cboCountry_AfterUpdate()
On Error Resume Next

cboCity.RowSource = "SELECT [City Name] FROM CityCodeList WHERE [Country
Name Code] = '" & cboCountry.Column(1) & "'"
cboCity.Requery

End Sub


But this does not seem to work?

Kind Regards
Ricky

ndalton said:
Yes; very possible.

Your first combo box (I'll call it comboCountry) should have an underlying
query something like:

SELECT [tblCountries].[CountryName] FROM [tblCountries]

And your second combo box (I'll call it comboCity) should have an underlying
query something like:

SELECT [tblCities].[CityName] FROM [tblCities]
WHERE [tblCities].[CountryName] LIKE (Forms![formName].comboCountry)

The only other thing you should need is: in comboCountry's AfterUpdate
subroutine, you need to code:

Me.comboCity.Requery

This is so that comboCity will reflect the correct cities after any change
to comboCountry.

Hope that helps. Good luck.

-ndalton


ricky said:
Hi

I am trying to create a pair of Linked Combo boxes, but cannot seem to
achieve this, is there a way I can do this. Both Combo are populated by a
query, but I would like the first combo to filter the second combo.

i.e
First Combo = Country
Second Combo = City

I would like the first combo to select a country and then the second combo
would only show cities in that country?

Is this possible?

Kind Regards

Rikesh
Click to expand...
Click to expand...
 
Worked it out, needed to also set the RowSourceType = "Table/Query"

Works perfectly now.

ricky said:
Hi

Thanks for the postings chaps. I have tried this, although I am not sure if
I've done something wrong, but I blanked out the Row Source Type and Row
Source, from the properties box, and in the code, I have placed the
following:

Private Sub cboCountry_AfterUpdate()
On Error Resume Next

cboCity.RowSource = "SELECT [City Name] FROM CityCodeList WHERE [Country
Name Code] = '" & cboCountry.Column(1) & "'"
cboCity.Requery

End Sub


But this does not seem to work?

Kind Regards
Ricky

ndalton said:
Yes; very possible.

Your first combo box (I'll call it comboCountry) should have an underlying
query something like:

SELECT [tblCountries].[CountryName] FROM [tblCountries]

And your second combo box (I'll call it comboCity) should have an underlying
query something like:

SELECT [tblCities].[CityName] FROM [tblCities]
WHERE [tblCities].[CountryName] LIKE (Forms![formName].comboCountry)

The only other thing you should need is: in comboCountry's AfterUpdate
subroutine, you need to code:

Me.comboCity.Requery

This is so that comboCity will reflect the correct cities after any change
to comboCountry.

Hope that helps. Good luck.

-ndalton


ricky said:
Hi

I am trying to create a pair of Linked Combo boxes, but cannot seem to
achieve this, is there a way I can do this. Both Combo are populated
Click to expand...
Click to expand...
by
a
Click to expand...
Click to expand...
 
Back
Top