From: QB on
1. If I understand properly. For a single form that is set to use as its
Record Source a table, it must load all the records in the table before
displaying the form.

2. What about form with subforms? Do the subforms do the same or do they
only load the data pertaining to the current link child master record?

I am interested in trying to optimize a form that has 20+ subforms and is
taking 10-20 seconds or so to load. I have read that I should remove the
default record source and populate the record source based on my lookup field
so it only has to access one record at a time. Does this make sense? What
else should I be trying to do to improve performance?

Thank you,

QB
From: Dorian on
You should definitely not have your main form load all the records in a table.
I'd have a search form upfront that takes search criteria and displays a
list of records matching the criteria. Then a button to show the details of
an individual record and on that details form some navigation buttons tro
scroll thru the found records one at a time.
If you have 20 subforms on a form, it seems excessive. Are all the subforms
relevant in all cases to every record on the form?
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"QB" wrote:

> 1. If I understand properly. For a single form that is set to use as its
> Record Source a table, it must load all the records in the table before
> displaying the form.
>
> 2. What about form with subforms? Do the subforms do the same or do they
> only load the data pertaining to the current link child master record?
>
> I am interested in trying to optimize a form that has 20+ subforms and is
> taking 10-20 seconds or so to load. I have read that I should remove the
> default record source and populate the record source based on my lookup field
> so it only has to access one record at a time. Does this make sense? What
> else should I be trying to do to improve performance?
>
> Thank you,
>
> QB
From: QB on
In this case, yes, there is a need for all the sub-form to get the full
picture for various tasks.

Thank you for confirming the proper approach!

QB




"Dorian" wrote:

> You should definitely not have your main form load all the records in a table.
> I'd have a search form upfront that takes search criteria and displays a
> list of records matching the criteria. Then a button to show the details of
> an individual record and on that details form some navigation buttons tro
> scroll thru the found records one at a time.
> If you have 20 subforms on a form, it seems excessive. Are all the subforms
> relevant in all cases to every record on the form?
> -- Dorian
> "Give someone a fish and they eat for a day; teach someone to fish and they
> eat for a lifetime".
>
>
> "QB" wrote:
>
> > 1. If I understand properly. For a single form that is set to use as its
> > Record Source a table, it must load all the records in the table before
> > displaying the form.
> >
> > 2. What about form with subforms? Do the subforms do the same or do they
> > only load the data pertaining to the current link child master record?
> >
> > I am interested in trying to optimize a form that has 20+ subforms and is
> > taking 10-20 seconds or so to load. I have read that I should remove the
> > default record source and populate the record source based on my lookup field
> > so it only has to access one record at a time. Does this make sense? What
> > else should I be trying to do to improve performance?
> >
> > Thank you,
> >
> > QB
From: Albert D. Kallal on
"QB" <QB(a)dicussions.microsoft.com> wrote in message
news:487B4D9C-978F-4616-B667-FCE50D0660D4(a)microsoft.com...
> 1. If I understand properly. For a single form that is set to use as its
> Record Source a table, it must load all the records in the table before
> displaying the form.
>

No, the above is incorrect, and is even incorrect for when using sql server.

so, if you launch the form with an where clause (or a filter), and on your
cheap-o office network the table has 500,000 records, and you pass the where
clause with one ID, then ONLY the one record is pulled down the network
wire.

So

docmd.Openform "frmCustomer",,,"id = 34642"

This means you can safely use bound forms to the large table (or query -
makes no difference performance wise) as you have now. So, in the above
example the above from customer is bound, but ONLY the one record comes down
the network connection. this is the cause for a file share or when using
odbc to sql server.


> 2. What about form with subforms? Do the subforms do the same or do they
> only load the data pertaining to the current link child master record?

Only the correct records pertaining to the current parent ID will come down
the wire.

> I have read that I should remove the
> default record source and populate the record source based on my lookup
> field
> so it only has to access one record at a time. Does this make sense?

No, it does not make sense, and it is the wrong advice.

What you do is place the sub-forms behind tab controls and simply don't load
the sub-form at all until the tab is clicked on. That way, if you have 1, or
2000 sub-forms the form load time will be the same. In this case, you will
set the source object of the sub-form control to the form you want to view.
Thus, no records for any sub-form not being viewed will not be transferred.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal(a)msn.com

From: QB on
I understand your previous post, and thank you for explicitly explaining
certain aspects for me!

This brings up a question, probably a bad approach on my behalp. Right now,
when the user click on the button to open the form, it open the form and then
goes to a new record. So in fact, I do not filter to a new record (If I
understand correctly, with my current method it is loading all the records,
then goinf to a new one).

Since this is not the right approach, how could I load the form on a new
record without loading all the records? I can't filter for a non-existant
rec id in this case.

Also, assuming I open the form as you mentioned using a WHERE clause, how
then do I allow the user to move to another record using a cbo to make his
selection from? Do you close the form and reopen it with the new WHERE
clause or is there a simpler approach that I am missing?

Thank you for enlightening me.... again! And thank you for your time.

QB




"Albert D. Kallal" wrote:

> "QB" <QB(a)dicussions.microsoft.com> wrote in message
> news:487B4D9C-978F-4616-B667-FCE50D0660D4(a)microsoft.com...
> > 1. If I understand properly. For a single form that is set to use as its
> > Record Source a table, it must load all the records in the table before
> > displaying the form.
> >
>
> No, the above is incorrect, and is even incorrect for when using sql server.
>
> so, if you launch the form with an where clause (or a filter), and on your
> cheap-o office network the table has 500,000 records, and you pass the where
> clause with one ID, then ONLY the one record is pulled down the network
> wire.
>
> So
>
> docmd.Openform "frmCustomer",,,"id = 34642"
>
> This means you can safely use bound forms to the large table (or query -
> makes no difference performance wise) as you have now. So, in the above
> example the above from customer is bound, but ONLY the one record comes down
> the network connection. this is the cause for a file share or when using
> odbc to sql server.
>
>
> > 2. What about form with subforms? Do the subforms do the same or do they
> > only load the data pertaining to the current link child master record?
>
> Only the correct records pertaining to the current parent ID will come down
> the wire.
>
> > I have read that I should remove the
> > default record source and populate the record source based on my lookup
> > field
> > so it only has to access one record at a time. Does this make sense?
>
> No, it does not make sense, and it is the wrong advice.
>
> What you do is place the sub-forms behind tab controls and simply don't load
> the sub-form at all until the tab is clicked on. That way, if you have 1, or
> 2000 sub-forms the form load time will be the same. In this case, you will
> set the source object of the sub-form control to the form you want to view.
> Thus, no records for any sub-form not being viewed will not be transferred.
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal(a)msn.com
>