Is it possible to filter ontop of the current filter?

[crispywafers]

Hi,

Is it possible to filter ontop of the current filter being applied to
records? This seems like it should be easy?

I have two drop down boxes-- one to filter on a student's last name,
one to filter on the current school year. They work seperatly, but not
together. I would like a user, if they want, to be able to apply both
filters at once -- (only a certain school year and only a certain
student). Help?

Current code:

First Drop Down box (filter on Last Name):

Private Sub Text67_Change()
If Me.Dirty Then 'Save first
Me.Dirty = False
End If
If IsNull(Me.Text67) Then 'Show all
Me.FilterOn = False
Else
Me.Filter = "[SLastName] Like """ & Me.Text67 & "*"""
Me.FilterOn = True
End If
End Sub
Second Drop Down Box (filter on school year):

Private Sub cmbFSchoolYear_Change()

If Me.Dirty Then 'Save first
Me.Dirty = False
End If
If IsNull(Me.cmbFSchoolYear) Then 'Show all
Me.FilterOn = False
Else
Me.Filter = "[SchoolYear] Like """ & Me.cmbFSchoolYear & "*"""
Me.FilterOn = True
End If

End Sub

[Ken Ismert]

Crispy,

Yes, its doable. You just need to combine the two filter clauses with
an "And". Do this in a separate subroutine in your form module:

Private Sub FilterMe()

Dim sFilter as String

sFilter = ""

If Me.Dirty Then 'Save first
Me.Dirty = False
End If

If Not IsNull(Me.Text67) Then
sFilter = "[SLastName] Like """ & Me.Text67 & "*"""
End If

If Not IsNull(Me.cmbFSchoolYear) Then
If Len(sFilter) > 0 Then
sFilter = sFilter & " And "
End If
sFilter = sFilter & "[SchoolYear] Like """ & Me.cmbFSchoolYear &
"*"""
End IF

If Len(sFilter) > 0 Then
Me.Filter = sFilter
Me.FilterOn = True
Else
Me.FilterOn = False
End IF

End Sub

This builds your filter string, adding an "And" if both filter
conditions are used. If neither filter control is set, it turns the
filter off.

-Ken

[crispywafers]

Ken,

Thank you for your help. I really appreciate it. That worked perfectly.
Thanks again!

[crispywafers]

Ken,

One last question. This works if they first filter on Last Name and
then choose from the school year menu...

Is it possible to also make it work if they decide to first filter on
School year and then last name?

So that both scenerios work?

[crispywafers]

Nevermind. I take that back.

Thanks!