Good Design Generally - Filter or Change RecordSource?

[Ian Chappel]

If manipulating by code, and both methods are possible, is it generally
better practice to change the form's RecordSource, or add filters?

I generally have been changing RecordSource, but I think this is because I
started doing so to "filter" ComboBox's RecordSources. I know all situations
are unique! Maybe underneath it all, adding filters does change the
RecordSource anyway?

 

[Allen Browne]

Both are useful, Ian.

In general, I use filters when:
a) the user should be able to remove them easily.

b) the filter is a temporary way of locating data or narrowing searches.

c) other code relies on removing the filter to find a record in the form.

I change the RecordSource when:
a) The user must not remove the filter (e.g. where a table contains records
for multiple users, and a user must not be permitted to see others' records)

b) You want to filter both a form and subform, but need to avoid this bug:
http://allenbrowne.com/bug-02.html

c) The result would be more efficient, e.g. using an INNER JOIN rather than
a subquery in the filter. Example:
http://allenbrowne.com/ser-28.html

d) There are hundreds of thousands of records.
Access 97 particularly is still unreliable under some circumstances when you
use bookmarks with large numbers of records. (Typically we alter the
RecordSource to a single record.)

 

[Ian Chappel]

Thanks Allen,

I think I've probably been doing the right thing using RecordSource in my
applications. I actually questioned this after reading an article on your
website (can't remember which one) which had code building up complex
filters. But I wasn't even considering the obvious problem of users removing
filters when they shouldn't be.

I was really asking from an efficiency aspect - from your point "d)", I
assume then that RecordSource [always?] is more efficient?

 

[Guest]

Allen,
This bring to mind a question then. All other things being equal, regarding
filtering or chaning record source, does either have a performance advantage?

 

[Allen Browne]

Ian (and Klatuu), I can't give you a categorical answer re performance.
There are just too many factors.

But it does seem that Access is not merely pulling everything and then
applying the filter on the results: it seems to be using more intelligent
fetching than that. Consequently, there is little practical difference in
performance either way, for most scenarios.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen,

I think I've probably been doing the right thing using RecordSource in my
applications. I actually questioned this after reading an article on your
website (can't remember which one) which had code building up complex
filters. But I wasn't even considering the obvious problem of users
removing filters when they shouldn't be.

I was really asking from an efficiency aspect - from your point "d)", I
assume then that RecordSource [always?] is more efficient?

Both are useful, Ian.

In general, I use filters when:
a) the user should be able to remove them easily.

b) the filter is a temporary way of locating data or narrowing searches.

c) other code relies on removing the filter to find a record in the form.

I change the RecordSource when:
a) The user must not remove the filter (e.g. where a table contains
records for multiple users, and a user must not be permitted to see
others' records)

b) You want to filter both a form and subform, but need to avoid this
bug:
http://allenbrowne.com/bug-02.html

c) The result would be more efficient, e.g. using an INNER JOIN rather
than a subquery in the filter. Example:
http://allenbrowne.com/ser-28.html

d) There are hundreds of thousands of records.
Access 97 particularly is still unreliable under some circumstances when
you use bookmarks with large numbers of records. (Typically we alter the
RecordSource to a single record.)

 

[Guest]

That's what I suspected. Thanks.
--
Dave Hargis, Microsoft Access MVP

Ian (and Klatuu), I can't give you a categorical answer re performance.
There are just too many factors.

But it does seem that Access is not merely pulling everything and then
applying the filter on the results: it seems to be using more intelligent
fetching than that. Consequently, there is little practical difference in
performance either way, for most scenarios.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen,

I think I've probably been doing the right thing using RecordSource in my
applications. I actually questioned this after reading an article on your
website (can't remember which one) which had code building up complex
filters. But I wasn't even considering the obvious problem of users
removing filters when they shouldn't be.

I was really asking from an efficiency aspect - from your point "d)", I
assume then that RecordSource [always?] is more efficient?

Both are useful, Ian.

In general, I use filters when:
a) the user should be able to remove them easily.

b) the filter is a temporary way of locating data or narrowing searches.

c) other code relies on removing the filter to find a record in the form.

I change the RecordSource when:
a) The user must not remove the filter (e.g. where a table contains
records for multiple users, and a user must not be permitted to see
others' records)

b) You want to filter both a form and subform, but need to avoid this
bug:
http://allenbrowne.com/bug-02.html

c) The result would be more efficient, e.g. using an INNER JOIN rather
than a subquery in the filter. Example:
http://allenbrowne.com/ser-28.html

d) There are hundreds of thousands of records.
Access 97 particularly is still unreliable under some circumstances when
you use bookmarks with large numbers of records. (Typically we alter the
RecordSource to a single record.)

"Ian Chappel" <ichappAThotmailDOTcoDOTuk> wrote in message
If manipulating by code, and both methods are possible, is it generally
better practice to change the form's RecordSource, or add filters?

I generally have been changing RecordSource, but I think this is because
I started doing so to "filter" ComboBox's RecordSources. I know all
situations are unique! Maybe underneath it all, adding filters does
change the RecordSource anyway?

 

[David W. Fenton]

But it does seem that Access is not merely pulling everything and
then applying the filter on the results: it seems to be using more
intelligent fetching than that. Consequently, there is little
practical difference in performance either way, for most
scenarios.

I think there's some secret magic in the code for linked child
subforms, and the same magic code is used for filtering, because it
seems to be remarkably efficient.

 

[David W. Fenton]

All other things being equal, regarding
filtering or chaning record source, does either have a performance
advantage?

I would agree that there probably isn't any substantial difference
for the base form that you're filtering or changing the
recordsource. But there can be dependencies that come from there
that make a difference.

 

[Ian Chappel]

Thanks All - good to have opinions of those with much more experience
confirming that I'm on the right track!