"The client would like the report to have a Table of Contents listing all the individuals listed within the report itself alphabetically (no problem), and with page numbers (problem). Not just a single page number, if the individual shows up on multiple pages he wants all of the pages numbers listed next to each name."
Create a table with two fields, call it tblReportPages:
ThePageNumber (long integer) TheValue (text)Assume that the individual's name is in the field Field2 on the report. Here's the code:
Option Compare Database Option Explicit Public HighestPage As Long Private Sub Report_Open(Cancel As Integer) HighestPage = 0 DoCmd.SetWarnings False DoCmd.RunSQL "DELETE * FROM tblReportPages;" DoCmd.SetWarnings True End Sub Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) If [Page] >= HighestPage Then If FormatCount = 1 Then Dim db As Database, rst As Recordset Set db = CurrentDb Set rst = db.OpenRecordset("tblReportPages", dbOpenDynaset) rst.AddNew rst!ThePageNumber = [Page] rst!TheValue = Me.Field2 rst.Update rst.Close Set rst = Nothing Set db = Nothing End If HighestPage = [Page] End If End Sub
When finished, the tblReportPages holds all of the individuals referenced with the page number. You would then sort them alphabetically and print out the index _after_ the report proper.
The only "catch" is that you need to actually print the report, or preview every page of the report, in order for the table to be complete.
How this works:
The first time each detail section is formatted, we're storing the page number and field value in the table. (The format event occurs twice for the first record on each new page, so we ignore all but the 1st.)
However, if you have "page n of m" on your report, the entire formatting process runs twice, so we have a global variable that remembers the highest page number that we've encountered during the report formatting; once we've seen all the pages, we stop writing to the table.