Count Records in a Subform

[Guest]

Hello,
I have a subform in datasheet view and I would like a count of the number of
records displayed in my main form.

I initially tried placing a field in my subform footer with the =Count()
function and setting field in my mainform to =[Forms]![frmSub]![Count], but
this did not work.

Someone suggested using Recordsetclone. So here's my attempt at code:
Me![Count] = Forms!frmSub.RecordsetClone.RecordCount

All this produces is a blank field. Any ideas on where I went wrong, or
someplace to look for more guidance?
Thanks!
Melinda

 

[Guest]

A quick update. I did some searching and tried:
=[Forms]![frmMaster]![frmSub].[Form].[RecordsetClone].[RecordCount]
instead of my code. This produced a number, but it is not always correct.
For example, when I open my db, there are hundreds of records, but the field
says there are 15. Ideas?

 

[Guest]

Melinda,

Assuming that you are colling this from your main form, try this;

'This IF Statement should be on one line until .MoveLast
If Not (Me.frmSub.Form.RecordsetClone.BOF And
Me.frmSub.Form.RecordsetClone.EOF) Then Me.frmSub.Form.RecordsetClone.MoveLast
fSubRecordCount = = Me.frmSub.Form.RecordsetClone.RecordCount

 

[Guest]

Thanks for the idea, but it doesn't seem to work. Perhaps I'm missing
something. Here's my bit of code.
Dim fSubRecordCount As Field

If Not (Me.frmSub.Form.RecordsetClone.BOF And
Me.frmSub.Form.RecordsetClone.EOF) Then Me.frmSub.Form.RecordsetClone.MoveLast

fSubRecordCount = Me.frmSub.Form.RecordsetClone.RecordCount
Me![moo].Value = fSubRecordCount

I put the entire IfNot on one line as suggested. I get an error when it
gets to the fSubRecordCount=Me.frmSub.Form.RecordsetClone.RecordCount.

Any idea on where I went wrong?
Also, I'm not sure what event to put this under on my main form. I'm using
unbound combo boxes to query the subform, so OnUpdate doesn't seem to work.
Thanks for your thoughts!
Melinda

 

[Guest]

Another update. I switched to using DCount. While not classy and kind of
slow, it works for my purposes. We already have to wait for a DSum, so
waiting a few seconds longer is no big deal.
Here's my solution: =DCount("*","qryMaster")