Good Design Generally - Filter or Change RecordSource?

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? 

0
Ian
7/19/2007 2:10:25 PM
access.forms 6864 articles. 2 followers. Follow

7 Replies
1138 Views

Similar Articles

[PageSpeed] 21

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.)

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Ian Chappel" <ichappAThotmailDOTcoDOTuk> wrote in message 
news:euGnO6gyHHA.748@TK2MSFTNGP04.phx.gbl...
> 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? 

0
Allen
7/19/2007 2:19:20 PM
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?

"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message 
news:ebG%23N$gyHHA.312@TK2MSFTNGP04.phx.gbl...
> 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.)
>
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> "Ian Chappel" <ichappAThotmailDOTcoDOTuk> wrote in message 
> news:euGnO6gyHHA.748@TK2MSFTNGP04.phx.gbl...
>> 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?
> 

0
Ian
7/19/2007 3:44:08 PM
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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ian Chappel" <ichappAThotmailDOTcoDOTuk> wrote in message
news:Oc5$luhyHHA.5980@TK2MSFTNGP04.phx.gbl...
> 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?
>
> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message 
> news:ebG%23N$gyHHA.312@TK2MSFTNGP04.phx.gbl...
>> 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 
>> news:euGnO6gyHHA.748@TK2MSFTNGP04.phx.gbl...
>>> 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? 

0
Allen
7/19/2007 5:10:15 PM
That's what I suspected.  Thanks.
-- 
Dave Hargis, Microsoft Access MVP


"Allen Browne" wrote:

> 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
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> "Ian Chappel" <ichappAThotmailDOTcoDOTuk> wrote in message
> news:Oc5$luhyHHA.5980@TK2MSFTNGP04.phx.gbl...
> > 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?
> >
> > "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message 
> > news:ebG%23N$gyHHA.312@TK2MSFTNGP04.phx.gbl...
> >> 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 
> >> news:euGnO6gyHHA.748@TK2MSFTNGP04.phx.gbl...
> >>> 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? 
> 
> 
0
Utf
7/19/2007 5:32:00 PM
"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in
news:O5qPueiyHHA.4824@TK2MSFTNGP02.phx.gbl: 

> 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                  http://www.dfenton.com/ 
usenet at dfenton dot com    http://www.dfenton.com/DFA/
0
David
7/19/2007 6:33:00 PM
Klatuu <Klatuu@discussions.microsoft.com> wrote in
news:27ECAD87-AD45-4E64-8F87-FB8B6AB2F5BE@microsoft.com: 

> 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. 

-- 
David W. Fenton                  http://www.dfenton.com/ 
usenet at dfenton dot com    http://www.dfenton.com/DFA/
0
David
7/19/2007 6:34:16 PM
Thanks All - good to have opinions of those with much more experience 
confirming that I'm on the right track! 

0
Ian
7/20/2007 11:06:03 AM
Reply:

Similar Artilces:

what is a good book
...

Inventory and Cost of Goods Sold Being Out of a Balance by a Penny
Hello: In GP 10.0 SP1, has anyone encountered an issue where Inventory and Cost of Goods Sold are out of balance by a penny upon posting Sales Order Processing invoices? It does not happen on every invoice or for every item. But, in the journal entry that results from posting as invoice, the client has to book the penny either to Inventory or Cost of Goods Sold in order to post the journal entry. From elementary accounting school days, aren't Inventory and Cost of Goods Sold supposed to equal? childofthe1980s Are lots in use with fractional unit sales? Multiple Units of Measure...

How do I change the font size of my list of emails?
I have a new computer and my Outlook is very, very small. Changing the screen resolution changes everything on my computer. I want to change the font size of my list of emails. ...

Design Checker Function Missing
With Publisher 2007, I am unable to locate the Design Checker function that can find and correct double spaces that follow a period. This valuable function was available with a previous Publisher edition. For a newsletter that I design, each article I receive is typed by a different person and they all continue to put double spaces after a period (based on the method typewriter typists had to use). Is this is function still in Publisher 2007 somewhere? Thank you. Jo Joy wrote: > With Publisher 2007, I am unable to locate the Design Checker function > that can find and correct double s...

At each change in data apply formula
Is there a function/ code that can look down a column and apply a formula when there is a change in data. i know there is the subtotal function but this does not work for my requirement. Using the following data I would want to automatically look at where the data changes (in column A) and apply a formula in another column (say Column B) for the same row. I would therefore expect to apply the formula as per the following example to cells B1, B4, B5 and B9 Col A row 1 AAA row 2 AAA row 3 AAA row4 BBB row5 CCC row6 CCC ro...

