Index page for report

[errolf]

I have a report and I use the the following function to create an index
Option Compare Database
Option Explicit


Dim db As Database
Dim toctable As Recordset

Function InitToc()
' Called from the OnOpen property of the report.
' Opens the database and the table for the report.
Dim qd As QueryDef
Set db = CurrentDb()
Set qd = db.CreateQueryDef("", "Delete * From [Table Of Contents]&quot
qd.Execute
qd.Close
' Opens the table.
Set toctable = db.OpenRecordset("Table Of Contents", dbOpenTable)
toctable.Index = "Description"

End Function

Function UpdateToc(tocentry 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.

toctable.Seek ">", tocentry

If toctable.NoMatch Then
toctable.AddNew
toctable!Description = tocentry
toctable![page number] = Rpt.Page
toctable.UPDATE
End If

End Function


The problem that I have is that if the product heading spans more than one page I get multiple entries on the table created eg:
Description Page Number

Fertilisers Retail 2
Fertilisers Retail 3
Fertilisers Retail 4
Frost Protection & Anti Transpirants 5
Frost Protection & Anti Transpirants 6
Fungicides And Bactericides (Commercial) 6
Fungicides And Bactericides (Commercial) 7
What I need is a function to concatenate the pages like:-
Description Page Number
Fertilisers Retail 2,3,4
Frost Protection & Anti Transpirants 5,6
Fungicides And Bactericides (Commercial) 6,7
Can anyone help with this problem
Errolf

 

[JerryDennison]

Would it work to append the unique descriptions to another table and then iterate through the ones in this table concatenating and updating the page numbers to a single field for each description of the newly appended unique descriptions table?

 

[MichaelRed]

Function UpdateToc(tocentry 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.

toctable.Seek ">", tocentry

If toctable.NoMatch Then
toctable.AddNew
toctable!Description = tocentry
toctable![page number] = Rpt.Page
toctable.UPDATE
End If

End Function

If, "tocentry' is the heading text, then shouldn't you be using "=" as the criteria in the seek statement?



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over