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

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

Jon Lewis wrote:

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

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.

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

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

John Spencer wrote:

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.


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

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

Thanks anyway.


"John Spencer" <JSPENCER (AT) Hilltop (DOT) umbc> wrote

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

What about replacing the DCounts() with subqueries?

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

On 20/09/2010 21:31:32, "David W. Fenton" wrote:
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

Public Function ECount(Expr As String, Domain As String, Optional Criteria As
String, Optional bCountDistinct As Boolean) As Variant

On Error GoTo Err_ECount
'Purpose: ose: Enhanced DCount() function, with the ability to count
distinct. 'Return: Number of records. Null on error.
'Arguments: nts: Expr = name of the field to count. Use square brackets if
the name contains a space. ' Domain = name of the table or query.
' Criteria = any restrictions. Can omit.
' ' bCountDistinct = True to return the number of distinct values in the
field. Omit for normal count. 'Notes: Nulls are excluded (whether distinct
count or not.) ' Use "*" for Expr if you want to count the nulls too.
' You cannot use "*" if bCountDistinct is True.
'Examples: les: Number of customers who have a region: ECount("Region",
"Customers") ' Number of customers who have no region: ECount("*",
"Customers", "Region Is Null") ' Number of distinct regions: ECount("Region",
"Customers", ,True) Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String

'Initialize to return Null on error.
ECount = Null
Set db = DBEngine(0)(0)

If bCountDistinct Then
'Count distinct values.
If If Expr <> "*" Then 'Cannot count distinct with the wildcard. strSql
strSql = "SELECT " & Expr & " FROM " & Domain & " WHERE (" & Expr & " Is Not
Null)" If Criteria <> vbNullString Then
strSql = strSql & " AND (" & Criteria & ")"
End If
strSql = strSql & " GROUP BY " & Expr & ";"
Set rs = db.OpenRecordset(strSql)
If rs.RecordCount > 0& Then
rs.MoveLast
End If
ECount ECount = rs.RecordCount 'Return the number of distinct records.
rs.Close
End If
Else
'Normal count.
strSql = "SELECT Count(" & Expr & ") AS TheCount FROM " & Domain
If Criteria <> vbNullString Then
strSql = strSql & " WHERE " & Criteria
End If
Set rs = db.OpenRecordset(strSql)
If rs.RecordCount > 0& Then
ECount = rs!TheCount 'Return the count.
End If
rs.Close
End If

Exit_ECount:
Set rs = Nothing
Set db = Nothing
Exit Function

Err_ECount:
If If Err.Number < 0& Or Err.Number > 65535 Then 'Valid range for CVErr()
ECount = CVErr(5) 'Out of range.
Else
ECount = CVErr(Err.Number)
End If
MsgBox Err.Description, vbExclamation, "ECount Error " & Err.Number
Resume Exit_ECount

End Function

See if you find this faster

Phil

"Phil" <phil (AT) stantonfamily (DOT) co.uk> wrote in
news:tcSdnea2mMVEfQrRnZ2dnUVZ7sSdnZ2d (AT) brightview (DOT) co.uk:

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 W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

On 22/09/2010 03:45:38, "David W. Fenton" wrote:
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