Count Values in Subform

Hello,

The subform in my database is called, "subAllEmployees".  I would like either a text box or a label box - which shows the count of records in the subform.

I don't know if you'll need to know this, but the subform records change - without the form being opened/closed.  I'm kinda figuring that maybe the count, will only count on the form open, & not change until it's reopened.  So, it will have to notice changes within the subform real time.

Thanks,
Jay
xp310Asked:
Who is Participating?
 
harfangCommented:
I forgot another simple solution. The above would be more appropriate for a Sum.

You can simply add a textbox with this control source:

    = <name of sub control>.Form.Recordset.RecordCount

Cheers!
(°v°)
0
 
jefftwilleyCommented:
Couple things here...

To do this, your subform has to be opened in form view...vs datasheet view.
You'll need a control, like a list box or label, either one will work.
Create an On_Timer event for your subform that looks like this.

Private Sub Form_Timer()
dim YourControlName as long
YourControlName = DCount("YourField", "Your Table", "Criteria") & 0   'Adding the zero at the end in case of null
end sub

Set the Timer Interval property of your subform to whatever value you want....60000 refreshes about every minute

Might need to know more about how your forms are linked and any filtering that's going on to get it perfect...let me know
J
0
 
peter57rCommented:
Hi xp310,

On your main form, put an unbound textbox.
Set its controlsource to something like:

=Dcount("*", subformrecordsourcename, "ChildLinkFieldname= " & txtControlContainingParentLinkField)

For example:
=DCount("*","[order details]","Orderid = " & [txtorderid])


Pete
0
 
harfangCommented:
Hello xp310

The normal way to do this would be:
 • open your subform in design view
 • create a textbox with Control Source: = Count(*)
 • name it for example txtCount

If your form is displayed as datasheet, this count will not be visible, so:
 • create a textbox on your main form
 • use Control Source: = <name of sub control>!txtCount

You now have a dynamic counter of the records in the subform. Note that you get the same information in the subform's navigation buttons ("record 3 of 12"). So for debugging purposes, the simple way is just to turn navigation buttons back on for the subform.

The count will be of records in the recordset, including any records deleted from somewhere or by someone else (showing #deleted in every field), and lacking those added elsewhere. If you add or delete from the subform, the count is updated automatically, of course.

If you need to refresh the recordset (and hence the count), you can requery the subform.

Cheers!
(°v°)
0
 
xp310Author Commented:
Hi Everyone!

Thank you all for your input.

Jefftwilley = I didn't test your code, so I didn't know if it worked or not - but I wanted to hold off, cause I felt there might of been an easier solution being posted.

Peter57r = I tried using your code, but my text box kept resulting in:  #Name? - it very well could of been the values I was inputting into the code.  I get screwed up on this stuff easily.

Harfang = I will be selecting your answer as the correct one.  In looking at it, I only had one value to change and it does exactly what I want it to.

Thank you everyone!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.