Keeping Form invisble until all data loaded

#11

The DCounts were all in the saved query recordsource of the Form so I
thought there would be some pre-calculation. However replacing them all
with aggregated subqueries surprisingly virtually elimanates any delay in
the Form opening and rendering. Each of the subQueries seems to need its
own additional subQuery as I found no way to apply criteria to the fields I
am counting in the same query (I mean the criteria has to apply to the Field
value and not the aggregated value) but even with this degree of query
nesting the form loads with no delay. Just shows how slow domain aggregate
functions are.

Thanks to all who responded.

Jon

"David W. Fenton" <NoEmail (AT) SeeSignature (DOT) invalid> wrote

Quote:

"Jon Lewis" <jon.lewis (AT) cutthespambtinternet (DOT) com> wrote in
news:3IadnZh1T7iS-ArRnZ2dnUVZ8uidnZ2d (AT) bt (DOT) com:

Removing the DCounts form the recordsource to the relevant
controls does help but there is of course still a delay in the
controls themselves populating.

What about replacing the DCounts() with subqueries?

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

#12

"Jon Lewis" <jon.lewis (AT) cutthespambtinternet (DOT) com> wrote in
news:48Kdncf9M9IZiQfRnZ2dnUVZ7r2dnZ2d (AT) bt (DOT) com:

Quote:

The DCounts were all in the saved query recordsource of the Form
so I thought there would be some pre-calculation.

I would never put them in the source query unless I needed to
filter/sort on them. I'd rather have them calculate for each record,
rather than it being done for the whole recordsource.

Quote:

However replacing them all
with aggregated subqueries surprisingly virtually elimanates any
delay in the Form opening and rendering. Each of the subQueries
seems to need its own additional subQuery as I found no way to
apply criteria to the fields I am counting in the same query (I
mean the criteria has to apply to the Field value and not the
aggregated value) but even with this degree of query nesting the
form loads with no delay. Just shows how slow domain aggregate
functions are.

A correlated subquery should do the job, i.e., filtering the
subquery based on whatever value you need to filter to from the row
it's in.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

#13

Jon Lewis wrote:

Quote:

Apart from waiting an arbitrary period before making a Form visible is there
no way in Access to know when all data has been calculated so that the Form
can be rendered in one go.

The Form in question is continuous with a Query Recordsource that contains
several DCount fields. I've optimised the query as much as I can (saved it,
indexes table fields etc.) but the Form is rendered in 'chunks' of records.
I've tried all obvious tweeks including:
Dim F as New myForm
DoEvents
myForm.Visible = True (after Visible = False in the Form's open event)

to no avail

Any comments/ideas?

Thanks

Jon

This may or may not be of any benefit.

I created a form called Test. I added a label "This is Form Test.". I
saved it.

I created a form called Test1. I added a label "Loading form Test".
I added the following code.
Private Sub Form_Load()
DoCmd.OpenForm "Test", , , , , acHidden
Me.TimerInterval = 4000 '4 second delay
End Sub
Private Sub Form_Timer()
Forms!Test.Form.Visible = True 'after 4 seconds
DoCmd.Close acForm, Me.Name
End Sub

I saved it and ran. Seems to work using an arbitrary time limit.

#14

"Jon Lewis" <jon.lewis (AT) cutthespambtinternet (DOT) com> wrote in
news:ZPSdnSU6r84O3BvRnZ2dnUVZ7vydnZ2d (AT) bt (DOT) com:

Quote:

The Form in question is continuous with a Query Recordsource that
contains several DCount fields. I've optimised the query as much
as I can (saved it, indexes table fields etc.) but the Form is
rendered in 'chunks' of records.

Are you sure you need to use DCounts() in the Recordsource? Can't
you remove them from the recordsource, and use the OnCurrent event
and use recordsets to look up the information and populate unbound
textboxes with the result?

I've been programming in Access for 14 years now on a near-daily
basis, and haven't needed DCount() in a form recordsource ever. I
have a difficult time thinking of a scenario where it would be
justified.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

#15

Not quite sure what you mean by use the OnCurrent event here. The starting
point for this function of my app is a continuous form with the calculated
columns for all records displayed at form load - not calculated for each
record when it becomes current.

I thought that having the DCounts in the recordsource would be quicker than
than using unbound calculated controls. In fact both methods in my scenario
(which is not particularly complex) are too slow.

The subqueries method I've adopted is much faster (see my other replies)

Jon

"David W. Fenton" <NoEmail (AT) SeeSignature (DOT) invalid> wrote

Quote:

"Jon Lewis" <jon.lewis (AT) cutthespambtinternet (DOT) com> wrote in
news:ZPSdnSU6r84O3BvRnZ2dnUVZ7vydnZ2d (AT) bt (DOT) com:

The Form in question is continuous with a Query Recordsource that
contains several DCount fields. I've optimised the query as much
as I can (saved it, indexes table fields etc.) but the Form is
rendered in 'chunks' of records.

Are you sure you need to use DCounts() in the Recordsource? Can't
you remove them from the recordsource, and use the OnCurrent event
and use recordsets to look up the information and populate unbound
textboxes with the result?

I've been programming in Access for 14 years now on a near-daily
basis, and haven't needed DCount() in a form recordsource ever. I
have a difficult time thinking of a scenario where it would be
justified.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

#16

"Jon Lewis" <jon.lewis (AT) cutthespambtinternet (DOT) com> wrote in
news:9LSdncCmOJEMwD3RnZ2dnUVZ8gSdnZ2d (AT) bt (DOT) com:

Quote:

Not quite sure what you mean by use the OnCurrent event here. The
starting point for this function of my app is a continuous form
with the calculated columns for all records displayed at form load
- not calculated for each record when it becomes current.

I wasn't aware it was a continuous form. That means OnCurrent is not
usable.

Quote:

I thought that having the DCounts in the recordsource would be
quicker than than using unbound calculated controls.

In a continuous form, you can't use unbound calculated controls for
the same reason you can't use OnCurrent.

Quote:

In fact both methods in my scenario
(which is not particularly complex) are too slow.

I would still suggest that subqueries aside, there are likely more
efficient ways to get the information, particularly if any of your
original domain aggregate functions were pulling data from the same
table.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Keeping Form invisble until all data loaded

comp.databases.ms-access comp.databases.ms-access

Re: Keeping Form invisble until all data loaded - 09-22-2010 , 09:36 AM

Re: Keeping Form invisble until all data loaded - 09-22-2010 , 02:23 PM

Re: Keeping Form invisble until all data loaded - 09-22-2010 , 03:49 PM

Re: Keeping Form invisble until all data loaded - 09-23-2010 , 03:06 PM

Re: Keeping Form invisble until all data loaded - 09-27-2010 , 04:02 AM

Re: Keeping Form invisble until all data loaded - 09-27-2010 , 11:45 AM