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

Q: What is the most efficient/fastest way to open a form?

P: n/a
Just a general question...

I'm currently using a combobox that when updated, opens a form with
its recordset based on a query using the combo box value as the
criteria.

I'm I correct in thinking that using:
docmd.openfrm "frmName",,,where "[ID]=" & cboSelectID
will open all records and then just navigate to that filtered record,
which is not as fast/efficient as using a query where the criteria is
set by a combo box?
Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Unless the form is already open, the code:
Docmd.Openform "frmName",,,"[ID]=" & cboSelectID
opens the form with a Filter applied, so it contains only the one record (or
none if there is no match).

You can remove the filter to see the other records:
Me.FilterOn = False

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"John" <so*********@hotmail.com> wrote in message
news:90**************************@posting.google.c om...
Just a general question...

I'm currently using a combobox that when updated, opens a form with
its recordset based on a query using the combo box value as the
criteria.

I'm I correct in thinking that using:
docmd.openfrm "frmName",,,where "[ID]=" & cboSelectID
will open all records and then just navigate to that filtered record,
which is not as fast/efficient as using a query where the criteria is
set by a combo box?

Nov 12 '05 #2

P: n/a
Allen, my question might need some clarification. I know the filter
opens the entire recordset and then has the form display the filtered
record. My question though relates to the speed of opening a form,
essentially.

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

-John

"Allen Browne" <ab***************@bigpond.net.au> wrote in message news:<Go*********************@news-server.bigpond.net.au>...
Unless the form is already open, the code:
Docmd.Openform "frmName",,,"[ID]=" & cboSelectID
opens the form with a Filter applied, so it contains only the one record (or
none if there is no match).

You can remove the filter to see the other records:
Me.FilterOn = False

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"John" <so*********@hotmail.com> wrote in message
news:90**************************@posting.google.c om...
Just a general question...

I'm currently using a combobox that when updated, opens a form with
its recordset based on a query using the combo box value as the
criteria.

I'm I correct in thinking that using:
docmd.openfrm "frmName",,,where "[ID]=" & cboSelectID
will open all records and then just navigate to that filtered record,
which is not as fast/efficient as using a query where the criteria is
set by a combo box?

Nov 12 '05 #3

P: n/a
You can run some timing tests on your own data, but in general, it's going
going to make much difference.

There are some advantages to opening the form so it only has one (or no)
record, and reassigning its RecordSource property so it only ever has one.
Filtering may be simpler if you load them all.

A well designed database will appear to load a form instantly if the first
record is available (i.e. there is an index on the fields used for criteria
or sorting). The remainder will load in "spare time", and when they are all
loaded, you will finally see the total count in the Nav Buttons. The user
can start working well before the final count is displayed. (This applies to
attached tables, or a form based on a query/sql statement.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"John" <so*********@hotmail.com> wrote in message
news:90**************************@posting.google.c om...
Allen, my question might need some clarification. I know the filter
opens the entire recordset and then has the form display the filtered
record. My question though relates to the speed of opening a form,
essentially.

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

-John

"Allen Browne" <ab***************@bigpond.net.au> wrote in message

news:<Go*********************@news-server.bigpond.net.au>...
Unless the form is already open, the code:
Docmd.Openform "frmName",,,"[ID]=" & cboSelectID
opens the form with a Filter applied, so it contains only the one record (or none if there is no match).

You can remove the filter to see the other records:
Me.FilterOn = False

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"John" <so*********@hotmail.com> wrote in message
news:90**************************@posting.google.c om...
Just a general question...

I'm currently using a combobox that when updated, opens a form with
its recordset based on a query using the combo box value as the
criteria.

I'm I correct in thinking that using:
docmd.openfrm "frmName",,,where "[ID]=" & cboSelectID
will open all records and then just navigate to that filtered record,
which is not as fast/efficient as using a query where the criteria is
set by a combo box?

Nov 12 '05 #4

P: n/a
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
Nov 12 '05 #5

P: n/a
RE/
Allen, my question might need some clarification. I know the filter
opens the entire recordset and then has the form display the filtered
record. My question though relates to the speed of opening a form,
essentially.

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


My typical UI paradigm is a listbox on the left side of the form that shows all
records in the DB and detail fields on the right side of the form that only show
info for the record whose row is currently selected in the listbox.

Therefore I have the option of opening up the form with no data at all loaded
and waiting for the user to walk the list.

Actually, I usually pre-position the list so that the first record is selected
and loaded - gives the user a getter idea of what's going on.

Either way, forms like that open pretty quickly. For some reason, the size of
the listbox's list doesn't to have much impact one way or the other.
--
PeteCresswell
Nov 12 '05 #6

P: n/a
On 6 Oct 2003 23:53:25 -0700 in comp.databases.ms-access,
so*********@hotmail.com (John) wrote:
Just a general question...

I'm currently using a combobox that when updated, opens a form with
its recordset based on a query using the combo box value as the
criteria.

I'm I correct in thinking that using:
docmd.openfrm "frmName",,,where "[ID]=" & cboSelectID
will open all records and then just navigate to that filtered record,
which is not as fast/efficient as using a query where the criteria is
set by a combo box?


The only difference will be that using a query, the query will be
compiled (i.e. it's execution plan is saved) whereas using using the
where clause argument on OpenForm the execution plan of the
recordset's query will have to be calculated. The hit on the database
itself will be the same.

If you can tell the difference in execution time between using a saved
query and an ad-hoc one then your name is Data and you should be
concentrating on the LCARS system on the Enterprise and not fiddling
about with Access :-)

--
A)bort, R)etry, I)nfluence with large hammer.
Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.