Filter Records with Multiple Combo Boxes

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dewey - 22 Dec 2005 16:30 GMT

I have a form that displays project details and includes a couple combo boxes
for filtering records. One of the combos filters records by project manager
and one filters by project area (eg. research, communications, education,
etc.). The AfterUpdate event of each box is similar to the following:

Private Sub cboFilterManager_AfterUpdate()
Me.Filter = "[strProjectManager] = '" & cboFilterManager.Text & "'
Me.FilterOn = True
End Sub

I would like to:
a.) Have each combo box include the other's criteria if a value has been
selected, so that the user could sort by project area and then further sort
by a particular project manager in that area - and vice versa.

b.) Filter each combo box by the other if a selection has been made. Thus,
if a project area filter is selected, only the managers involved in that
project area will appear in the project manager combo box - and vice versa.

It's the "vice versa" parts that I'm really unsure about.

Thanks in advance, and thanks again to all those who provide assistance
here. If it weren't for you, I'd be even more clueless than I am now.

Signature

J. Mullenbach

Reply to this Message

TC - 23 Dec 2005 14:12 GMT

I'd do it like this. Change the control/field names to suit.

(untested)

' module level declarations:
private gThis as string
private gThat as string
private gTother as string

Private Sub This_AfterUpdate()
gThis = trim$(me![This])
ReFilter
End Sub

Private Sub That_AfterUpdate()
gThat = trim$(me![That])
ReFilter
End Sub

Private Sub Tother_AfterUpdate()
gTother = trim$(me![This])
ReFilter
End Sub

