Table of contents in report 1

[fmientjes]

I have a report showing multiple chapters and paragraphs:

Chapter 1
Chapter 2
Paragraph 2.1
Paragraph 2.2
Paragraph 2.3
......(number of paragraphs may vary)
Chapter 3

The paragraphs are in the detail section and have a Force New Page after section. Chapter 1 is on the Report header and Chapter 3 is on the Report footer.

Now I want to create a table of contents whith the right page numbers. But the number of paragraphs is variable, so I don't know up front what Chapter 3 has as page number.

How to solve this?
Thanks for your help,
Frans

 

[kjv1611]

Never tried it before. Have you tried looking into using the bookmark property with VBA? If you have your paragraphs in a table somewhere, you could use the bookmark property, possibly (it says can be used with forms - so I would imagine it might work with reports as well), to point to each item. As far as the page numbers, try looking at the Pages property - it says it works for forms and reports.

 

[MajP]

I would post this in the Access Reports forum if you have not done that yet. This would be my approach, but this is an interesting idea and maybe there is an easier way.

I would assume that this has to happen dynamically. So using the reports page event, I would return the value of the "paragraph field" and the value of the current page and then use ado or DAO to write to a table. Then I would build a seperate report off this table. I doubt there is anyway you could do this internally to the report.

 

[MajP]

This works pretty well

I made a table called tblTOC with a text field and a page number field:
tblTOC
strTOCentry
intPageNumber

Now I open the report and write to the table.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Static repeatName As String
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblTOC", dbOpenDynaset)
If Not Me.strLastName = repeatName Then
rs.AddNew
repeatName = Me.strLastName
rs.Fields("strTOCEntry") = Me.strLastName
rs.Fields("intPageNumber") = Me.Page
rs.Update
End If
End Sub

This table lists each person name and the page on which they appear.

Now from that table I can build a TOC. To put it at the end of the report put it in as a subform. Not sure How you would get it in to the beginning

 

[MajP]

I put the subform in the report header and then had to add 1 to the page number(even though the TOC was more than a page)

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  Static repeatName As String
  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset("tblTOC", dbOpenDynaset)
  If Not Me.strLastName = repeatName Then
    rs.AddNew
    repeatName = Me.strLastName
    rs.Fields("strTOCEntry") = Me.strLastName
    rs.Fields("intPageNumber") = Me.Page + 1
    rs.Update
  End If
End Sub

Private Sub Report_Open(Cancel As Integer)
DoCmd.SetWarnings (False)
DoCmd.RunSQL "delete * from tblTOC"
DoCmd.SetWarnings (True)
End Sub

When the report opens is deletes the old TOC table, and builds a new one before the report renders. So you can put it in the beginning. Mayber someone has a better way, but this works well.

 

[TheAceMan1]

How are ya fmientjes . . .

How to Create a Table of Contents or Index for a Report

See Ya! . . . . . .

 

[fmientjes]

Sorry about my late reaction (holiday).

Thanks for your efforts. This will do the trick.
Exactly what I want.

Frans

 

[hunarch]

MajP,

I am an absolute novice.
Could you please tell me how I "write to the table"? Is the sub-form based on tblTOC?
Many thanks
RodH

 

[hunarch]

TheAceMan1,
The MS link you have provided states that it applies to MS Access 2000. It was reviewed in 2005. Is it safe to assume that it applies to Access 2003?
I have followed the procedure accurately (and had someone else check as well) but, on print review, I receive an error message "Run-time error '3219', Invalid Operation". When I open debug it suggests that it is stalling on the line of the module: "Set TocTable=db.OpenRecordset("tblTableOfContents", dbOpenTable)".
What might I be doing incorrectly?
RodH

 

[TheAceMan1]

hunarch . . .

The code requires [purple]Microsoft DAO 3.6 Object Library[/purple] to run. To [blue]check/install[/blue] the library, in any code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Then using the up arrow, [purple]push it up as high in priority as it will go[/purple]. Click OK.

With the reference above and looking at the code, I see no reason the code should'nt work across the board.

Also double check the table name!

See Ya! . . . . . .

 

[hunarch]

Thank you 'AceMan1.
I have followed your advice. However, I have now managed to get a TOC working. There were a number of errors I sorted out: correcting some spelling mistakes; making sure that field types matched; replacing a linked TOC table with one that is in the parent database; and recognisnig which of several tables was actually being drawn upon for the TOC.

However, I am still in need of additional TOCs for other reports that are a bit more involved. I have a premonition (wild and desperate guess)that MajP's procedure might work for these other reports. Would you mind responding on MajP's behalf to my request of MajP?

It is coming close to midnight here. What operating hours do you have ahead of you (so that I may pursue further assistance!)?
RodH

 

[hunarch]

I had someone else design my database back-end and I have been adding to it since then (using baling twine and rubber bands). I am limited in my understanding of the design and even more limited in being able to explain it. Neverthless: I have a single table that my TOC (courtesy of MS Article 210269 above) draws on. This is located in the public part of the database. But I need to draw upon a number of other tables. I am hoping that this might be possible by using code in the private part of the database - hence my interest in MajP's code. Er, have I provided sufficient information for a tentative answer?
RodH

 

[hunarch]

Nested TOC
I have adapted the procedure in MS Article 210269 (How to Create a Table of Contents or Index for a Report - See above) so that my report writes to four tables. The aim is to have a TOC with up to four nested levels.

It seems to make sense to rename "Description" in the module to, e.g. Descr1, Descr2, Descr3 and Descr4 (corresponding with the sane names in the four tables that the report writes to: tblTOC1, rptTOC2, rptTOC3, rptTOC4). However, names other than "Description" cause an error message - as if it is a preserved term in MS Access. Does anyone know why this might be and how I can change "Description"?
RodH