Filter Dropdown Combobox by Another Field

[kdagostino]

I have a subform for tblFaculty in which the fields Title, FirstName,
LastName, and Suffix are combo boxes based on those fields. i.e. Title looks
up the values in Title from tblFaculty.

Is there a way I can filter the dropdown menus based on which one has been
entered?

For example, if the user enters in data in LastName, then FirstName only
looks up values for FirstName which have that last name as well.

I

 

[Klatuu]

Yes, the technique is called Cascading Combos.
To do this, you use queries for your combo row source properties.
You make the second combo in the order you want filter based on the value in
the first combo, and the third filter on the second, etc.
Then in the After Update event of the first combo, requery the second combo,
and in the After Update event of the second combo, requery the third, and so
on.

 

[thefonz37]

Is there a website that maybe gives a simple tutorial for the steps on how to
do this? Maybe an example database?

 

[Klatuu]

Thought I knew where one is, but I can't find it.
Actually, it is not that hard.
Lets say we want to filter by Company and Department.
Lets set up two combos. cboComp and cboDept
We only want to see departments in cboDept for the company selected in
cboComp.

So we set up our row source for cboComp
SELECT CompID, CompName FROM tblCompany;

Where The bound column is 1, which will return CompID for the selected row.

Now we set up the row source for cboDept to filter on the company
SELECT DeptID, DeptName FROM tblDept WHERE CompDept = Me.cboComp;

Where CompDept is the field in tblDept that is the foreign key to tblCompany

Now in the After Update event of cboComp

Me.cboDept.Requery

Simple as that. When you select a company, only that company's departments
will be listed in cboDept.

So, one step further, we only want to see Contacts in the Department
selected. We need a row source for cboContact that filters on the selected
department

SELECT ContactID, ContactName FROM tblContact WHERE DeptContact =
Me.cboDept;

And again, in the After Update of cboDept

Me.cboContact.Requery

 

[Douglas J. Steele]

Is
http://office.microsoft.com/en-us/access/HA011730581033.aspx?pid=CH063650361033
what you were looking for?

 

[thefonz37]

Hmm...on futher though this might not be the right thing I need to use.
Thanks for answer, though, I'm definitely storing the knowledge for future
use.

 

[Clif McIrvin]

Well Doug, I can't speak for anyone else, but that link sure looks
like something I will be able to use in the app I'm developing!

Thanks.