Does loading additional forms drain resources?

[Paul]

I have two complex forms with multiple tabs and subforms that take from 6 to
10 seconds to load. This is after I've done everything I can think of to
improve performance - indexing query criteria fields, only putting fields I
actually use in the queries, turning off Name Autocorrect, etc.

I'm now thinking that in order to reduce the users' wait time to open these
forms, I could open them when the application first opens, but make them
invisible until the user clicks a button to "open" one of the forms, at
which time I would simply make it visible. When they click a command to
close one of the forms, VBA would hide the form again in case they wanted to
open it again later.

Does such an approach have a downside? That is, does keeping complex forms
open but invisible while users are performing other operations in the
application - does this create a drain on system resources, or can Access
easily handle a couple of open forms without any loss in performance for
other operations?

Thanks in advance,

Paul

 

[Albert D. Kallal]

Pre-loading forms does use up some resources. However, the real issue in
costs is your additional coding and developer time.

In most cases you should be able to get your form load time down to about
the one second range.

One great tip is to not load sub forms until you need them.

For example, if you have a main form with 12 sub forms, if you can place
each sub form behind a tab, then you can set this up so that when the form
loads only ONE sub-form loads (the one on the 1st tab).

By taking the above approach, then if you have a form with one sub form, or
twelve sub forms, the form load time is going to be the SAME in both cases.

When you click on a tab, then you can at runtime load the sub-form for the
tab just selected.

So if your form has quite a few tabs, then a great approach is to simply not
load of sub forms behind those other tabs.

You can use the following code to load a sub form what a person clicks on a
tab:


Private Sub tab1_Change()

Select Case tab1

Case 1

If Me.frmItineraryView.SourceObject = "" Then
Me.frmItineraryView.SourceObject = "frmItineraryView"

Do watch our for the link master/child settings. Often you need to set them
also. So, typical code looks like:


Me.frmOldRes.SourceObject = "frmActiveResA"
Me.frmOldRes.LinkChildFields = "ContactID"
Me.frmOldRes.LinkMasterFields = "ContactID"


I think the above is a better approach then the "pre-load" those forms idea.
If you pre-load forms, then your application startup time is going to be
that much longer anyway.

It is certainly possible that preloading forms is a solution here and the
tradeoff is some cost in additional resources. Computers today have mega
gigs of megabytes so this tends not really to be the issue anymore.

Generally what comes into play here is what is going to cost in terms of the
"lest" amount in human resources. computers = cheap, human time =
"expensive" development time.

I think it's somewhat better of a development approach overtime as you add
more tabs and more forms to this main form is to Defer the loading of sub
forms until the tab is actually clicked on

If you can't get satisfactory performance doing the above, then perhaps you
do try your preloading of forms idea.

For me the preloading idea does not work very well. Most of my forms only
open up to one record. Thus I open up most of my forms with a "where" clause
that loads only ONE record. By the way this is a good tip in general terms
of performance. If you have a form bound to a table with a million rows in
it, and you launch the form with a "where" clause that is restricted to only
one record, that form will ONLY pull the one record from that table into
that form. This means that your application performance will be the same if
you have ten records in the table, or a million records in that table.

It makes absolutely no sense to bind a form to a large table and then launch
the form and hope that the form is intelligent enough to not load very many
records. In fact this is likely the number one reason why MS access gets
such a bad rap for poor performance. It's not that MS access is slow. In
fact it's not even that MS access allows you to bind a form to a large table
(binding access forms to large tables is simply OK, the trick is to ALWAYS
restrict records loaded from the table when the form is launched). So, a
reason why often ms-access applications perform slow is people building and
designing applications simply bind forms to tables without any regard for
what data gets loaded into the form.

When you walk up to an instant teller, it does not FIRST download every
single account name into the instant teller machine and THEN ask you for the
account number. The machine FIRST ASKS YOU for a account number and loads
ONE RECORD. So, a really great tip in terms of performance is to ask the
user what record they are going to work on, and then launch the form. You'll
also notice that this is also how most web based applications work, and
again it's done for reasons of performance, but it also tends to be more
user friendly and Make searching of data easier for the user.

I explain this type of application flow here:
http://www.members.shaw.ca/AlbertKallal/Search/index.html

 

[Paul]

I've heard it's better to only download the records you need instead of
always loading all records into your forms, but this is the first time I've
seen such a clear explanation of how to do it. It's also a great
explanation of what "user-friendly" really means. This will cause me to
re-think the way I'm designing my interfaces.

I've used something similar to what you are doing in the "Search for People"
form. In an effort to prevent users from adding the same Contact more than
once in the "Add New Contact" form, I use a list box to dynamically update
the matching displays of existing contacts as the user types characters into
the Last Name text box for the new contact. I use the Change event of the
text box to trigger the form's Refresh method. (Tools - Options - Keyboard
Tab - Behavior Entering Field has to be set to "Go to end of field" for that
to work).

But I hadn't thought of taking the additional step of not populating a
form's Record Source until the user's entry has narrowed down the choices.

I also hadn't thought of loading a tab's subform until you click on the tab.
I'll have to try it out.

Thanks so much for the illumination, Albert.

Paul

 

[tina]

and to add on to Albert's comments about subforms, Paul: when you're using
a tab control to display subform records, you don't need a subform control
on each tab. just put a single subform control on the form, and move it
"behind" the tab control, where it will "show through" each tab page. then
use the code Albert posted, to load the subform you need into the subform
control, each time you select a tab.

hth