Can I Create a Form Control that Displays the Amount of Records?

[warrenpage]

At the bottom of my forms and tables, there is a status bar that indicates the total number of records in that particular form/table.
Just out of curiosity…is there a way to create a control inside a form, that displays the total record number?


For example, here's a screen shot sample of the status bar I'm referring to:



Is there a way to create a text box or some type of form control that will display 117? Or, for example, 57 of 117? If it's possible, what do I need to do to create it? Thank you.
PS: The reason I want to create something like this is so I can have control over the font and size of the number. Warren Page, Access 2013

[JoeM]

Let's say that your Form's control source is a Query named "Query2". Then, on your Form, just add a Text Box, and use the following formula in the Control Source property of that Text Box:
=Dcount("*","Query2")

The Dcount function just counts the number of records in some object (Table, Query). You can add criteria (third argument, but it is not required).
Here is a good write-up on Dcount: http://www.techonthenet.com/access/f...ain/dcount.php

[warrenpage]

Thanks for the information. I tried it on one table so far, and it works fine. I'll dabble more with it, but right now, it suits my needs perfectly. Thank you!

One follow-up question, though: Does the DCount feature work only in forms? Is it possible to put it somewhere in a table? WP

[JoeM]

One follow-up question, though: Does the DCount feature work only in forms? Is it possible to put it somewhere in a table? WP

In the older version of Access (prior to 2010), you could not do any calculations in tables. You could only do them in Queries, Forms, Reports, or VBA.
Even though they now allow you to do them in Tables, many serious programmers still follow the old relational database logic and will not use calculated fields in tables.
Not a bad practice to follow, especially if you ever need to convert your back-end tables to something like SQL or mySQL someday (they will not support calculations in tables).

[Missinglinq]

...serious programmers still follow the old relational database logic and will not use calculated fields in tables...

Excellent point! Tables really should be used for one purpose only, to store data, not to manipulate it! Manipulation always carries with it the possibility of corruption rearing its ugly head!

BTW, if you want to emulate the status bar. i.e. to show 'Record X of Y Number of Records,' you can use something like

Private Sub Form_Load()
  DoCmd.GoToRecord , , acLast
  DoCmd.GoToRecord , , acFirst
End Sub

This is needed because the Access Gnomes don't always load the entire RecordSet when a Form first opens. Then to update the count as you move from Record-to-Record:

For a Label

Private Sub Form_Current()
  Me.LabelName.Caption = "Record  " & CurrentRecord & "  Of  " & RecordsetClone.RecordCount & "  Records"
End Sub

For Textbox

Private Sub Form_Current()
  Me.TextboxName = "Record  " & CurrentRecord & "  Of  " & RecordsetClone.RecordCount & "  Records"
End Sub

Linq ;0)>

[warrenpage]

Thanks for the replies. Having this in a table is not necessary for me. I only asked about it purely out of curiosity. The DCount option worked for me quite nicely, right out of the starting gate, first try.

[June7]

However, the DCount is counting directly on the table or query and might not agree with the actual count of records on the form if filtering is applied to form.

Calculated field type (Access 2010 up) in table will not allow use of functions.