calulated field on main after filtering subform

  • Thread starter Thread starter bhammer
  • Start date Start date
B

bhammer

The user can apply any filter on the subform. I want the calculated field on
the main form to count the number of distinct Addresses with whatever filter
is applied. Is this done with a recordset clone? or query?

Currently in the ControlSource I have,
=DCount("Address","qryUniqueAddresses"), where the query is simply a copy of
the same query as the subform and with only one field, Address, with the
Totals on. This works to count all, but I'm looking for the count after
filtering.
 
My issue is that I don't want .recordcount, but the distinct values of a
field in a recordset that has a filter applied. Also I want the count to
display even when no filter is on, so what Event is that?

I even tried eliminating the main form from the problem by adding a footer
to the subform (datasheet view so it won't show) to calculate the unique
Addresses, but I don't know how to write that expression. If I could get
that, then the main form textbox could simply refer to the footer textbox.
Hmm.
 
Back
Top