so*********@hotmail.com (John) wrote in
<90**************************@posting.google.com >:
So, I was wondering if using a query based recordset where the
query parameter criteria is based on a combobox is any quicker in
opening a form than using the standard wizard code (filter).
Well, this will all depend on how many records are returned by your
recordsource, how many by the filter, how big the PK index is and
how many records there are in the table.
Opening a form with DoCmd.OpenForm and using the filter argument is
just as fast as applying a filter on a form already open with all
records loaded in terms of the time it takes to load the data.
Loading the form definition itself is, of course, going to take
exactly the same amount of time.
My usual method is to do this:
1. the form has a recordsource that returns a blank, uneditable
record. I usually do this with a TOP 1 query on the smallest table
in the front end, and return NULL for all the fields that are
bound. A sample would look something like this:
SELECT TOP 1 Null As PersonID, Null As LastName, Null As
FirstName FROM tblSmallTable;
This accomplishes several things:
1. your controls can be bound.
2. the form is uneditable until you load a record.
3. since it's based on a small table, it's fast.
Once the form is loaded, you need some method for loading a record,
such as your combo box. However, instead of using bookmark
navigation to move to the record chosen in the combo box, instead
assign the recordsource to the form with a WHERE clause that
restricts the results to the one record.
I often also use text boxes instead of a combo box so the user can
type in a partial match and return more than one record. For
example, for looking up people's names, I'd have a FIND text box
and if someone puts in "Fen, D" it will return records with this
WHERE clause:
WHERE LastName Like "Fen*" And FirstName Like "D*"
It does mean that the LastName field can't have a comma in it. If
you need that, you could have separate LastName and FirstName
lookup textboxes. But I've used a single one in dozens of apps and
clients really like the ease of use.
This kind of approach scales extremely well and is fast. I've got
it working in apps with a range of a few dozen to 370K records in
the main table from which records are being returned. And these are
Access apps loading Jet data in a multi-user environment.
Speed is simply not an issue for these kinds of lookups.
As you well imagine, loading 370K records off the bat and then
filtering *would* be a major problem, and that's why I generally no
longer use bookmark navigation.
--
David W. Fenton
http://www.bway.net/~dfenton
dfenton at bway dot net
http://www.bway.net/~dfassoc 
