Anyone have any ideas for an efficient method/function to count the total
number of records in a subDatasheet please? I have a MainForm with a
Datasheet sub form that has a subDatasheet (constructed in the Form and not
at Table or Query level).

Me.MainForm.subControl.Form.subControl.Form.Record setClone.RecordCount of
course only returns the sub record count for the selected record on the
Datasheet. I need the total subDatasheet records for all the Datasheet
records.

The Datasheet Record Source is a saved query but with a complex run time
generated Filter applied (to the Datasheet form). The subDatasheet Record
Source is a saved query with Master/Child links to the Datasheet. I foresee
DAO/Domain Aggregate methods as going to be very difficult to construct and
slow to update so I just wanted to see if anyone had any ideas.

A2K7 but MDB format

TIA

Jon

If the records are related then there should be some method to construct a
query to return the count. You would have to Join the queries (record sources)
for the main form, the sub-form, and the sub-sub-form and apply a filter to
return only the relevant records.


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

Jon Lewis wrote:

You could clone the recordsource of the subform and count that.

"Jon Lewis" <jon.lewis (AT) cutthespambtinternet (DOT) com> wrote

Well there's several ways with queries to get a total but they are all very
slow 60 secs plus with the ones I've tried with 4000 odd datasheet records

I tried another approach and it works well and is virtually instant. For
anyone's future reference:

Dim rs As DAO.Recordset, rs2 As DAO.Recordset
Set rs = Me.subCampaignTarget.Form.RecordsetClone
If Not rs.EOF Then
Dim strIDs As String
rs.MoveFirst
Do Until rs.EOF
strIDs = strIDs & "," & rs!CompanyID
rs.MoveNext
Loop
strIDs = Mid(strIDs, 2)
Set rs2 = CurrentDb.OpenRecordset("SELECT
qryCampaignTargetContacts.ContactID FROM qryCampaignTargetContacts WHERE
qryCampaignTargetContacts.CompanyID IN (" & strIDs & ")")

If Not rs2.EOF Then
rs2.MoveLast
GetSubCount = rs2.RecordCount
Else
GetSubCount = 0
End If
Else
GetSubCount = 0
End If

Jon

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