Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Second Combo Box Filter

Posted on 2014-01-14
6
Medium Priority
?
782 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 39

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 39

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 39

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

609 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