By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,398 Members | 1,075 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,398 IT Pros & Developers. It's quick & easy.

Filters

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

Private Sub txt_FilterByWhat_Change()

DoCmd.ShowAllRecords
If (Me.FilterByWhere = "") Or IsNull(Me.FilterByWhere) Then
Me.Filter = "Type ='" & Me.txt_FilterByWhat & "'"
Me.FilterOn = True
DoCmd.Requery
Else
Me.Filter = "Type ='" & Me.txt_FilterByWhat & "'" & "and Where ='" &
Me.FilterByWhere & "'"
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_Change()

DoCmd.ShowAllRecords
If (Me.txt_FilterByWhat = "") Or IsNull(Me.txt_FilterByWhat) Then
Me.Filter = "Type ='" & Me.FilterByWhere & "'"
Me.FilterOn = True
DoCmd.Requery
Else
Me.Filter = "Type ='" & Me.txt_FilterByWhat & "'" & "and Where ='" &
Me.FilterByWhere & "'"
Me.FilterOn = True
DoCmd.Requery
End If

End Sub
Aug 22 '08 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Did you name one control "txt_FilterByWhat" and the other
"FilterByWhere" 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...@hotmail.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_FilterByWhat_Change()

DoCmd.ShowAllRecords
If (Me.FilterByWhere = "") Or IsNull(Me.FilterByWhere) Then
Me.Filter = "Type ='" & Me.txt_FilterByWhat & "'"
Me.FilterOn = True
DoCmd.Requery
Else
Me.Filter = "Type ='" & Me.txt_FilterByWhat & "'" & "and Where ='" &
Me.FilterByWhere & "'"
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_Change()

DoCmd.ShowAllRecords
If (Me.txt_FilterByWhat = "") Or IsNull(Me.txt_FilterByWhat) Then
Me.Filter = "Type ='" & Me.FilterByWhere & "'"
Me.FilterOn = True
DoCmd.Requery
Else
Me.Filter = "Type ='" & Me.txt_FilterByWhat & "'" & "and Where ='" &
Me.FilterByWhere & "'"
Me.FilterOn = True
DoCmd.Requery
End If

End Sub
Aug 22 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.