Record Count

[Guest]

I have a subform which displays vendor records within each zip code record on
my form. I would like a count of all records displayed on my subform that
are active with a group = 11. The code below counts all records fitting the
criteria for all zip codes. I need it to apply itself to each zip code
record so it recalculates for each zip code record. Any ideas how I can do
this or where to place the code?

=DCount("[Vendor_Nbr]","qry_Zip_Code_Vendors","[Status_Code] = 'Active' and
[Peer_Group_Code] =11 ")

 

[Duncan Bachen]

I have a subform which displays vendor records within each zip code record on
my form. I would like a count of all records displayed on my subform that
are active with a group = 11. The code below counts all records fitting the
criteria for all zip codes. I need it to apply itself to each zip code
record so it recalculates for each zip code record. Any ideas how I can do
this or where to place the code?

=DCount("[Vendor_Nbr]","qry_Zip_Code_Vendors","[Status_Code] = 'Active' and
[Peer_Group_Code] =11 ")

Use the Form_Current event of your main form. This event will fire on
each zipcode (each record) as you move through the various records.

Create a textbox on the mainform, and set the value of the textbox equal
to the results of the Dlookup.

txtNumVendors = "There are " &
DCount("[Vendor_Nbr]","qry_Zip_Code_Vendors","[Status_Code] = 'Active'
and [Peer_Group_Code] =11 ") & " vendors."

If Peer_Group_Code changes for each zip code, you'll need to replace the
11 with a reference to the field which contains the Peer Code.