Avatar of get-ADuser -F ($_.Name -eq "Todd")
get-ADuser -F ($_.Name -eq "Todd")Flag for United States of America asked on

Second Combo Box Filter

I have Access 2010.  I have a continuous form.  I have one combo box (cbo_BranchSearch) that filters my form

Private Sub cbo_BranchSearch_AfterUpdate()
If Me.cbo_BranchSearch & "" <> "" Then
Me.Filter = "[PriKey] = " & Me.cbo_BranchSearch
Else
Me.Filter = ""
End If
Me.FilterOn = True
Me.cbo_LocationUser.Requery
End Sub

I have a second combo box (cbo_LocationUser) that's rowsource is similar to the first cbo pointing to my query qry_ElistSearch, in which the form gets its data.

The first cbobox works great.  Now I would like the second cbo to be filtered based on what I selected on the first cbo.  Then when I see those, it would then filter my continuous form from my selection on the second.  I am sure this question is asked many times.  But I think I am not phrasing the question right on my search so my apologies.  Thanks you so much.  It has been awhile since I used VBA.   Any help is much appreciated.


Todd
Microsoft AccessVisual Basic Classic

Avatar of undefined
Last Comment
PatHartman

8/22/2022 - Mon
PatHartman

This technique is called "cascading combos" and it is indeed a common question on Access forums.

There are several methods.  The one I use involves adding criteria to the RowSource query and one line of code to the AfterUpdate event of the "parent" combo.

The RowSource query for combo2 would be:
Select ...
From ...
Where SomeField = Forms!yourform!yourcombo1;

Then in the AfterUpdate event of combo1:
Me.Combo2.Requery

To include a third combo:
The RowSource query for combo3 would be:
Select ...
From ...
Where SomeField = Forms!yourform!yourcombo2;

Then the AfterUpdate event of combo2:
Me.Combo3.Requery

And finally, to filter the form - I never use filters because I almost always use non-Jet/ACE back end databases.  Filters assume the ENTIRE recordset is local and is used to reduce what you actually see presented in the form.  Instead, I use criteria in the query itself.  This is far superior when working with remote databases since it limits the data that is retrieved rather than retrieving everything and hiding some of it.

So your RecordSource query would be:

Select ...
From ...
Where somefield = Forms!yourform!combo1 AND somefield2 = Forms!yourform!combo2;

Then you need to Requery the form.  You can do this with a button (my preference when there is more than one selection field involved) or by using the AfterUpdate event of the "rightmost" field.

Me.Requery
ASKER
get-ADuser -F ($_.Name -eq "Todd")

OK I think I got you... AND thank you for the terminology.  I knew its been awhile.  Here is what I have.  And once I see it, I will get back to remembering it and understanding it.  I sometimes get mixed up with this automatic macro assignment too. But Capricorn1 showed me how to stop that from happening.  I have this

Combo1

SELECT [Branch Abbreviations].PriKey, [Abbreviation] & " " & "-" & " " & [Branch Name] AS BRANCH, [Branch Abbreviations].[Branch Name], [Branch Abbreviations].Abbreviation
FROM [Branch Abbreviations]
ORDER BY [Abbreviation] & " " & "-" & " " & [Branch Name];

AfterUpdate is this  ( I think you told me you don't like filters,, and I will take whatever will work)
Private Sub cbo_BranchSearch_AfterUpdate()
If Me.cbo_BranchSearch & "" <> "" Then
Me.Filter = "[PriKey] = " & Me.cbo_BranchSearch
Else
Me.Filter = ""
End If
Me.FilterOn = True
Me.cbo_LocationUser.Requery
End Sub


Combo two I had in RowSource

SELECT qry_ElistSearch.PriKey, qry_ElistSearch.[Location/User]
FROM qry_ElistSearch
WHERE (((qry_ElistSearch.PriKey)=[Forms]![frm_ELsearch]![cbo_BranchSearch]))
ORDER BY qry_ElistSearch.[Location/User];

It does what it is supposed to do perfectly.......   Now what do I do for Combo2's After update?  I am a little hazy on that.  I want to filter down in combo2 when I select the right User from qry_ElistSearch.[Location/User]...  

Thanks for all your help.  Once I get this,  I will be rolling good!
ASKER
get-ADuser -F ($_.Name -eq "Todd")

This did not work in the record source of Combo2.  It may be because of my filtering in Combo1?

SELECT qry_ElistSearch.PriKey, qry_ElistSearch.[Location/User]
FROM qry_ElistSearch
WHERE (((qry_ElistSearch.PriKey)=[Forms]![frm_ELsearch]![cbo_BranchSearch]) AND ((qry_ElistSearch.[Location/User])=[Forms]![frm_ELsearch]![cbo_LocationUser]))
ORDER BY qry_ElistSearch.[Location/User];
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
PatHartman

You are referencing two combos in this query.  I would have expected it to reference only combo1.
ASKER
get-ADuser -F ($_.Name -eq "Todd")

So I must be doing something wrong.  I guess the simplest question is.  How to take a Continuous form, and filter it down by two combo boxes?  I do understand the cascade, but it doesn't do me any good, if the form is not filtered down.   Thanks for any help.  It is so much appreciated.
ASKER CERTIFIED SOLUTION
PatHartman

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question