Form "open" but not loaded yet (still calculating DCounts)

[sohunter]

I have a form that alerts users to various data clean up tasks needed -- by giving them report/data issue names, a button to open that report, and a count (before they even open the report) as to how many records are in that report.

These dCount expression text controls - there's about 5 of them, are really slowing down the load time of the form. The form appears, and it looks like you can use it, but it's not "enabled" until the calculating is done...about 10 seconds into load-time.

I'm not overly concerned, at least at the moment, about the load-time...just want the user to know it's not loaded yet, until it really is. Any basic suggestions on a game-plan to handle this situation?

 

[TheAceMan1]

How are ya sohunter . . . . .

Make a Totals Query that returns the count for each. [purple]Then thru functions return the count using Recordsets.[/purple] Textbox ControlSource would be:

[blue]   = Total1()[/blue]

In a module in the modules window, the function would be something like:

[blue]Public Function Total1()
   Dim db As DAO.Database, rst As DAO.Recordset
   
   Set db = CurrentDb()
   Set rst = db.OpenRecordset("[purple][b]TotalQueryame[/b][/purple]", dbOpenDynaset)
      Me![purple][b]TextBoxName[/b][/purple] = rst![purple][b]FieldName[/b][/purple]
   Set rst = Nothing
   Set db = Nothing
End Function[/blue]

See Ya! . . . . . .

 

[sohunter]

Hi AceMan1
I'm going to 5 different queries for these totals. So I'd have to do this function 5 times, right? Do you think that's faster than just doing DCounts on each query from the form?

 

[TheAceMan1]

How are ya sohunter . . . . .

[blue]Do you think that's faster than just doing DCounts on each query from the form?[/blue]

[purple]Domain Aggregate Function are the slowest functions form what they do.[/b][/purple] Campared to queries they don't even belong in Access. However, they can be very useful where recordcount is not large, making useful short code.

Make the Totals Queries and post back the totals queries of each. I'll combine them into one routine . . . .

See Ya! . . . . . .

 

[TheAceMan1]

How are ya sohunter . . . . .

[blue]Do you think that's faster than just doing DCounts on each query from the form?[/blue]

[purple]Domain Aggregate Function are the slowest functions form what they do.[/b][/purple] Campared to queries they don't even belong in Access. However, they can be very useful where recordcount is not large, making useful short code.

Make the Totals Queries and post back the [purple]SQL[/purple] of each. I'll combine them into one routine . . . .

See Ya! . . . . . .

 

[sohunter]

You are very kind.

qryDM_PossibleDuplicatesCount
qryDM_NotArchivedACount
qryDM_NoRaceACount
qryDM_NoCountyACount
qryDM_BirthdateACount

The field we're retrieving in all of these (just one row per query, of course) is CountOfClientID.

I've named my form's text fields, but I'll get the idea with whatever examples you give.

Some of these queries take more than a few seconds to run though, so I may still be looking at my original problem of wanting to hide the form, or give some kind of splash screen? while the queries are running.

 

[sohunter]

oops - you said SQL...

SELECT Count(qryDM_PossibleDuplicates.ClientID) AS CountOfClientID
FROM qryDM_PossibleDuplicates;

SELECT Count(qryDM_NotArchivedA.ClientID) AS CountOfClientID
FROM qryDM_NotArchivedA;

SELECT Count(qryDM_NoCountyA.ClientID) AS CountOfClientID
FROM qryDM_NoCountyA;

SELECT Count(qryDM_NoRaceA.ClientID) AS CountOfClientID
FROM qryDM_NoRaceA;

SELECT Count(qryDM_BirthdateA.ClientID) AS CountOfClientID
FROM qryDM_BirthdateA;

 

[TheAceMan1]

OK sohunter . . . . .

The code requires [purple]Microsoft DAO 3.6 Object Library[/purple] to run. To [blue]check/install[/blue] the library, in any code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Then using the up arrow, [purple]push it up as high in priority as it will go[/purple]. Click OK.

Now, in the [blue]ControSource[/blue] of the textboxes of interest, insert the following:

