combo box based on another combo box

[Darhl Thomason]

I would like to have a combo box based on another combo box on my form. In
my tables, I have a Region table with two Regions; I have a District table
with 5 districts. Table structure is:

tblRegion
RegionID RegionName
1 Western
2 Central

tblDistrict
DistrictID DistrictName RegionID
1 Mountain 2
2 Northern Plains 2
3 Pacific 1
4 NorthWest 1
5 MidWest 2

What I want is to have the Region combo box control what shows in the
District combo box. For example, if I select the Western Region, I only
want the Pacific and NorthWest Districts to show. And if I select the
Central Region, I only want the Mountain, Northern Plains and MidWest
Districts to show.

The RowSource for the Region combo box is:
SELECT tblRegion.RegionID, tblRegion.RegionName FROM tblRegion ORDER BY
tblRegion.RegionName WITH OWNERACCESS OPTION;

The RowSource for the District combo box is:
SELECT tblDistrict.DistrictID, tblDistrict.DistrictName FROM tblDistrict
ORDER BY tblDistrict.DistrictName WITH OWNERACCESS OPTION;

My DB is programmed in VBA and is an Access2003 file format.

Thanks!

Darhl

 

[Amy Blankenship]

I would like to have a combo box based on another combo box on my form. In
my tables, I have a Region table with two Regions; I have a District table
with 5 districts. Table structure is:

tblRegion
RegionID RegionName
1 Western
2 Central

tblDistrict
DistrictID DistrictName RegionID
1 Mountain 2
2 Northern Plains 2
3 Pacific 1
4 NorthWest 1
5 MidWest 2

What I want is to have the Region combo box control what shows in the
District combo box. For example, if I select the Western Region, I only
want the Pacific and NorthWest Districts to show. And if I select the
Central Region, I only want the Mountain, Northern Plains and MidWest
Districts to show.

The RowSource for the Region combo box is:
SELECT tblRegion.RegionID, tblRegion.RegionName FROM tblRegion ORDER BY
tblRegion.RegionName WITH OWNERACCESS OPTION;

The RowSource for the District combo box is:
SELECT tblDistrict.DistrictID, tblDistrict.DistrictName FROM tblDistrict
ORDER BY tblDistrict.DistrictName WITH OWNERACCESS OPTION;

try

SELECT tblDistrict.DistrictID, tblDistrict.DistrictName FROM tblDistrict
WHERE tblDistrict.RegionID LIKE IIF(Me.RegionID IS NOT NULL, Me.RegionID,
"*")
ORDER BY tblDistrict.DistrictName WITH OWNERACCESS OPTION;

Then put

Me.District.Requery

in the Region combobox's AfterUpdate

HTH;

Amy

 

[Graham R Seach]

Darhl,

The following pages describes how to do it:
http://www.pacificdb.com.au/MVP/Code/ComboRS.htm

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Darhl Thomason]

Thanks Graham!

That did the trick. I've bookmarked your page, hopefully I can use your
page for other reference ideas.

Darhl