Count Records in a subform (1 Viewer)

[scottrie2]

I have a form (Family) that has a subform (children). On the main form, the is a next button, previous button and a combo box that allows a user to navigate to a specific family. I need to be able to know how many children there are when I navigate to a record. I have tried a few options that have not worked. The most direct method I tried was:

    If Me.Children.Form.Recordset.RecordCount > 0 Then
        ChildOn (1)
        Else
        ChildOn (0)
    End If

This worked in that the result for RecordCount returns either a 0 (no children) or a 1 (any number of children). The problem is that it does not give an accurate number -- 1 child returns a 1 and 6 children returns a 1 as well. I need to have an accurate count for what I am programming (I am going a different direction than the ChildOn that I was using.) I also put a text box on the subform with a source of =Count(*). That does work, but I cannot figure out how to access that number from the main form's Next button because it is not calculated until AFTER the code from the next button is finished processing. I was wondering if there was some type of DAO recordset code that I could run that would see how many children records were associated with the FamilyID of the main form.

Thanks for any insight.

 

[MajP]

you can do a dcount
dcount("*","SomeTableorQuery","FamilyID = " & me.FamilyID)

 

[MajP]

However, your code should work if standard access table or query.
Add in to debug
msgbox me.children.form.recordset.recordcount

hat type of recordsource/recordset is the subform (access query, sql server, ADO recordset, dao recordset)? Is that code on the on current event?

 

[scottrie2]

Thank you, the dcount worked.