Solved

Second Combo Box Filter

Posted on 2014-01-14
6
744 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 34

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 34

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 34

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

920 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now