count records

[SueBK]

Thought I posted this, but I can't find it anywhere. Bizarre.

I have a table (with a form for data entry), fields are:
* ID
* Project Aspect (a list of 10 set items)
* Status (red/orange/green)
* Month

I have a 2nd table, fields are:
* ID
* AspectID (from the first table)
* Comment, Action, By Whom, By When (all separate fields)

The reason for the two separate fields is that each month each aspect must have a single status, but can have multiple comments.

Form 1 is a continous form that allows the user to update the status for each Aspect. Each record has a command button that filters on the Aspect ID field, to open a form for the 2nd table.

I would like (if possible) to have an information box on the first form that shows how many comments are recorded against a given aspect for a given month.

 

[John Big Booty]

I'm not sure if this is what you are looking for but if you set the forms "Navigation Buttons" properties to Yes you will get that information i.e. record 1 of n.

 

[John Big Booty]

Alternatively you could use an unbound text box with the Control Source set to;

=Count([Comment])

 

[SueBK]

I finally managed (with a great deal of input from someone else) to write a Control Source equation that counts the number of records in the comments table, filtered by the particular aspect and the reporting month. Works beautifully.

My equation looks like:
=DCount("[Commentid]","tblStatusComments","[RptID]=" & Str([RptID]) & " AND [StatusID]=" & Str([StatusID]))

Where: tblStatusComments is the table the comments are stored in.
CommentID is the primary key of that table
RptId is the reporting month
StatusID is the primary key of the table containing the aspects and their status