calulated field on main after filtering subform

[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.

 

[DStegon via AccessMonster.com]

Make the calculated field on the main form unbound and have code run upon
your filter event that will open a recordset and get .recordcount for the
recordset and then have the code update the value.

I personally hate subforms and connected forms in general. So much easier to
have everything unbound and then fill in values based on the recordsets you
are opening based on the conditions you are setting. More coding... I
understand... but far more controlable.

 

[bhammer]

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.

 

[DStegon via AccessMonster.com]

in your query use SELECT DISTINCT against the field you wish to count.

Example:

SELECT DISTINCT Customers.Address FROM Customers WHERE (blah, blah.. your
filters);

This will give ONLY the addresses that are different, thus the count of
unique values after the filters are applied.. If you add the customers name
and they have the same address but different names then they both will apear
because the two together are unique.