Filters

[katie.liddle@hotmail.co.uk]

Hi guys,
I have written the following code to filter based on a selection from
a dropdown box, and it works:

Private Sub txt_FilterByWha t_Change()

DoCmd.ShowAllRe cords
If (Me.FilterByWhe re = "") Or IsNull(Me.Filte rByWhere) Then
Me.Filter = "Type ='" & Me.txt_FilterBy What & "'"
Me.FilterOn = True
DoCmd.Requery
Else
Me.Filter = "Type ='" & Me.txt_FilterBy What & "'" & "and Where ='" &
Me.FilterByWher e & "'"
Me.FilterOn = True
DoCmd.Requery
End If


End Sub

So I copied and pasted it, changing the fields around to produce the
following, but it doesn't work... any ideas?

Private Sub FilterByWhere_C hange()

DoCmd.ShowAllRe cords
If (Me.txt_FilterB yWhat = "") Or IsNull(Me.txt_F ilterByWhat) Then
Me.Filter = "Type ='" & Me.FilterByWher e & "'"
Me.FilterOn = True
DoCmd.Requery
Else
Me.Filter = "Type ='" & Me.txt_FilterBy What & "'" & "and Where ='" &
Me.FilterByWher e & "'"
Me.FilterOn = True
DoCmd.Requery
End If

End Sub

[lyle fairfield]

Re: Filters

Did you name one control "txt_FilterByWh at" and the other
"FilterByWh ere" without the "txt_" prefix?

When setting filters it may be more efficient to have one Private Sub,
say, FilterBy, which examines the state of the two controls and sets
the filter appropriately, and to call it from each of the two change
events. Once you have that working, the need for copying and the
inefficiency of two more or less identical procedures and two
opportunites for syntax errors are all removed.

Many developers would not set a filter but rather reset the the form's
recordsource to a dynamic query string. Depending on the database
engine and the location of the tables this might be more efficient in
terms of records loaded and processed.


On Aug 22, 6:04 am, katie.lid...@ho tmail.co.uk wrote:

Hi guys,
I have written the following code to filter based on a selection from
a dropdown box, and it works:
>
Private Sub txt_FilterByWha t_Change()
>
DoCmd.ShowAllRe cords
If (Me.FilterByWhe re = "") Or IsNull(Me.Filte rByWhere) Then
Me.Filter = "Type ='" & Me.txt_FilterBy What & "'"
Me.FilterOn = True
DoCmd.Requery
Else
Me.Filter = "Type ='" & Me.txt_FilterBy What & "'" & "and Where ='" &
Me.FilterByWher e & "'"
Me.FilterOn = True
DoCmd.Requery
End If
>
End Sub
>
So I copied and pasted it, changing the fields around to produce the
following, but it doesn't work... any ideas?
>
Private Sub FilterByWhere_C hange()
>
DoCmd.ShowAllRe cords
If (Me.txt_FilterB yWhat = "") Or IsNull(Me.txt_F ilterByWhat) Then
Me.Filter = "Type ='" & Me.FilterByWher e & "'"
Me.FilterOn = True
DoCmd.Requery
Else
Me.Filter = "Type ='" & Me.txt_FilterBy What & "'" & "and Where ='" &
Me.FilterByWher e & "'"
Me.FilterOn = True
DoCmd.Requery
End If
>
End Sub