Update Is GOOD 03-26-10
Updating I Good!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! -- ~Joseph~ update bad bad service packgood "jack wilkinson" <josephturner65@hotmail.com> wrote in message news:7DA06527-731E-4B9B-A70F-192A5BC93EBD@microsoft.com... > Updating I Good!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! > > -- > ~jack wilkinson~ "jack wilkinson" <bargaindirect@clerk.com> kirjoitti viestiss´┐Ż:uyWmVmF0KHA.2512@TK2MSFTNGP05.phx.gbl... > update bad bad service packgood > "jack wilkinson" <josephturner65@hotmail.com> wrote in message >...

Worksheet Change Event #2
I saw a forum response a while back that will allow programming the cursor to move to certain cells as the user enters data. I cannot remember how that was done. Can someone refresh my memory? This time I will copy the information into my own little help file. -- DCSwearingen ------------------------------------------------------------------------ DCSwearingen's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21506 View this thread: http://www.excelforum.com/showthread.php?threadid=474829 The code may have used the worksheet_change event. Here's ...

Form different size in form view vs design view
Hello A newbie question...Why when I set my form size to 6 inches width in design view it fills the page in Form view? Thanks for any input! Steve On Sun, 6 Dec 2009 17:24:01 -0800, SteveZmyname <SteveZmyname@discussions.microsoft.com> wrote: The AutoResize property of the form should be set to Yes. -Tom. Microsoft Access MVP >Hello >A newbie question...Why when I set my form size to 6 inches width in design >view it fills the page in Form view? Thanks for any input! > >Steve Is it set to maximize on Open? Or possibly you have another form opene...

Change Contact List Location
Is there a way to change the location of Live Mail's Contact List from its buried existence on my system drive (C:) to my data drive (D:)? I moved my storage folder over there a long time ago. Thank you. "Jack Gillis" <XXXXXXXXXXXXX@widomaker.com> wrote in message news:OwbnmhtmKHA.5840@TK2MSFTNGP05.phx.gbl... > Is there a way to change the location of Live Mail's Contact List from > its buried existence on my system drive (C:) to my data drive (D:)? I > moved my storage folder over there a long time ago. You didn't mention your OS,...

Account numbers change
My financial mail stolen from mailbox - found in woods. I contacted broker/bank etc and now have new account numbers. I just connected to Fidelity to download statement.Money still has my old account info so - Money statement shows sold,sold,sold,sold,sold .... TRANSFERRED .... I called Fidelity to see how to handle it - they said to create new account ........ losing my history. No I'm not gonna do that ! What's the easiest way to do this ? To accept the download, then delete the sell,sell,sell,transfer download info and just change my account numbers ? Thanks Bobb In micros...

How do I change the default "Add to Outlook Contacts" Folder
I've got several contact folders in Outlook 2007 Business, Personal, School, Misc. When I get an e-mail from a new contact and right click on their address, in the list that appears the option "Add to Outlook Contacts" is available. Unfortunately it always adds the contact to my defualt main "contacts" list. I don't have the option of choosing the Personal or School list, nor can I seem to find a way to change the folder so it defaults to say business contacts all the time and not the main contact list. Is there a way to do this? No. To save to an al...

Changing Fiscal Year Settings
In the implementation document the Fiscal Year settings can now not be changed, I think I set this wrongly. Is there a way to change the template? Am I correct in thinking that the template has a direct effect on how the reports are produced? Currently the reports get printer annually and I would like them quarterly. James Fiscal year settings are somthing you look at in detail and know exactly what you are after as it is a one off setting that cannot be changed, as far as I am aware. hth Regards, Nathan "JamesE" wrote: > In the implementation document the Fiscal Year s...

CAyncSocket like class design problem
Hi, I am trying to write a wrapper class for Winsock SOCKET to use it in a non-mfc project. When I checked MFC CAyncSocket class code, I found that it provides a number of overridables like OnAccept, OnConnect, OnSend etc. But in the winsock documentation, I can=92t find any messages corresponding to these events. While analyzing the call stack of OnAccept, I found that a message with id 883(I think it=92s WM_SOCKET_NOTIFY) is received by an object of class CSocketWnd. From the parameters of this message a CAyncSocket is constructed and provided the override functionality. Is the WinSock SOC...

Change in Commssion Default from v1.4 to v2.0
Hi When I create a new item in So or HQ in VERSION 1.4 the default commission mode is set to Base the Items commission on the schedule set for the Sales Rep. In VERSION 2.0 the default commission mode is set to Do not allow commission for this item. Does anyone know if their is a way to change the default in VERSION 2.0 back to what is was in VERSION 1.4 so I don't have to change the items each time I set up new ones. thanks Greg Sorry - Meant VERSION 1.3 "Morcott" <greg@rtl-world.com> wrote in message news:OW2UiYCAIHA.5160@TK2MSFTNGP05.phx.gbl... > Hi > W...

