Form with Tabs slow to load

  • Thread starter Thread starter Maureen
  • Start date Start date
M

Maureen

HELP PLEASE!

I have an Access 2002 db - front end and back end
(tables) are split.

One form is giving me trouble - it is incredibly slow to
load. Navigation between records is quick once the form
is open but it is just too slow in opening.

This form has 5 tabs - each containing a subform which is
dependent on the main form (of course)and is also
dependant on the content of the subform(s) preceeding
it. Each subform gets requeried upon entry so that the
correct data is displayed. Each subform opens quickly by
itself -

There must be a design flaw - but I'm not sure where to
begin looking. I'm hoping someone out there can give me
some design tips/suggestions on where to begin to look so
that I can fix the slow load on this one form.

Thanks for any help you can offer!
 
Maureen,
What do you mean by "incredibly slow"? Is there any code in the Load or Open
events of any of the sub forms or of the main form? All of those events have
to run before the form completely opens at the first record.
 
Lynn,

Thanks for helping.

There is NOTHING in the Load or open events on the main
form. Each of the subfoms HAD a requery command on open
but I removed all of that to see what the difference was
and it was minimal. I will be putting the requery
commands back in on the open event of the subforms.

The initial load time is about 15-20 seconds which, to
me, seems incredibly slow. Once the form is open - I can
click around the subforms and move from record to record
on the main form without delays so it's just in the
initial opening.

Any ideas of what I should look at to speed things up?
 
Maureen,

Why would you want to put a requery command in the open event of a form.
That event only runs when you first open a form and it does a query of the
data at that time, thus there shouldn't be any need for a requery.

One thing you might try is to not bind the subforms until they are first
accessed by the user. Set the recordsource for a subform when the user
activates a tab and then requery the subform.
 
The problem is that each Subform will have to be loaded
with data from relevant Table(s) before the (main) Form is
ready for the user. In effect, you have to wait for the
loading of ALL Subforms and the Main Form. If you have a
number of Subforms, one on each Tab Page, this will slow
the opening of the Form.

What I normally do is to use the TabControl but don't use
the Tab Pages. I place ONE SubformControl ON the Main
Form. Set the SourceObject to the Subform required on
opening (probably the one on the your first Tab Page at
the moment). I use code in the TabControl_AfterUpdate (?)
Event to change the SourceObject of the SubformControl
when the user moves from 1 tab to another.

This way, when the Form is open only ONE Subform + Main
Form need to be loaded. When the user moves from one tab
to another, there will be small delay as another Subform
is loaded. In effect, you share the delay during the
operation of the Form rather than concentrate the delay on
the opening of the Form.

HTH
Van T. Dinh
MVP (Access)
 
Lynn & Van,

THANKS to you both! I understand what you are
suggesting - a different approach from each of you but I
think the overall idea/suggestion is the same. Don't let
the subforms load until the user requests them by
clicking on the tab.

