[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Count Values in Subform

Posted on 2006-05-31
Medium Priority
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

ID: 16800782
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

ID: 16800810
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

ID: 16804836
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

harfang earned 2000 total points
ID: 16804841
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

ID: 16809423
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


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

834 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