Record count in sub forms 1

[kennetha]

hi everyone!
I need to count records in a sub form based on a query. I used
Forms![mainform].form![subform].RecordsetClone.RecordCount but with out success. Can you help please.
thx in advance
Kenneth.

 

[rickgrimes]

Kenntha,

Try the DCount function. i.e.,

Dcount("[Anyfield]","[QueryName_forSubform]","AnyCriteria&quot

The AnyCriteria is optional. It will return the number of "Anyfield" within a table or query. I have used this many times in a textfield.

Hope this helps.

Rick

 

[kennetha]

thx rick
I tried to use dcount & even count. It "worked" but when there are no records, the field is blank and not null. I need to count even 0 records.
thx
Kenenth

 

[OhioSteve]

Yeah, RickGrimes is 100% on target. First, you need to concentrate on counting the subform's data source (the query). Second, you need to use Dcount or something similar.

 

[rickgrimes]

Kenneth,

Maybe a block if:
If Dcount(...)<1 then
Myfield=0
End if

Rick

 

[BillPower]

This is a completely different approach.

In a Module's declaration section:

Public gRecordCount As Long

In the On Current event of the Sub Form:

Dim rst As DAO.Recordset
On Error GoTo Err_RecordCount
Set rst = Me.RecordsetClone
rst.MoveLast
gRecordCount = rst.AbsolutePosition + 1
RecordCount_Continue:
MsgBox gRecordCount
rst.Close
Exit Sub
Err_RecordCount:
gRecordCount = 0
Resume RecordCount_Continue

I'll leave it up to you how to use the gRecordCount.

Bill