I'm trying to write a macro that will execute upon the
click event of the tab - the macro will set the
sourceobject of the subform. I can walk through these
steps manually and get it to work - but I am unable to
get it written in a workable macro (I don't know codes).
I'm trying it with the sourceobject of the subform rahter
then sourcerecord of the form Since it was easier for me
to locate the SOURCEOBJECT within the Macro window.

I am trying to use the SetValue command - the ITEM is the
subform (path is [Forms]![mainform]![subform].
[SourceObject]. The value in the EXPRESSION is set to
be the [form name of subform].

I'm missing something here but can't figure out what.

YOUR CONTINUED HELP WILL BE MOST APPRECIATED!

Side note: Lynn, I need to requery the tab subforms
every time they are viewed because what they display is
subject to change depending upon the record the user has
active on the preceeding tab - if you have another
suggestion on how to handle this I'm wide open!
 
A 2nd reply at this level of the thread:

The error I am getting when trying to execute a macro
using the setvalue command mentioned earlier is:

"Vis Basic can't convert the data type fo one of the
arguments you entered"

I tried to write a macro to fill in a missing
RecordSource also (after I replaced the SourceObject) but
received similar errors.

I really need some help here - I feel so close to the
solution but can't quite grasp it.

Thank YOU!
 
When you wrote "Macro", did you mean Access Macro (likely since you
mentioned "SetValue") or Access VBA code?

Either way, post relevant details (names of main Form / SubformControls /
Forms that are being uses as the SourceObjects of the SubformControls),
details of your Macro actions including the macro arguments or the relevant
VBA code.

If you actually meant Access Macros, I will be struggling since about the
only Macros I wrote for the last 8-9 years are "AutoExec" Macro and the
usual Macro action is RunCode action to run VBA codes!

Hopefully, Lynn knows more on Macros and pray that Ken Snell and Steve
Schapel are watching this thread as they are more conversant with Macros.
 
Hopefully, Lynn knows more on Macros and pray that Ken Snell and Steve
Schapel are watching this thread as they are more conversant with Macros.
Click to expand...

Argggggggggghhhhhhhhhhhh....I stay away from Macros as much as you do, Van
 
Maureen,
I'm not much of a Macro expert, but I don't think you can use the SetValue
option to change the recordsource of your subform. You can use the click
event of the Page to set it with code similar to the following:

Forms!YourMainForm.Form!YourSubform.RecordSource

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Maureen said:
Lynn & Van,

THANKS to you both! I understand what you are
suggesting - a different approach from each of you but I
think the overall idea/suggestion is the same. Don't let
the subforms load until the user requests them by
clicking on the tab.

I'm trying to write a macro that will execute upon the
click event of the tab - the macro will set the
sourceobject of the subform. I can walk through these
steps manually and get it to work - but I am unable to
get it written in a workable macro (I don't know codes).
I'm trying it with the sourceobject of the subform rahter
then sourcerecord of the form Since it was easier for me
to locate the SOURCEOBJECT within the Macro window.

I am trying to use the SetValue command - the ITEM is the
subform (path is [Forms]![mainform]![subform].
[SourceObject]. The value in the EXPRESSION is set to
be the [form name of subform].

I'm missing something here but can't figure out what.

YOUR CONTINUED HELP WILL BE MOST APPRECIATED!

Side note: Lynn, I need to requery the tab subforms
every time they are viewed because what they display is
subject to change depending upon the record the user has
active on the preceeding tab - if you have another
suggestion on how to handle this I'm wide open!
-----Original Message-----
The problem is that each Subform will have to be loaded
with data from relevant Table(s) before the (main) Form is
ready for the user. In effect, you have to wait for the
loading of ALL Subforms and the Main Form. If you have a
number of Subforms, one on each Tab Page, this will slow
the opening of the Form.

What I normally do is to use the TabControl but don't use
the Tab Pages. I place ONE SubformControl ON the Main
Form. Set the SourceObject to the Subform required on
opening (probably the one on the your first Tab Page at
the moment). I use code in the TabControl_AfterUpdate (?)
Event to change the SourceObject of the SubformControl
when the user moves from 1 tab to another.

This way, when the Form is open only ONE Subform + Main
Form need to be loaded. When the user moves from one tab
to another, there will be small delay as another Subform
is loaded. In effect, you share the delay during the
operation of the Form rather than concentrate the delay on
the opening of the Form.

HTH
Van T. Dinh
MVP (Access)






.
Click to expand...
Click to expand...
 
Oops...the complete statement should be:

Forms!YourMainForm.Form!YourSubform.RecordSource = "YourTableName"

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Lynn Trapp said:
Maureen,
I'm not much of a Macro expert, but I don't think you can use the SetValue
option to change the recordsource of your subform. You can use the click
event of the Page to set it with code similar to the following:

Forms!YourMainForm.Form!YourSubform.RecordSource

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Maureen said:
Lynn & Van,

THANKS to you both! I understand what you are
suggesting - a different approach from each of you but I
think the overall idea/suggestion is the same. Don't let
the subforms load until the user requests them by
clicking on the tab.

I'm trying to write a macro that will execute upon the
click event of the tab - the macro will set the
sourceobject of the subform. I can walk through these
steps manually and get it to work - but I am unable to
get it written in a workable macro (I don't know codes).
I'm trying it with the sourceobject of the subform rahter
then sourcerecord of the form Since it was easier for me
to locate the SOURCEOBJECT within the Macro window.

I am trying to use the SetValue command - the ITEM is the
subform (path is [Forms]![mainform]![subform].
[SourceObject]. The value in the EXPRESSION is set to
be the [form name of subform].

I'm missing something here but can't figure out what.

YOUR CONTINUED HELP WILL BE MOST APPRECIATED!

Side note: Lynn, I need to requery the tab subforms
every time they are viewed because what they display is
subject to change depending upon the record the user has
active on the preceeding tab - if you have another
suggestion on how to handle this I'm wide open!
-----Original Message-----
The problem is that each Subform will have to be loaded
with data from relevant Table(s) before the (main) Form is
ready for the user. In effect, you have to wait for the
loading of ALL Subforms and the Main Form. If you have a
number of Subforms, one on each Tab Page, this will slow
the opening of the Form.

What I normally do is to use the TabControl but don't use
the Tab Pages. I place ONE SubformControl ON the Main
Form. Set the SourceObject to the Subform required on
opening (probably the one on the your first Tab Page at
the moment). I use code in the TabControl_AfterUpdate (?)
Event to change the SourceObject of the SubformControl
when the user moves from 1 tab to another.

This way, when the Form is open only ONE Subform + Main
Form need to be loaded. When the user moves from one tab
to another, there will be small delay as another Subform
is loaded. In effect, you share the delay during the
operation of the Form rather than concentrate the delay on
the opening of the Form.

HTH
Van T. Dinh
MVP (Access)





-----Original Message-----
Lynn,

Thanks for helping.

There is NOTHING in the Load or open events on the main
form. Each of the subfoms HAD a requery command on open
but I removed all of that to see what the difference was
and it was minimal. I will be putting the requery
commands back in on the open event of the subforms.

The initial load time is about 15-20 seconds which, to
me, seems incredibly slow. Once the form is open - I can
click around the subforms and move from record to record
on the main form without delays so it's just in the
initial opening.

Any ideas of what I should look at to speed things up?



.
Click to expand...
Click to expand...
Click to expand...
 
Looks like I'm counting on Ken and Steve for some help
here.

I need to fill in the sourceobject of a subform on
opening it (opening by clicking on the tab control). I'm
trying to do this with a macro but continually get error
messages - ie the value of the property isn't supported
by visual basic.

I gotta believe this is do-able. But I can not get it to
work - any help you can offer will be greatly appreciated!
 
Back
Top