I have a report laid out in Design View as shown at the end of this message.

I have code that performs the following steps:

1. In main report's Report_Open(), DELETE any old rows in tblTOC for
this username.
2. In main report's CategoryHeader_Format(), add a row to tblTOC with
the current category name and the current page number.
3. In the table of contents subreport, Cancel if NoData event fires.

When this report was in an MDB file, if Text12 had the datasource =
"[Page] of [Pages]" the report would run through the code for all the
data and properly fill in tblTOC with page numbers each time the
CategoryHeader_Format() event fired.  So that when the page with
FormVersionHeader on it was viewed, the table of contents would be
properly displayed.

I upsized this database to a SQL server backend / ADP front end and now
the page with FormVersionHeader on it is blank.  My assumption is that
the report cancels the first time through because tblTOC is blank.
However, in the MDB file, once the report was open in preview mode, the
table of contents would get filled in the second time you navigated to
the page with FormVersionHeader on it.  This second rendering doesn't
seem to be happening in the ADP file.  Does anyone have any suggestions
on why this might occur?

Code snippets:

rptMain:

Private Sub Report_Open(Cancel as Integer)

    ' Setup the table of contents
    If UCase(Me.ShowTOC.Caption) <> "Y" Then
      With Me
        .Text12.ControlSource = "=""Page "" & [Page]"
        .grpFormVersionHeader.Visible = False
      End With
    Else
      With Me
        .Text12.ControlSource = "=""Page "" & [Page] & "" of "" & [Pages]"
        .grpFormVersionHeader.Visible = True
      End With

      ' Clear out old table of contents
      Set cmd = New ADODB.Command
      With cmd
        Set .ActiveConnection = Application.CurrentProject.Connection
        .CommandType = adCmdText
        .CommandText = "DELETE FROM " & sTOC_TABLE & " WHERE
Username='" & sUsername & "'"
        .Execute
      End With
      Set cmd = Nothing
    End If

End Sub

Private Sub grpCategoryHeader_Format(Cancel As Integer, FormatCount As
Integer)

  ' Update the table of contents, if necessary
  If UCase(Me.ShowTOC.Caption) = "Y" Then
    AddToTOC sTOC_TABLE, sUsername, CStr(Me.txtCategoryHeader),
CLng(Me.Page)
  End If

End Sub

Public Sub AddToTOC(sTocTable As String, sUsername As String, sText As
String, lPageNumber As Long)

  Dim rs As ADODB.Recordset
  Dim sTableEntry As String

  Set rs = New ADODB.Recordset
  With rs

    Set .ActiveConnection = Application.CurrentProject.Connection
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic

    .Open "SELECT * FROM " & sTocTable & " WHERE Username='" &
sUsername & "'"

    ' Save category name
    sTableEntry = Mid(Trim(sText), 1, .Fields("TableEntry").DefinedSize)
    .Filter = "TableEntry='" & sTableEntry & "'"

    ' If we haven't stored the pagenumber yet, save it to the table
    If .RecordCount = 0 Then
      .AddNew
      .Fields("TableEntry").Value = sTableEntry
      .Fields("PageNumber").Value = lPageNumber
      .Fields("Username").Value = sUsername
      .Update
    End If

    .Filter = adFilterNone
    .Close
  End With
  Set rs = Nothing
End Sub

rptTOC:

Private Sub Report_NoData(Cancel As Integer)
  Cancel = CInt(True)
End Sub

Report layout:

ReportHeader - acts as a cover sheet
------------
Label1 - datestamp
Label2 - database path

FormVersionHeader (FormVersion is a date stamp, all records in
recordsource have the same value)
---------

Subreport1 - based on the table of contents report, which is based on
the table, tblTOC.

CategoryHeader
---------------
Textbox1 - Displays category name in bold

ItemHeader
----------
Textbox2 - Item name
Textbox3 - Item description

Detail
------
contains several subreports that show details about the item

PageFooter
----------

Textbox12 - contains the page number info