Help Counting Records to Display on a Form or Report

[b.richardson]

I would like to count the number of records corresponding to a specific field. I have two tables involved.

One table [client table] contains people and a bunch of info, including the organization they are associated with.
Second table [agency table] contains details about agencies.

When filling in details about a client, users can pull an agency name from a drop down, which comes from [agency table].

I also have a form for adding new agencies. On that form, I would like to add a field that displays the number of records, from the [client table], of people assigned to the specific org displayed on the form at that time.

For instance, if the user is looking at the all the info for Agency A, there would be a field that says "Number of Active Clients", which would count the number of records from the [client table] with a user assigned to Agency A.

I hope that makes sense. Sorry if it's too much info. This is my first "phone-a-friend" lifeline request and I want to give as much info as possible to hopefully get some help. Thanks in advance!!

[Bulzie]

Look up DCount function.

[b.richardson]

I figured it would be DCount. Should have said that. I've tried using it but just get #Error. I'm not sure how to tie the field on the form to the field in the table, or if that's even possible.
Here's what I've tried: =DCount("[tblFullClientList.PrimaryAgencyAssigned]","tblFullClientList","[fldPrimaryAgencyAssigned] = Forms!frmAgencyInfo!fldNonProfitCivicOrgName")

[Bulzie]

What error are you getting? That looks like it should work as far as the syntax.

[b.richardson]

When I open the form, the field just says "#Error". I don't get any errors when I build the expression. I thought maybe I had a reference wrong or something since it seems the calculation isn't working (at least I think that's what's happening based on the #Error in the field. Thanks!

[davegri]

If everything is on the same form

=DCount("[PrimaryAgencyAssigned]","tblFullClientList","[fldPrimaryAgencyAssigned] = '" &  [fldNonProfitCivicOrgName] & "'")

Reply Reply With Quote