Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Second Combo Box Filter

Posted on 2014-01-14
6
Medium Priority
?
793 Views
Last Modified: 2014-01-20
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
0
Comment
  • 3
  • 3
6 Comments
 
LVL 40

Expert Comment

by:PatHartman
ID: 39780134
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
0
 
LVL 4
ID: 39787195
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!
0
 
LVL 4
ID: 39787210
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];
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 40

Expert Comment

by:PatHartman
ID: 39788504
You are referencing two combos in this query.  I would have expected it to reference only combo1.
0
 
LVL 4
ID: 39794731
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.
0
 
LVL 40

Accepted Solution

by:
PatHartman earned 2000 total points
ID: 39794984
Just as an example:
The query for combo2 references combo1 for its criteria.
The query for combo3 references combo2 for its criteria.

So - each combo refers ONLY to the preceding combo.  Of course, this presumes your tables are properly structured.  

Since you said you had only two combos, then combo2's RowSource query would reference ONLY combo1.  You can't include Location in the criteria since location hasn't yet been chosen.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Implementing simple internal controls in the Microsoft Access application.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question