Form takes 2 seconds before subforms start to load

[Chrisso]

Hi All

I have a form (frmStaffDetails) with three subforms and a bunch of
controls for filtering the subforms. frmStaffDetails itself has a
table as a data source.

I am trying to get frmStaffDetails to open as quickly as possible. I
have added some Debug.Print calls to find out why the form takes 3
seconds to load.

The Debug.Print out shown below indicates that 2 of the 3 seconds are
taken before anything on frmStaffDetails happens:

User clicks button to open frmStaffDetails at: 18/02/2010 10:58:02
?? what happens here that takes 2 seconds ??
Subform1 Form_Load: 18/02/2010 10:58:04 <<< subforms start to
load
Subform1 Form_Current: 18/02/2010 10:58:04
Subform2 Form_Load: 18/02/2010 10:58:04
Subform2 Form_Current: 18/02/2010 10:58:04
Subform3 Form_Load: 18/02/2010 10:58:06
Subform3 Form_Current: 18/02/2010 10:58:06
frmStaffDetails Form_Open: 18/02/2010 10:58:06 <<< main form starts
to load
frmStaffDetails Form_Load: 18/02/2010 10:58:06
frmStaffDetails Form_Current: 18/02/2010 10:58:06

How can I find out what is happening in the 2 seconds that the user
clicks the button to open frmStaffDetails and the first subform being
loaded? What could be happening here?

Stepping through with the debugger shows me that no other code of mine
is called between the user clicking and frmStaffDetails opening so
what is Access doing in this time?

Thanks in advance for any ideas, hints or tips.
Chrisso

 

[Rob Parker]

Hi Chrisso,

Assuming that your posted timings are coming from a set of Debug.Print
statements:

The delay is between subform2 and subform3. I'd check how long the query
which is the Record Source for each of these subforms takes to run: open
each query directly to see how long it takes. It's likely that one of them
will be slow - perhaps due to a function being called. I had a similar
problem with a form in one of my applications using a custom function to
concatenate multiple authors into a single field; removing that from the
form's RecordSource changed a couple of seconds delay into no delay.

HTH,

Rob

 

[Chrisso]

HiChrisso,

Assuming that your posted timings are coming from a set of Debug.Print
statements:

The delay is between subform2 and subform3.  I'd check how long the query
which is the Record Source for each of these subforms takes to run: open
each query directly to see how long it takes.  It's likely that one of them
will be slow - perhaps due to a function being called.  I had a similar
problem with a form in one of my applications using a custom function to
concatenate multiple authors into a single field; removing that from the
form's RecordSource changed a couple of seconds delay into no delay.

HTH,

Rob



Chrissowrote:








- Show quoted text -

Thanks for the reply Rob. Here is another set of timings that shows
the delay is *before* any subforms load:

txtStaffName_DblClick: 19/02/2010 06:41:34

sfrmStaffCoursesStatusDisplay Form_Load: 19/02/2010 06:41:41
sfrmStaffCoursesStatusDisplay Form_Current: 19/02/2010 06:41:41

sfrmTrainerCoursesCompleted Form_Load: 19/02/2010 06:41:41
sfrmTrainerCoursesCompleted Form_Current: 19/02/2010 06:41:41

frmTrainerActivities Form_Load: 19/02/2010 06:41:42
frmTrainerActivities Form_Current: 19/02/2010 06:41:42

frmStaffDetails Form_Load: 19/02/2010 06:41:42
frmStaffDetails Form_Current: 19/02/2010 06:41:42

I think the original timings I posted were misleading. As you can see
the time is taken before the first subform
(sfrmStaffCoursesStatusDisplay) load event is called.

I can open sfrmStaffCoursesStatusDisplay by itself and it opens very
quickly - 0.4 seconds. Therefore I think the delay is something other
than that sub form opening.

I really need to work out what Access is doing between the button
being clicked and the first subform being loaded.

Thanks for any further ideas,
Chrisso

 

[Chrisso]

I have tested all the queires that feed the subforms independently now
and they all execute very fast so I am pretty sure that the subforms
are not taking the time.

The main form itself sits on a table that is also very fast to open.

Chrisso

 

[Chrisso]

I think I know where I have been going wrong. I was only timing how
long it takes to *open* the query. DoCmd.OpenQuery

What is harder to time (unless someone can tell me) is how long the
*entire* query takes to execute.

When I used DoCmd.OpenQuery in a VB loop to calculate timings I can
only time how long Access takes to display a query window with the
first lot of results. It then takes another 2-3 seconds to display the
total number of records in the query at the bottom of the window - I
am guessing that this means the whole record set has been calculated.
I think I also read this in another posting on here somewhere.

Therefore when I thought the query in my first sub form was quick I
was wrong - it was only quick to display. It does indeed take 2-3
seonds to display the total number of records in the set. So Access
must be calculatng the whole record set before the subform loads thus
explaining what it is doing between the user clicking the button and
the first sub form loading - it is executing its query.

Thanks for your help,
Chrisso