Create Table of Contents report from another report's recordset.

[rommie1]

Hi Folks,

I've previously created a Table of Contents using one field from another report's recordset. Now, (of course) my user wants additional information. This is a drug database that keeps up with various info including Generic name and Brand name. My current Table of Contents is for Generic name and the user wants Brand listed in the same column as well. My problem is that when I run the source report the function only populates the Brand field in the aatoc table for the first page of the source report but the Generic field is populated just fine. Here are my functions. Any suggestions would be greatly appreciated.

Option Compare Database
Option Explicit


Dim DB As DAO.Database
Dim TocTable As Recordset
Dim TocTable_B As Recordset
Dim TocEntry As String
Dim TocEntry_B As String

Function InitToc()
' Called from the OnOpen property of the report.
' Opens the database and the table for the report.

Set DB = CurrentDb()
DB.Execute "DELETE * FROM [aatoc];"
'Delete all previous entries in "aatoc" table.


Set TocTable = DB.OpenRecordset("aatoc", dbOpenTable)
TocTable.INDEX = "Generic"

Set TocTable_B = DB.OpenRecordset("aatoc", dbOpenTable)
TocTable_B.INDEX = "BRAND"

End Function

Function UpdateToc(TocEntry As String, TocEntry_B As String, Rpt As Report)

' Called from the OnPrint property of the section containing
' the Table Of Contents entry. Updates the Table Of Contents
' entry in the "aatoc" table.

TocTable.Seek ">", TocEntry
TocTable_B.Seek ">", TocEntry_B

If TocTable.NoMatch Then
TocTable.AddNew
TocTable!Generic = TocEntry
TocTable![PAGE NUMBER] = Rpt.PAGE
TocTable.Update
End If

If TocTable_B.NoMatch Then
TocTable_B.AddNew
TocTable_B!BRAND = TocEntry_B
TocTable_B![PAGE NUMBER] = Rpt.PAGE
TocTable_B.Update
End If
End Function

 

[rommie1]

Well, I played around with it a little more and figured it out. So, thanks anyway. FYI the code is below. Oh, sorry I didn't surround the code in my original post with tags.

In order to get both fields in the same column without duplicates I created two queries.

1. One make table query creates the table of contents table and it's SQL statement is ( SELECT aatoc.Generic AS Alphalst, First(aatoc.[PAGE NUMBER]) AS PageNo INTO tblTableofContents
FROM aatoc
GROUP BY aatoc.Generic;

2. The other is an update query to update the new tblTableofContents.Alphalst field with the Brand names and their corresponding page numbers. It's SQL statement is ( INSERT INTO tblTableofContents ( PageNo, Alphalst )
SELECT First(aatoc.[PAGE NUMBER]) AS [FirstOfPAGE NUMBER], aatoc.BRAND
FROM aatoc
GROUP BY aatoc.BRAND;

Option Compare Database
Option Explicit


Dim DB As DAO.Database
Dim TocTable As Recordset
Dim TocEntry As String
Dim TocEntry_B As String

Function InitToc()
    ' Called from the OnOpen property of the report.
    ' Opens the database and the table for the report.
    
   Set DB = CurrentDb()
    DB.Execute "DELETE * FROM [aatoc];"
    'Delete all previous entries in "aatoc" table.
      
     
     Set TocTable = DB.OpenRecordset("aatoc", dbOpenTable)
     TocTable.INDEX = "Generic"
     
    
End Function
         
Function UpdateToc(TocEntry As String, TocEntry_B As String, Rpt As Report)

    ' Called from the OnPrint property of the report detail section containing
    ' the Table Of Contents entry. Updates the Table Of Contents
    ' entries Description, BRAND and PAGE_NUMBER in the "aatoc" table.  [b]Make sure there are not NULL values in either field or you will get a data type mismatch error because the variables TocEntry and TocEntry_B are dimensioned as Strings which can't contain NULL values.[/b]



    TocTable.Seek ">", TocEntry
    
    
    If TocTable.NoMatch Then
        TocTable.AddNew
        TocTable!Generic = TocEntry
        TocTable!BRAND = TocEntry_B
        TocTable![PAGE_NUMBER] = Rpt.PAGE
        TocTable.Update
        
    End If
      
End Function