How to display a query record count in a form control

Question

I have a query that returns a fluid # of records, depending on criteria selected in the form. I would like to display the total # of records returned to the form.

I have added a unbound text field to the footer in the form that is displaying the controls and resulting records. I tried the following expressions in the text field, both of which result in #error:

=Count([qrnname]![fieldtocount])
=DCount([qrnname]![fieldtocount])

This should be simple.

Answer 1

Don't break your head with complications:

RecordN = Me.Recordset.RecordCount

Answer 2

An other alternative is to use =Count(primaryKey) in the Control Source property

It seems better if you have some filter on your original query, so you don't have to apply them again in the DCount (expression, domain, [criteria]) function.

A quick method for counting Access records in a form

Answer 3

DCount requires string values for its arguments. Assuming fieldtocount is the name of a field returned by the named query qrnname, use this as your text box's Control Source ...

=DCount("[fieldtocount]", "qrnname")

Since that query depends on criteria selected in the form, Requery the text box whenever those criteria change to update the count displayed in the text box.

Answer 4

use this =DCount([fieldtocount]![qrnname])

The syntax for the DCount function is:

DCount ( expression, domain, [criteria] )

expression is the field that you use to count the number of records.

domain is the set of records. This can be a table or a query name.

criteria is optional. It is the WHERE clause to apply to the domain.

Dcount in detail