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
FormVersionHead er 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 FormVersionHead er 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 FormVersionHead er 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(Can cel as Integer)
' Setup the table of contents
If UCase(Me.ShowTO C.Caption) <> "Y" Then
With Me
.Text12.Control Source = "=""Page "" & [Page]"
.grpFormVersion Header.Visible = False
End With
Else
With Me
.Text12.Control Source = "=""Page "" & [Page] & "" of "" & [Pages]"
.grpFormVersion Header.Visible = True
End With

' Clear out old table of contents
Set cmd = New ADODB.Command
With cmd
Set .ActiveConnecti on = Application.Cur rentProject.Con nection
.CommandType = adCmdText
.CommandText = "DELETE FROM " & sTOC_TABLE & " WHERE
Username='" & sUsername & "'"
.Execute
End With
Set cmd = Nothing
End If

End Sub

Private Sub grpCategoryHead er_Format(Cance l As Integer, FormatCount As
Integer)

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

End Sub

Public Sub AddToTOC(sTocTa ble 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 .ActiveConnecti on = Application.Cur rentProject.Con nection
.CursorType = adOpenKeyset
.LockType = adLockOptimisti c

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

' Save category name
sTableEntry = Mid(Trim(sText) , 1, .Fields("TableE ntry").DefinedS ize)
.Filter = "TableEntry ='" & sTableEntry & "'"

' If we haven't stored the pagenumber yet, save it to the table
If .RecordCount = 0 Then
.AddNew
.Fields("TableE ntry").Value = sTableEntry
.Fields("PageNu mber").Value = lPageNumber
.Fields("Userna me").Value = sUsername
.Update
End If

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

rptTOC:

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

Report layout:

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

FormVersionHead er (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
FormVersionHead er 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 FormVersionHead er 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 FormVersionHead er 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(Can cel as Integer)
' Setup the table of contents
If UCase(Me.ShowTO C.Caption) <> "Y" Then
With Me
.Text12.Control Source = "=""Page "" & [Page]"
.grpFormVersion Header.Visible = False
End With
Else
With Me
.Text12.Control Source = "=""Page "" & [Page] & "" of "" & [Pages]"
.grpFormVersion Header.Visible = True
End With

' Clear out old table of contents
Set cmd = New ADODB.Command
With cmd
Set .ActiveConnecti on = Application.Cur rentProject.Con nection
.CommandType = adCmdText
.CommandText = "DELETE FROM " & sTOC_TABLE & " WHERE Username='"
& sUsername & "'"
.Execute
End With
Set cmd = Nothing
End If

End Sub

Private Sub grpCategoryHead er_Format(Cance l As Integer, FormatCount As
Integer)

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

End Sub

Public Sub AddToTOC(sTocTa ble 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 .ActiveConnecti on = Application.Cur rentProject.Con nection
.CursorType = adOpenKeyset
.LockType = adLockOptimisti c

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

' Save category name
sTableEntry = Mid(Trim(sText) , 1, .Fields("TableE ntry").DefinedS ize)
.Filter = "TableEntry ='" & sTableEntry & "'"

' If we haven't stored the pagenumber yet, save it to the table
If .RecordCount = 0 Then
.AddNew
.Fields("TableE ntry").Value = sTableEntry
.Fields("PageNu mber").Value = lPageNumber
.Fields("Userna me").Value = sUsername
.Update
End If

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

rptTOC:

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

Report layout:

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

FormVersionHead er (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