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

Using the form's Filter property vs using a query's WHERE clause

Seth Schrock
Expert 2.5K+
P: 2,941
I have a search form that currently pulls up all the records at the beginning and then uses the form's filter property to filter down the results based on what is typed into the search boxes. As there are many records, it takes awhile to bring the form up along with all the records. My hope is to be able to use either the form's filter property or maybe just the WHERE clause in the query that makes it so that no records are returned when the form first opens and then starts to show up some records when the criteria is being entered. My question is which method would be better/faster? My first guess would be that it would be better to use the WHERE clause in the query so that no records would be returned at all and the way that I think of the form's filter is that all records are still retrieved, but only some are shown. I haven't read this anywhere. It is just my impression of how it would work. Can anyone confirm/deny this theory?
Nov 25 '13 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 5K+
P: 5,397

In anycase...
From what I understand:
If you use the form's filter property, the entire recordset is return and then the filter applied.
So in a FE with a local table or a small database (even if split) with a good infrastructure (hardware), there may not be any real difference.
If however, the table is located on the LAN, is large and/or the infrastructure is poor, then one may want to look at "late-binding" the recordset to the form or using a query to first limit the records and then the filter to show only those that need to be used/seen.
Nov 25 '13 #2

Seth Schrock
Expert 2.5K+
P: 2,941
It is split to a SQL Server database. Right now I have a Search-as-you-type functionality using the OnChange events of the search boxes, so I'm not sure at what point I can do the binding. Maybe I'll just have to make it so that it does the search in the AfterUpdate event instead. Either that or possibly do it so that while I'm editing one search box, the query is bound to the other one so that the query does some of the limiting of records returned, but I still get my functionality. Alright, I think that I have my answer. I'll leave this thread open though in case someone else has any insight.
Nov 25 '13 #3

Post your reply

Sign in to post your reply or Sign up for a free account.