Filtering via a combo box

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
smodsAsked:
Who is Participating?
 
Vadim RappConnect With a Mentor Commented:
Here's I think what you need.
Copy-of-ee5.mdb
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
smodsAuthor Commented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
smodsAuthor Commented:
Hi LSM,

Did you have a chance to look into this?

Regards

Chris
0
 
smodsAuthor Commented:
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
 
Vadim RappCommented:
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
 
smodsAuthor Commented:
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
 
smodsAuthor Commented:
Random! It's working now! Many thanks :)
0
 
Vadim RappCommented:
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
 
smodsAuthor Commented:
I know about the tab control and it would be ideal if you could alter the appearance of it!
0
 
Vadim RappCommented:
> 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
 
smodsAuthor Commented:
0
 
Vadim RappCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.