Access: Table of Contents Report

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

By Helen Feddema

Access versions: 2007-2013
Level:  Intermediate


Word has a sophisticated Table of Contents feature that automatically creates a table of contents from designated headings in a document.  Access reports don’t have such a feature, but I managed to create one using VBA code, custom database properties and some Word features.

The Orders Report

The sample database for this article, Table of Contents Report (AA 236).accdb, has tables from the old Northwind sample database, and a standard stepped Orders report:

img 54b36e4cd9c1c - Access: Table of Contents Report

This is a long report, so it would be handy to have a table of contents listing the page for each Customer ID.  Here is a high-level description of how I did it:

  1. Export the report to Word RTF format.
  2. Using VBA code working with the RTF document and a recordset of CustomerIDs for customers who have orders, search for each CustomerID and get the number of the page it is on, and save the CustomerID and page number to a table.
  3. Create a subreport (rsubTOC) whose record source is a query based on the table filled with current Customer ID and page number values.
  4. Make a copy of rptOrders (rptOrdersWithTOC), and insert the subreport into the report header.
  5. Make a complex expression to display Roman numerals for the TOC page numbers, and Arabic numerals (starting with 1) for the regular page numbers.

Here is the first page of the report with a Table of Contents:

img 54b36e7894f84 - Access: Table of Contents Report

And here is the first page of the body of the report:

img 54b36e8e182b6 - Access: Table of Contents Report

The procedure listed below does the work of saving the page numbers to the table used as the subreport’s record source:

Public Function GetReportPages()
 On Error GoTo ErrorHandler
    'Close existing RTF file if necessary
    strCurrentPath = Application.CurrentProject.Path & "\"
    strWordRTFFile = strCurrentPath & "Orders.rtf"
    Debug.Print "Word RTF file: " & strWordRTFFile
 On Error Resume Next
    Set doc = appWord.Documents(strWordRTFFile)
    If Not doc Is Nothing Then
       doc.Close savechanges:=wdDoNotSaveChanges
    End If
 On Error GoTo ErrorHandler
    'Export Orders report to Word RTF file
    strReport = "rptOrders"
    DoCmd.OutputTo objecttype:=acOutputReport, _
       objectname:=strReport, _
       outputformat:=acFormatRTF, _
       outputfile:=strWordRTFFile, _
    Set doc = appWord.Documents.Open(strWordRTFFile)
    'Clear old TOC records
    strTOCTable = "tblTOCPageNos"
    strSQL = "DELETE * FROM " & strTOCTable
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    'Set up recordsets of customer codes to search for and table
    'for storing Customer IDs and page numbers
    strQuery = "qryCustomerIDs"
    Set rstSource = CurrentDb.OpenRecordset(strQuery)
    Set rstTOC = CurrentDb.OpenRecordset(strTOCTable)
    appWord.Visible = True
    'Turn off spelling and grammar checking
    With appWord.Options
       .CheckGrammarAsYouType = False
       .CheckGrammarWithSpelling = False
       .ContextualSpeller = False
       .CheckSpellingAsYouType = False
    End With
    Do While Not rstSource.EOF
       strCustomerID = rstSource![CustomerID]
       With rstTOC
          ![CustomerID] = strCustomerID
          'Find Customer ID in Word document
          With appWord.Selection.Find
             .Text = strCustomerID
             .Replacement.Text = ""
             .Forward = True
             .Wrap = wdFindStop
             .Format = True
             .MatchCase = False
             .MatchWholeWord = True
             .MatchWildcards = False
             .MatchSoundsLike = False
             .MatchAllWordForms = False
          End With
          Set sel = appWord.Selection
         'Retrieve page number from Word document
          intPageNumber = sel.Information(wdActiveEndPageNumber)
          Debug.Print "Customer ID " & strCustomerID _
             & " page no.:  " & CStr(intPageNumber)
          ![PageNumber] = intPageNumber
       End With

See download code below for entire code including error handler.

The expression that creates the page numbers references several invisible textboxes in the report footer (the yellow ones – that is my convention for invisible controls):

img 54b36ecf96776 - Access: Table of Contents Report

The page number expression is:

 =IIf(([Page]>[txtTOCPages].[Value])=True,("Page " & [txtCorrectedPageNo].[Value] & " of " & [txtCorrectedNumPages].[Value]),"Page " & RomanNo([txtPage].[Value]))

As is often the case on Access reports, I sometimes found it necessary to reference the value of a textbox, rather than the actual value that is the control source of the textbox.  Here are the control sources of the txtCorrectedPageNo and txtCorrectedNumPages textboxes referenced in the above expression:


txtTOCPages has this control source:


This custom database property is saved from this procedure, which is run from the report’s Open event, which also puts up a message box asking if you want to recreate the Table of Contents, and runs the GetReportPages procedure if you click Yes.

 Public Function GetTOCPages() As Integer
 On Error GoTo ErrorHandler
    'Get number of TOC pages and save to a custom database property
    strReport = "rsubTOC"
    DoCmd.OpenReport reportname:=strReport, _
    Set rpt = Reports(strReport)
    intTOCPages = CInt(rpt![txtNumPages].Value)
    Debug.Print "TOC Pages: " & intTOCPages
    DoCmd.Close objecttype:=acReport, _
    strPropertyName = "TOCPages"
    lngDataType = dbInteger
    Call SetProperty(strPropertyName, lngDataType, intTOCPages)
    Exit Function
    MsgBox "Error No: " & Err.Number _
       & " in GetTOCPages procedure; " _
       & "Description: " & Err.Description
    Resume ErrorHandlerExit
 End Function


The code in the sample database needs the following references (in addition to the default references):

Microsoft Word 14.0 Object Library

Microsoft Forms 2.0 Object Library

Microsoft Scripting Runtime

If you import code or objects into a database of your own, you may need to set one or more of these references.  The version number may differ, depending on your Office version; check the version you have.  References are set in the References dialog, opened from the VBA window.  For more information on working with references, see Access Archon #107, Working with References.

Supporting Files

The zip file containing this article, in Word 97-2003 format, plus the supporting file(s), may be downloaded from the Access Archon page of my Web site, as, which is the last entry in the table of Access Archon columns for Access Watch.

Document Name: Table of Contents Report (AA 236).accdb

Document Type:  Access 2007-2010 database (can also be used in higher versions of Access)

Place in: Wherever you want

subs profile e1563205311409 - Access: Table of Contents Report
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address