Solved

Filtering via a combo box

Posted on 2011-02-17
14
361 Views
Last Modified: 2012-05-11
Hi all,

Please see the attached DB and the form frm_KPI_Main

I've added a third combo box for the team leaders.  What I want to be able to do is filter the cashier combo box based on their Team Leader.  So TL 1 is selected then only the cashiers for that TL will appear on the cashier combo box.  

However I also want the first option in the TL box as All to display all cashiers in the cashier combo box and for all to be the default selection (the TL filter will be for convenience only)

Thanks

Chris
Copy-of-ee5.mdb
0
Comment
Question by:smods
[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
  • 7
  • 5
14 Comments
 
LVL 85
ID: 34915548
So you're looking for "cascading combos"? That is, when a user selects a value in Combo1, it should filter the items in Combo2?

If so, you can use the AfterUpdate event of Combo1 to set the RowSource of Combo2:

Sub Combo1_AfterUpdate()
  Me.Combo2.Rowsource = "SELECT Col1, Col2, Col3 FROM SomeTable WHERE SomeField=" & Me.Combo1.Column(1)
End Sub

If "SomeField" is a Text value:

  Me.Combo2.Rowsource = "SELECT Col1, Col2, Col3 FROM SomeTable WHERE SomeField='" & Me.Combo1.Column(1) & "'"

If "SomeField" is a Date value:

  Me.Combo2.Rowsource = "SELECT Col1, Col2, Col3 FROM SomeTable WHERE SomeField=#" & Me.Combo1.Column(1) & "#"

To show "All" in your combo, you can use a Union query:

Me.Combo2.Rowsource = "SELECT 0, 1, '<All>' FROM SomeTable UNION SELECT Col1, Col2, Col3 FROM SomeTable WHERE SomeField='" & Me.Combo1.Column(1) & "'"

The caveat to using UNION is that both SELECT statements MUST return the same number of fields.

You can then check to see if the user has selected "All":

If Me.Combo1.Column(1) = 0 Then
  '/user has selected ALL
  Me.Combo2.Rowsource = "SELECT 0, 1, '<All>' FROM SomeTable UNION SELECT Col1, Col2, Col3 FROM SomeTable"
Else
  Me.Combo2.Rowsource = "SELECT 0, 1, '<All>' FROM SomeTable UNION SELECT Col1, Col2, Col3 FROM SomeTable WHERE SomeField='" & Me.Combo1.Column(1) & "'"
End If





0
 

Author Comment

by:smods
ID: 34915664
So you're looking for "cascading combos"? That is, when a user selects a value in Combo1, it should filter the items in Combo2?
That is correct yes.

So they are text fields so looking at my example database it would be:

Private Sub TeamLeader_AfterUpdate()

Me.Cashier.RowSource = "SELECT Cashier FROM tbl_CashierDetails WHERE Team_Leader='" & Me.TeamLeader.Column(0) & "'"

End Sub

Open in new window


This works.

To show "All" in your combo, you can use a Union query:

Me.Combo2.Rowsource = "SELECT 0, 1, '<All>' FROM SomeTable UNION SELECT Col1, Col2, Col3 FROM SomeTable WHERE SomeField='" & Me.Combo1.Column(1) & "'"

The caveat to using UNION is that both SELECT statements MUST return the same number of fields.

You can then check to see if the user has selected "All":

If Me.Combo1.Column(1) = 0 Then
  '/user has selected ALL
  Me.Combo2.Rowsource = "SELECT 0, 1, '<All>' FROM SomeTable UNION SELECT Col1, Col2, Col3 FROM SomeTable"
Else
  Me.Combo2.Rowsource = "SELECT 0, 1, '<All>' FROM SomeTable UNION SELECT Col1, Col2, Col3 FROM SomeTable WHERE SomeField='" & Me.Combo1.Column(1) & "'"
End If

This bit has lost me completely.  Can you explain a little further?  Where would I put the code to check what the user has selected?

Also on a related note.  The TL combo is pulling values from the table tbl_CashierDetails but each TL appears more than once.  What can I do to the TL combo box so that each TL only appears once?

Regards

Chris
0
 

Author Comment

by:smods
ID: 34941589
Hi LSM,

Did you have a chance to look into this?

Regards

Chris
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 500 total points
ID: 34957767
Here's I think what you need.
Copy-of-ee5.mdb
0
 

Author Comment

by:smods
ID: 34959788
Thank you for this.  I'm just copying everything across.  I seem to be missing something though?  

When opening the form I get "Undefined Function 'SelectedTL' in expression."

0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 34960370
I just tried it once again in Access 2002 and 2003, and it worked. Maybe you are using 2007 and it hits some restriction.

You will find SelectedTL in the new module Module1, and it returns the currently-selected team leader from its listbox. It's then used in the criteria in the rowsource of Cashier listbox.

Public Function SelectedTL() As String
    On Error Resume Next
    Dim r As Recordset
    Set r = Forms!frm_kpi_main.TeamLeader.Recordset.Clone
    r.MoveFirst
    r.Move Forms!frm_kpi_main.TeamLeader.ListIndex
    SelectedTL = r(0)
End Function
0
 

Author Comment

by:smods
ID: 34960639
My bad! I didn't see the module!  I've added this but cashier combo box is blank now?

I've added the Cashier.Requery to the after update so am unsure as to what I have done wrong.  Your example works perfectly for me so I appear to have missed something!
0
 

Author Comment

by:smods
ID: 34960817
Random! It's working now! Many thanks :)
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 34961167
Two non-critical things I have noticed:

1. maybe it would be easier to use tab control instead of showing and hiding the frames.

2. in one place of the code I made it somewhat shorter by replacing some

If condition then
  boolean_variable=true
  else
  boolean_variable=false
end if

for simply

boolean_variable = condition

Take a look.
0
 

Author Comment

by:smods
ID: 34961262
I know about the tab control and it would be ideal if you could alter the appearance of it!
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 34961288
> it would be ideal if you could alter the appearance of it!

If you give more details (what's the problem?), I can take a look at that.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 34961921
Right, since the top portion of the tab control can't be customized, some workaround would be required anyways. Besides showing / hiding the controls, could also make it transparent, and hide the upper gray part with overlapping subform (textbox etc. don't want to go in front of tab control), but that still would leave the tab caption in default font. Another possibility might be in using win32 libraries to paint the window directly, even more complex. Two more, try to use Styles, or try to use more advanced tab control. So what you already have is probably the best. I personally wouldn't even think about building workarounds just to improve the looks, but it's my personal preference.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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 …

726 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