Counting Records (and sheep?)

[EricTheRed]

Another question / yes indeed / some brilliant answers / that's what I need.

Sorry, but it's been a long day. You can't blame an aspiring poet!

Anyhow, I'm having trouble counting records. Here's the situation: For every person in my database, there is either zero, one, or two sets of "contact information" associated with him or her. This information is displayed as a subform on the primary "person" form. I'd like to be able to determine (from inside the subform) the number of contact information records associated with each person. Is there any easy way to accomplish this? I have tried "Me.Recordset.RecordCount", however it requires one to iterate through all the records before it produces an accurate result.* "DCount" is also an option, although I don't want to have to sift through the entire table (several thousand records eventually) to divine the existence of one or two. Currently I'm using

While Not Me.Recordset.EOF
Me.Recordset.MoveNext
Wend

followed by RecordCount to determine the number of records. This, however, strikes me as a somewhat ugly solution. Is there some better way I could be doing this? Thanks in advance for any help.

* This produced much frustration. Grr. I was very confused to have it calmly report the existence of only one record when I was very, very sure there were two.

 

[WayneRyan]

Eric,

You could make a new unbound control and make its ControlSource:

qryCountContacts

Where that query is a "Count(*)" query on your subform table,
whose PK field criteria is:

=Forms![YourMainForm]![YourSubForm].Form![YourPK]

It should be OK, and you could add a requery on subform
deletions/additions.

Wayne

 

[EricTheRed]

Thanks for the help. I used a slightly modified solution:

SELECT COUNT(*) FROM tblContactInformation WHERE ContactID=Forms!frmContact!ContactID;

which seems to work just as well. It counts the number of contact information records with the same ContactID as the main contact being viewed. I believe this'll work alright, though I'd appreciate it if someone points out any fatal flaw before I present this Thurdsay!

 

[WayneRyan]

Eric,

That will work as long as your main and subform are joined
on that field.

Wayne

 

[EricTheRed]

Yes, they are, so that's good. Though, while you're around, I have [yet another!] quick question. When I define the ControlSource of the text box to be qryCountCInfo, all I get is #Name? I assume that this means it can't find the query, or somesuch, so I altered it slightly:

SELECT COUNT(*) AS RCount FROM tblContactInformation WHERE ContactID=Forms!frmContact!ContactID;
...then set ControlSource to qryCountCInfo!RCount

but to no avail. Interestingly enough, however, if I manually run the query while my main contact form is visible, it works perfectly. Huh. Help, please!

 

[Rich]

=Count(*) in an unbound text box in the subform footer will do what you want

 

[EricTheRed]

Perfect. Thank you so much. I may actually finish this thing afterall!