Table of Contents stopped working (MDB -> ADP)

[Beowulf]

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

[Beowulf]

I've been fiddling around with the table of contents some more.
It still does not show up in the print preview, but when the data is
finally printed, the table shows up.

The only thing I can figure is that an ADP file will cache table data to
cut down on network traffic. Some the main report opens up, then the
table of contents sub-report is opened and its data is pulled. However,
since we just opened the main report, the recordsource for the subreport
is empty. Then after the report has finished loading in print preview,
the data is not fetched again when I navigate to page 2 (which is the
page with the table of contents on it). In the MDB file, the data for a
page is fetched everytime you navigate to it (or at least more
frequently) than it is in an ADP file.

Beowulf wrote:

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