[blue]   =CntDM(Idx)

   [green]'Idx = 1 for PossibleDuplicates
   'Idx = 2 for NotArchivedA
   'Idx = 3 for NoCountyA
   'Idx = 4 for NoRaceA
   'Idx = 5 for BirthdateA[/green][/blue]

Next in a [blue]module[/blue] in the [blue]modules window[/blue], copy/paste the following function:

[blue]Public Function CntDM(Idx As Integer)
   Dim db As DAO.Database, rst As DAO.Recordset, SQL As String
   
   Set db = CurrentDb()
   SQL = "SELECT Count(ClientID) AS CntID "
   
   If Idx = 1 Then
      SQL = SQL & "FROM qryDM_PossibleDuplicates;"
   ElseIf Idx = 2 Then
      SQL = SQL & "FROM qryDM_NotArchivedA;"
   ElseIf Idx = 3 Then
      SQL = SQL & "FROM qryDM_NoCountyA;"
   ElseIf Idx = 4 Then
      SQL = SQL & "FROM qryDM_NoRaceA;"
   Else
      SQL = SQL & "FROM qryDM_BirthdateA;"
   End If
   
   Set rst = db.OpenRecordset(SQL, dbOpenSnapshot)
   CntDM = rst!CntID
   
   Set rst = Nothing
   Set db = Nothing

End Function[/blue]

[purple]Thats it . . . give it a whirl & let me know . . .[/purple]

See Ya! . . . . . .

 

[sohunter]

Great to have that code. Thank you. It works!

It still takes a while, perhaps 20 seconds now as opposed to 30 or something) so I'll still be looking for a way to notify user the form won't load instantly.

 

[TheAceMan1]

sohunter . . . . .

What kind of table recordcount are you talking?

Is this on a network?

See Ya! . . . . . .

 

[sohunter]

Where I'm developing at home it's not a network, but at the nonprofit that uses the database, it is a network. Anything that runs slow at my home PC doesn't tend to do much better there. I am using an older Dell Optiplex GX1 ... 128 ram, am about to upgrade finally!

My Count queries are looking at queries with 4 to 5 tables.

eg qryDM_NoCountyA:

SELECT tblIntake.ClientID, IIf(IsNull([tblComponent].[ClientID]),"","C") AS Has_Components, IIf(IsNull([tblEvents].[ClientID]),"","E") AS Has_Events, Sum(tblServiceLog.Units) AS SumOfUnits, tblIntake.DOB, [tblIntake].[last] & ", " & [tblIntake].[first] & " " & [tblIntake].[ClientID] AS FullNameClientID, tblIntake.First, tblIntake.Last, tblIntake.IntakeDate, Max(tblServiceLog.Date) AS MaxOfDate, tblIntake.County, tblIntake.CountyWhich, tblIntake.AssignedCounselor, tblIntake.StaffCompleting
FROM ((tblIntake LEFT JOIN tblServiceLog ON tblIntake.ClientID = tblServiceLog.ClientID) LEFT JOIN tblEvents ON tblIntake.ClientID = tblEvents.ClientID) LEFT JOIN tblComponent ON tblIntake.ClientID = tblComponent.ClientID
WHERE (((tblServiceLog.Date)>=#7/1/2004#))
GROUP BY tblIntake.ClientID, IIf(IsNull([tblComponent].[ClientID]),"","C"), IIf(IsNull([tblEvents].[ClientID]),"","E"), tblIntake.DOB, [tblIntake].[last] & ", " & [tblIntake].[first] & " " & [tblIntake].[ClientID], tblIntake.First, tblIntake.Last, tblIntake.IntakeDate, tblIntake.County, tblIntake.CountyWhich, tblIntake.AssignedCounselor, tblIntake.StaffCompleting, IIf(IsNull([tblComponent].[ClientID]),"","C"), IIf(IsNull([tblEvents].[ClientID]),"","E")
HAVING (((tblIntake.ClientID) In (select ClientID from qryHasComponentorServiceOrEvent)) AND ((tblIntake.County) Is Null Or (tblIntake.County)=""))
WITH OWNERACCESS OPTION;