private sub ReFilter()
dim s as string
if gThis <> "" then s = " AND ([This]=""" & gThis & """)"
if gThat <> "" then s = s & " AND ([That]=""" & gThat & """)"
if gTother <> "" then s = s & " AND ([Tother]=""" & gTother & """)"
if s = "" then
me.filteron = false
else
me.filter = mid$( s, 6)
me.filteron = true
endif
end sub

The abivge is a useful technique for building criteria strings when you
do not know how many of the criteria (if any) have been set. Note how
even the first string is given a leading " AND ". Thus, if the string s
is not empty, you know for sure that it starts with the 5 characters "
AND ", regardless of which criteria were or were not present - so you
just strip0 those leading characters, and you are left with a proper
boolean expression for use in the Filter prop.

HTH,
TC

Reply to this Message

Dewey - 23 Dec 2005 16:57 GMT

Thanks for the advice TC. Everything seems to work correctly up to the point
where the filter is actually applied - then I get a run-time error.

I left everything the same except the "ReFilter()" procedure, which I
entered as follows (keeping with your example):

Private Sub ReFilter()
Dim s As String
If gThis <> "" Then
s = " AND ([This]= """ & gThis & """)"
Else
If gThat <> "" Then
s = s & " AND ([That]= """ & gThat & """)"
End If
End If

If s = "" Then
Me.FilterOn = False
Else
Me.Filter = Mid$(s, 6)
Me.FilterOn = True
End If
End Sub

I think everything works correctly until the "Me.Filter = Mid$(s, 6)" line.
When I check the value of 's' in the Immediate window just before stepping
into the Me.Filter line, the following is returned:
And ([This]= "selection")
If I check the value of 's' afterwards (just before the Me.FilterOn
execution) the value is still the same as above. Then I get a prompt to
supply a parameter for [This] followed by a run-time error.

Any ideas?

Jason

Signature

J. Mullenbach

> I'd do it like this. Change the control/field names to suit.
>
[quoted text clipped - 43 lines]
> HTH,
> TC

Reply to this Message

TC - 24 Dec 2005 02:57 GMT

Well, first up, your recoding is wrong, if you want to be able to apply
both filter values at the same time. My code carefully allowed that.
Your changes have taken that ability away, so the code no longer
complies wth your original; description of what you wanted to do!

Put it back the way I suggested. Then change this:

me.filter = mid$(s, 6)

to this

s = mid$(s, 6)
debug.print ">"; s; "<"
me.filter = s

and try again. If it doesn't work, be sure to cut & paste the code
verbatim, so I can see what other changes you might have made!

HTH,
TC

Reply to this Message

Dewey - 27 Dec 2005 16:39 GMT

I must have copied your code incorrectly the first time because I received an
"expected end of block" error when I attempted to compile. That's what
prompted me to try to "fix" things...

Anyway, I re-copied your code as exactly as possible and added the change
you recommended. I didn't receive an error this time - but nothing happens
when I make a selection in either of the combo boxes. I tried adding the
"Me.FilterOn = True" line to the end of the ReFilter procedure, but I got a
Run-time error 2185: "You can't reference a property or method for a control
unless the control has the focus".

Here is the code I'm using:

Private Sub cboFilterManager_AfterUpdate()
gFilterManager = Trim$(Me![cboFilterManager])
ReFilter
End Sub

Private Sub cboFilterCommittee_AfterUpdate()
gFilterCommittee = Trim$(Me![cboFilterCommittee])
ReFilter
End Sub

Private Sub ReFilter()
Dim s As String
If gFilterManager <> "" Then s = " And ([cboFilterManager]=""" &
gFilterManager & """)"
If gFilterCommittee <> "" Then s = s & " And ([cboFilterCommittee]=""" &
gFilterCommittee & """)"
If s = "" Then
Me.FilterOn = False
Else
s = Mid$(s, 6)
Debug.Print ">"; s; "<"
Me.Filter = s
End If
End Sub

The following lines are present in the declarations section:
Private gFilterManager As String
Private gFilterCommittee As String

Thanks again for your help.

Signature

J. Mullenbach

> Well, first up, your recoding is wrong, if you want to be able to apply
> both filter values at the same time. My code carefully allowed that.
[quoted text clipped - 16 lines]
> HTH,
> TC

Reply to this Message

Dewey - 27 Dec 2005 17:00 GMT

Oops. I see now that I wasn't supposed to remove the Me.FilterOn = True line
from your code, but just replace the line above with your recommendation.
Sorry, I'm not usually this dim...

With all of your code entered as it's supposed to be, I get the run-time
error described in my previous post.

Signature

J. Mullenbach

> I must have copied your code incorrectly the first time because I received an
> "expected end of block" error when I attempted to compile. That's what
[quoted text clipped - 60 lines]
> > HTH,
> > TC

Reply to this Message

Dewey - 28 Dec 2005 18:37 GMT

I was able to solve the run-time error. The problem (I think) was due to the
fact that the combo boxes I'm using for filters are both unbound. In order
to apply a filter, the values in the unbound boxes needed to be assigned to a
bound field. Also, without the ".Text" qualifier added to the field property
in each of the AfterUpdate events, the values returned were numerical. I
used the following code to correct the problem:

Private Sub cboFilterManager_AfterUpdate()
gFilterManager = Trim$(Me![cboFilterManager].Text)
ReFilter
End Sub

Private Sub cboFilterCommittee_AfterUpdate()
gFilterCommittee = Trim$(Me![cboFilterCommittee].Text)
ReFilter
End Sub

Private Sub ReFilter()
Dim s As String
If gFilterManager <> "" Then s = " And ([strProjectManager1] = """ &
gFilterManager & """)"
If gFilterCommittee <> "" Then s = s & " And ([strCommittee] = """ &
gFilterCommittee & """)"
If s = "" Then
Me.FilterOn = False
Else
s = Mid$(s, 6)
Debug.Print ">"; s; "<"
Me.Filter = s
Me.FilterOn = True
End If
End Sub

Now I'm able to filter using both combo boxes. But here's the new
problem.... I use the following code to clear the filters after the first
application:

Private Sub cmdClearFilter_Click()
Me.Filter = ""
Me.FilterOn = False
cboFilterManager.Value = ""
cboFilterCommittee.Value = ""
End Sub

However, when I choose a new value in one of the combo boxes, the
debug.print from the ReFilter procedure shows that the other box still
retains the previous filter value. Any ideas on how to properly clear the
filters between applications?

Thank you.

Signature

J. Mullenbach

> Oops. I see now that I wasn't supposed to remove the Me.FilterOn = True line
> from your code, but just replace the line above with your recommendation.
[quoted text clipped - 67 lines]
> > > HTH,
> > > TC

Reply to this Message

Dewey - 29 Dec 2005 18:00 GMT

I figured out how to properly clear the filters between applications... The
problem was due to the variables 'gFilterManager' and 'gFilterCommittee' not
being cleared after each execution of the ReFilter procedure.

I added the following lines to the end of the procedure and now everything
works correctly:
gFilterManager = vbNullString
gFilterCommittee = vbNullString

Signature

J. Mullenbach

> I was able to solve the run-time error. The problem (I think) was due to the
> fact that the combo boxes I'm using for filters are both unbound. In order
[quoted text clipped - 118 lines]
> > > > HTH,
> > > > TC

Reply to this Message