Link to home
Start Free TrialLog in
Avatar of Michael Vasilevsky
Michael VasilevskyFlag for United States of America

asked on

Get Subform Rowcount

I have a subform in datasheet view on my form. How can I get the number of rows currently displayed by the subform query in VBA?

Something like: Forms!frm_Documents!DocumentsSubform.rowcount
ASKER CERTIFIED SOLUTION
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jpipkins
jpipkins

   Dim rcs As Recordset
    Set rcs = Me.frmSchedule.Form.RecordsetClone
   
Just put that in the ControlSource of a text box:

=Forms!frm_Documents!DocumentsSubform.Form.RecordsetClone.Recordcount

or even shorter syntax ... assuming the text box is on your main form:

=DocumentsSubform.Form.RecordsetClone.Recordcount

mx
Avatar of Michael Vasilevsky

ASKER

loves it
Use what these fine experts have suggested.  however, one little caviot:
If the recordset recordcount is > 0, In code, use a .MoveLast then .MoveFirst.  
This forces Jet to count all the records
Otherwise the recordcount may not account for all the records when the number of records is big.
You could also use

=DCount("*","[YourSubformTableOrQueryName]")  ... *Assuming* that ... you have not done any filtering at the subform level, ie on the subform (or datasheet) right clicked and used one of the 'Filter' functions.

mx
Mike ... I think you spent too much time in the sun at El Centro this weekend.

What is your definition of 'big'.  Why do you think the clone would not be correct?

mx
the recordsetclone is correct.  However, when you open up a form that has a lot of records in the recordset (or clone), Access needs to be "forced to the bottom".

You've seen it when you open a form and the record count at the bottom does not show how many records you have.  In a little while, it will.  That's because Jet is "fetching" the record count in the background for performance purposes.
Well ... not sure a form s/b opening with THAT many records to begin with.

mx
mvasilevsky ... what Mike is saying ... is you might want to do this ... 'depending'

Public Function mGetRecCnt() As Long
    Dim rst as dao.recordset
    set rst = me.RecordsetClone
    With rst
        If .RecordCount = 0 then
            mGetRecCnt = 0
            Goto mGetRecCnt_Exit
        End If
        .MoveLast
        mGetRecCnt = .RecordCount      
mGetRecCnt_Exit:
      rst.Close
      End With
      set rst = Nothing
End Function

Then, say from a text box you would do this:

=mGetRecCnt()
Good stuff. I never have more than a few thousand records so I think RecordsetClone will be sufficient for me. If I need to get the rowcount for a larger recordset, I'll use the function above.
Thanks again!

Michael
A few thousand is a lot of records ... you may experience what Mike mentioned.  If so, take two viagra and call Mike the next morning, lol.

Why so many records in a subform/datasheet - just curious.

mx
well, it may be something you run into....or not.  I run into it because I routinely have large recordsets on ODBC Tables.  
An example where you might have large numbgers of records would be a User query screen that allows them to specify their own criteria and return results.  You never know how restrictive or how open the criteria may be.

The bigger picture is that Access/Jet WILL return a false reading of recordcount at some point unless you go to the bottom and back.  So do you want code with will be buggy under the right conditions or do you want bullet-proof, reliable code.
Good point Mike, I'm revising my code now.

Thanks for the help!
Not sure you have to 'go back'.  Why?  Doing a MoveLast should give you the correct count ... just like it does if you were to use the record selectors and goto to the last record ... wait ...and see the total.

mx