Count Values in Subform

Posted on 2006-05-31
Last Modified: 2008-02-01

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.

Question by:xp310
    LVL 34

    Expert Comment

    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
    LVL 77

    Expert Comment

    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])

    LVL 58

    Expert Comment

    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.

    LVL 58

    Accepted Solution

    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


    Author Comment

    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!

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now