Keeping Form invisble until all data loaded

[Jon Lewis]

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

 

[John Spencer]

You might try something like the following. In my simple test it seemed to work

Public Sub sOpenPopulated()
Dim sFormName As String

sFormName = "frmCalendarTable"

DoCmd.OpenForm sFormName, acNormal, , , , acHidden

With Forms(sFormName)
.RecordsetClone.MoveLast
.Bookmark = .RecordsetClone.Bookmark
.RecordsetClone.MoveFirst
.Bookmark = .RecordsetClone.Bookmark
.Visible = True
End With

End Sub


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[David W. Fenton]

The Form in question is continuous with a Query Recordsource that
contains several DCount fields.

Remove the DCounts from the recordsource and show use them as
ControlSources in textboxes on the form. Or, figure out a way to
avoid needing them in the first place.

 

[John Spencer]

If you post the SQL of your current query, it might be possible to suggest
changes to the query. Or instead of having the DCount calculations in the
query, it might be possible to set them up on the form's controls (at least in
some cases).

Do you need to be able to update the records that are being displayed?

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Jon Lewis]

Sorry for the late feedback on this (I'd actually forgotten I'd posted the
question!)

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.

I was just wondering if there was a generic way one testing that all data
and rows had been calculated and loaded before making a form visible but it
seems not.

Thanks anyway.

 

[Jon Lewis]

Please see below (unfortunately your suggestion above made no difference -
maybe the symptom wasn't that noticeable in your simple test?

Thanks anyway.

 

[David W. Fenton]

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]

Allen Brown has a number of functions which are reported to ne
much faster than the inbuilt "D" functions including ELookup,
EMin, EFirst, ESum etc. They work in exactly the same way as the
eqivalent DLookup, DMin, DFirst, DSum etc

Is it not the case that these were faster only until A2000? The
problem was that the domain aggregate functions were slow on linked
tables, so many of us used replacement functions. I used Trevor
Best's tCount() functions. But with A2000, I thought that bug was
fixed, so they were no longer necessary.

I already suggested replacing them with custom functions, and it's
only if that didn't work that I suggested subqueries.

 

[Phil]

Is it not the case that these were faster only until A2000? The
problem was that the domain aggregate functions were slow on linked
tables, so many of us used replacement functions. I used Trevor
Best's tCount() functions. But with A2000, I thought that bug was
fixed, so they were no longer necessary.

I already suggested replacing them with custom functions, and it's
only if that didn't work that I suggested subqueries.

David
No idea about the speed. I have never had big enough recordsets for any
differences in timing to show up. However I have been using Access since it
first came out and was told at some point that ELookup was faster than
DLookup, so have used it instintively ever since.
Maybe if someone has some large recordsets they could do some timings and let
the NG have their conclusions

Phil

 

[Jon Lewis]

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]

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.

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.

 

[Salad]

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.

 

[David W. Fenton]

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.

 

[Jon Lewis]

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]

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.

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.

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.