Combo box with values based on another combo box

[Angel69]

I have a main form that filters data in a subform based on selections via combo box users make on the main form.

So I have the 'department' and 'manager's name'. If someone selects 'Human Resources' from 'department' combo box, then I only want to see the managers that are in the Human Resourses departement when I drop down the combo box for 'manager's name'. Currently I'm seeing all the managers and a user can select a manager that is not in human resources and get no data returned. I prefer for him to get a list of those that are in that department only. The source of the combo box is a query.

Thanks in advance for any help!

EDITED: I got it! Found help here: http://accessprogrammer.blogspot.com/2005/08/filter-combobox-based-on-another.html

 

[RuralGuy]

Do a search on "Cascading Combo Boxes".

 

[zooropa67]

See the following where I posted a small file showing a Country > City Combo pair
http://www.access-programmers.co.uk/forums/showthread.php?p=1255122#post1255122

 

[MarvinM]

I have solved this. I have a similar situation except working with Region, Area and Facility. I have a Location table (tblLocation) that contains 3 columns: Region, Area, and Facility. The combo box for Region uses this for the Row Source:

SELECT DISTINCT tblLocation.Region FROM tblLocation ORDER BY tblLocation.Region;

And I put this in the After Update event:

    cboArea.Value = Null    ' Reset Area field
    cboFacility.Value = Null    ' Reset Facility field
    [cboArea].Requery
    [cboFacility].Requery
    [cboArea].SetFocus

When a particular Region is selected from that combo box, I want to make the Area combo box only show valid Areas within the selected Region. So this goes in the Row Source for the Area combo box:

SELECT DISTINCT tblLocation.Area FROM tblLocation WHERE (((tblLocation.Region)=Forms![frmMain]!cboRegion.Value)) ORDER BY tblLocation.Area;

And in the After Update event:

    cboFacility.Value = Null    ' Reset Facility field
    cboFacility.Requery
    cboFacility.SetFocus

The final piece is, for selecting from just the facilities in that area, the Row Source for the Facility combo box:

SELECT DISTINCT tblLocation.Facility FROM tblLocation WHERE (((tblLocation.Area)=Forms![frmMain]!cboArea.Value) And ((tblLocation.Region)=Forms![frmMain]!cboRegion.Value)) ORDER BY tblLocation.Facility;

I hope this is some useful code that many people can reuse.