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
Solved

Second Combo Box Filter

Posted on 2014-01-14
6
756 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 35

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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 35

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 35

Accepted Solution

by:
PatHartman earned 500 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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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 a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

856 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