Recordsource, recordset and filter (A2K SR1)

Topic

New Reply

When we want to get records that satisfy a certain criterion, we can formulate the SQL statement for it.
I understand that we can achieve the above with one of the following strategies:
(a) set the recordsource of the form that satisfies the SQL,
( get the recordset of the table that satisfies the SQL criterion, or
© use a filter similar to the WHERE clause of the SQL statement.

My question is – is there any differences amongst the three ways and what are their performance penalty?

Reply | Quote

Replies

( is different from (a) and ©: both (a) and © will cause the form to display only the filtered records. ( will only give you a recordset that can be manipulated in code – unless you set the RecordSource of the form, in which case it’s the same as (a).

In case (a) the records not satisfying the criteria are inaccessible until you change the RecordSource of the form again. In case © the user can turn off the filter to see all records – unless you have set the CanFilter property of the form to No.

I have the impression that changing the RecordSource is slightly more efficient – setting a filter seems to generate a lot of event handling. But in most cases, I don’t think you’ll notice the difference, and it probably depends on the design of your form (presence or absence of subforms, code in OnCurrent event etc.)

Reply | Quote

In my experience, it’s faster to change the recordsource than to manipulate filters on large recordsets.

Reply | Quote

Thanks for your explanation, HansV. My tables contains only about 1000 rows and it is rather small. I experimnted with it and couldn’t visibly find the difference. That’s why I seek help. Thanks again, and to Charlotte too.

Reply | Quote