And in case I don't see you,good afternoon,good evening,and good n
Well thanks for all the help and assistance over the year(s) group (seriously), you saved my butt more then a few times. That said I finally got my data all transferred to MySql and will not be looking back. As some of you may know/remember I've had serious issues with Excel and find it to be highly flawed in fundamental ways, despite any of the functionality built over that. I personally don't find it worth it given the level of frustration and, IMHO, sloppy core programming that makes day in and out use a nightmare. In any event that is MS's cross (and shame) ...

Months List: changing the case
I did a months list as it is teached in the book Financial Statements but I can't change the case of the month title. I mean, the date format is mmmm dd, yyyy and it is displayed like thi example: january 31, 2004 february 29, 2004 march, 31 2004 I would like to change the the first character to become a capita letter: January 31, 2004 February 29, 2004 March, 31 2004 I hope someone can help me! Thanks -- Message posted from http://www.ExcelForum.com The following is not a valid ate in any country. march, 31 2004 The others you should be able to format with the format you show by si...

Changing the words "Sales Receipt" to "Invoice"
I cannot find how to change the word at the top right of our Sales document to read "Invoice" rather than "Sales Receipt". We bill out as a receivable for most of our goods and there seems to be some confusion with our customers. Thanks in advance for your help. -- Ron Moore Changing Sales Order to Invoice There is a receipt variable called "Transaction.ReceiptTransactionName" (in <SUB name="PrintTransactionType"> of receipt.xml)which returns the "default" name for the transaction based on it's type. You can replace this w...

Rules Wizard changes rule
I am using Outlook 2002 with all current patches, and I have one html account and three POP accounts (let's call the POP accounts A, B and C). 'A' is my main account and it's messages go to the Inbox. 'B' is a secondary account and collects a lot of spam. 'C' is my work account. I want to route all incoming messages from the 'B' account to the 'B' folder and all the incoming messages from the 'C' account to the 'C' folder. I have rules that do this this simple task, only when saving the 'C' rule, the Rules Wizard ...

How to change a transfer to a charge
I accidentally defined a number of credit card transactions as transfers to a cash account. The problem is, Money 204 will not allow me to simply edit the credit card transactions to re-define them as charges as opposed to transfers. When I try editing the "To" account, I get an error message... "If you do not want a transfer, click Cancel and then choose a different type of transaction. No matter what I click, the transaction remains a transfer. The credit card transactions are reconciled but the cash account is not. I expect this should be simple but short of deleting the tr...

Error -2147023545 when trying to change password in OWA2003
When trying to change the password for a user through SSL enabled OWA 2003, it results in the following error: Error number: -2147023545 Server is running Exchange 2003 on Windows 2003 with a FE/BE setup. Any tips and pointers would be much appreciated. Thanks. Chin ...

Inventory and Cost of Goods
Is there a way that when you add new item you can set the cost of the item you sell to a percentage? For example I have a barcode my employee's scan for a money order and they are prompted to enter a price. Can I set up the cost to be 100% of what ever amount they enter? Aslo is there a way that I can setup a barcode for lottery that they get from the lottery machine and when they ring it up it will not effect the phisyical inventory or cost of Goods? We want to use the COST of GOODS on the Z report to balance with our physical inventory not our virtual unlimited inventory that w...

Change the Default Language Setting
Is it possible to set the language to 'English (Australia)' so it will be the default across all office applications? I keep having to change it manually in outlook at the moment. ...

Please, give me some links on the good dating sites.
Your welcome everyone, Can you help me to find popular dating sites. On Tipe: http://www.yahoo.com, please. Thx, CholocopPoone -- CholocopPoone Why would anyone post this question in an Office newgroup??? -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "CholocopPoone" <CholocopPoone.5d39500@officefrustration.com> wrote in message news:CholocopPoone.5d39500@officefrustration.com... > > Your welcome everyone, > Can you help me to find popular dating sites. > On Tipe: http...

Changing the Insertion Point Colour
I have powerpoint 2007 and have decided to use a background with a general grey colour. Unfortunately this makes the insertion point cursor really difficult to see. Is there any way of changing the colour of this so it becomes more visible with this background? Try the Vippy Cursor at: http://www.ghacks.net/2006/07/20/vippy-a-writer-friendly-cursor/ I have it installed on Win 7. However, I cannot find it in my Start menu or in Control Panel>Programs listing. Gordo "Mad Highlander" <Mad Highlander@discussions.microsoft.com> wrote in message news:AF...

Date Format Change
I'm running Money 2004 with Win XPPro. In the registers, I somehow got the date format as day/month/year. I'd like month/day/year. Could anyone please tell me how to change the date format. Money's Help files weren't helpful for me. Thanks Mike In microsoft.public.money, Mike Fox wrote: >I'm running Money 2004 with Win XPPro. In the registers, I somehow >got the date format as day/month/year. I'd like month/day/year. >Could anyone please tell me how to change the date format. Money's >Help files weren't helpful for me. Start->Settings...