Reports - creating a table of contents

"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.