Feedback on VBA for Opening Forms

[Bill]

I guess this isn't your typical question on how to do something. I was just
curious about a method I started using to open up forms. I just recently
"discovered" this and wonder if anyone out there knows if this method is
good, bad, makes no difference or whatnot.

Basically I got the idea to set every form with the DataEntry property to
True. Then whenever I open a form I specify on the fly the RecordSource
through code, usually a SQL statement for the specific record or records the
user is looking for and then set DataEntry to False and refreshing. My
thinking is that this is faster than setting the forms Recordsource to a
table or query and then opening the form with a where clause to filter out
the records that they are looking for. My reasoning behind thinking this
(based on absolutely nothing) is that Access doesn't pull the entire set of
the query or table over the pipe and then filter the records locally. It
only takes the records it needs. Does anyone else do this? Would this
truly be a quicker and less bandwidth intensive way of opening up a form?
Unfortunately I don't have a database of enough size to test it out. Any
feedback would be appreciated.

TIA,
Bill

 

[Larry Linson]

You are correct that replacing the Form's RecordSource in code, provided you
have properly indexed your tables on the fields you are using as criteria is
more efficient at retrieving Access data than using a WhereCondition
argument. (The Form does not have to be created in DataEntry mode, but can
be created with a Query that simply lacks the WHERE clause.)

In practice, however, there's not likely to be a discernable performance
difference unless you are dealing with very large numbers of records, and
the data is coming across the network.

Larry Linson
Microsoft Access MVP