How count the number of records in a subform

On a main form I have a text box.  In that text box I want to put the number of records represented in the sub-form on the main form.

How can I do this?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
I usually use the main forms Timer event.  Something like:

Private sub Form_Timer()

    'reset the timer interval so it doesn't keep doing this
    me.timerinterval = 0

    'I use a label, not a text box
    me.lbl_Records.Caption = me.subFormControlName.RecordsetClone.Recordcount

End Sub

Open in new window

Then, in the Form_Load event, I set the TimerInterval to 100.  I also use the ApplyFilter event associated with the subform, so that if the user filters the subform, it will update the timer interval on the parent form.
Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)

    me.parent.timerinterval = 100

End Sub

Open in new window

Gustav BrockCIOCommented:
That's a good method.

You could also create a textbox on the subform with controlsource: =Count(*)
Name it, say, CountID.
Then, on the parent, have a textbox referring to that: =[subformControlName].Form!CountID

SteveL13Author Commented:

I have a textbox on the subform named CountID.    But I have to have the control source be =Count([ID]) or I get the count of all records in the database.

But I cannot get this to work on the parent:

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Gustav BrockCIOCommented:
It should be:




And you may have to type this into the property sheet of the control, not in the control itself.

If too much trouble, use Dale's method.

SteveL13Author Commented:
Using Dale's method:

I get an error on this line when I try to compile:   Method or data member not found on txtCountID

Me.lbl_Records.Caption = Me.txtCountID.RecordsetClone.RecordCount
Gustav BrockCIOCommented:
I think it should read:

     Me!lbl_Records.Caption = Me!subFormControlName.Form.RecordsetClone.Recordcount

and I doubt your subform control is named txtCountID ...

Dale FyeOwner, Developing Solutions LLCCommented:
replace "subFormControlName" with the "name" of the subform control.  This might be the actual name of the subform (which is generally the default), or it might be a name that you gave the control.  

I always change my subform control names to "sub_datasheet" or "sub_Form", so  I know how to refer to the control without too much effort.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.