Solved

Filtering via a combo box

Posted on 2011-02-17
14
325 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
  • 7
  • 5
14 Comments
 
LVL 84
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
 
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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
 

Author Comment

by:smods
ID: 34961368
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

746 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