Count Records X of Y across Records on Form

Question

I have a Form with fields StudentID, StartDate, TxDate (Treatment Date) among c.60 other fields. There is a Go To Student drop-down box in the Form's Header that establishes a subset for the StudentID selected records only when a particular StudentID is selected from the drop-down.

There are buttons to click to First, Previous, Next, Last

I want to show on the Form, as you click First, Previous, Next, or Last, and go to the Next record, the number record for that individual StudentID. That is, if the Student had 6 records, it would display 1 of 6 for their first record, 2 of 6 for the Next, 3 of 6 for the Next....

I've tried things like DCount('RecordNo","Depression Form Query","Student") which counts all the records in the Data Table, not just the individual StudentID's.

I used an Autonumber in the Students Table but it generates a number equivalent to the StudentID being the 3rd Student entered, not a count of their Treatment Dates.

I've tried various loopings I've found on the net but they do not count across records for the same Student.

I made an X of Y Report which I then dragged and dropped into the Form. It generates the correct Y (6) but an incorrect X.

[ enter image description here ]

The Depression Form Query is based on the Students Table and the Depression Table.

Please understand, I am counting across Forms (records), not within the same record. StudentID = 8131999, Form's Record No = 356 (count would be "1"), clicking the Next button programmed by me on my Form takes me to another instance for StudentID 8131999, Form's Record No = 461 (count would be "2"), clicking the Next button programmed by me on my Form takes me to another instance for StudentID = 8131999, Form's Record No = 826 (count would be "3"), etc. Thanks — Vancer

June7 June7 · Accepted Answer · 2017-05-22T19:24:44

I am aware you want to count records. The Allen Browne link is discussing counting records.

Your DCount() would have to be:

DCount("*","[Depression Form Query]","StudentID=" & [StudentID] & " AND RecordID < " & [RecordID]) + 1

Just realized the Allen Browne link doesn't really go into use of DCount for this, just barely mentions it to advise that it can be very slow. And it can be.

If you apply filter criteria to the query, the same filter criteria would have to be constructed within the DCount() WHERE argument.

A nested query to rank within grouping would be even more complicated than the examples in the link.

A form is just not a practical vehicle for this calc. Better would be to build a report using its Grouping & Sorting feature and textbox RunningSum property.

Count Records X of Y across Records on Form

1 Answers