Code Help: Table of Contents

[F1stman]

Hello,

I am having some issues with code to create a table of contents table for
later referrencing. I have following instructions from a help page located at
http://support.microsoft.com/kb/q131588/ to the best of my abilities. Please
see the following code modified from this example. It is saved in the
TableofContents Module.

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()
' Delete all previous entries in Table of Contents table.
Set qd = db.CreateQueryDef("", "Delete * From [TableofContents]")
qd.Execute
qd.Close
' Open the table.
Set toctable = db.OpenRecordset("TableOfContents", DB_OPEN_TABLE)
toctable.Index = "Description"
End Function


Function UpdateToc(tocentry As String, Rpt As Report)
' Call from the OnPrint property of the section containing
' the Table Of Contents Description field. Updates the Table Of
' Contents table.
toctable.Seek "=", tocentry
If toctable.NoMatch Then
toctable.AddNew
toctable!Description = tocentry
toctable![page number] = Rpt.Page
toctable.Update
End If
End Function


InitToc() is entered in the OnOpen event of the report (Called "CIP3" and
UpdateToc([Proj_Title],[Report]) is entered in the OnPrint event of the
header. However, I am running into the following errors:

When I open the report, I get the message "Invalid Operation" and the debug
takes me to the "set toctable" line (third from bottom in first part or
InitToc() part of code).

At one point I could avoid this but got a "type mismatch" error for the
OnPrint or UpdateToc() event.

Any help is appreciated. I am desperate at this point and don't really
understand the code I am using.

Thanks, Adam Kemp

 

[pietlinden]

Adam,

What version of Access are you using? If you're using any version
post Access97, then the default library is ADO, not DAO, so you need
to change your references a little bit.

To fix it do the following:
1. open any code module. (code behind a button, any module...)
2. with the VB editor window active, go to Tools, References, and de-
select the ADO 2.x library and select the DAO 3.x library.
3. Close the references window.
4. recompile your code.

If it compiles without error, you should be able to run the code. If
not, post the error number/messages you get after doing this.

HTH,
Pieter

 

[F1stman]

Pieter,

No good. Either I did it wrong (don't think I did) or that solution won't
work. I am trying to get me code to.

OnOpen of report
1) Open the 'Table of Contents' table.
2) Clear the table data.
OnPrint of [Proj_Title] header on report
1) Fill the table with the description with assigned page numbers.

Data to fill description: [Proj_Title]

Please give me any tips you can.

Adam Kemp

 

[Michael Gramelspacher]

Pieter,

No good. Either I did it wrong (don't think I did) or that solution won't
work. I am trying to get me code to.

OnOpen of report
1) Open the 'Table of Contents' table.
2) Clear the table data.
OnPrint of [Proj_Title] header on report
1) Fill the table with the description with assigned page numbers.

Data to fill description: [Proj_Title]

Please give me any tips you can.

Adam Kemp

Is your database split into FE and BE? I seem to remember some issues with
Seek.

I could post the code I use with a split database.

 

[F1stman]

Michael,

Yes, it is split. I would love to have your code for the split DB.

Many thanks,

Adam Kemp

Pieter,

No good. Either I did it wrong (don't think I did) or that solution won't
work. I am trying to get me code to.

OnOpen of report
1) Open the 'Table of Contents' table.
2) Clear the table data.
OnPrint of [Proj_Title] header on report
1) Fill the table with the description with assigned page numbers.

Data to fill description: [Proj_Title]

Please give me any tips you can.

Adam Kemp

Is your database split into FE and BE? I seem to remember some issues with
Seek.

I could post the code I use with a split database.

 

[Michael Gramelspacher]

Michael,

Yes, it is split. I would love to have your code for the split DB.

Many thanks,

Adam Kemp

Pieter,

No good. Either I did it wrong (don't think I did) or that solution won't
work. I am trying to get me code to.

OnOpen of report
1) Open the 'Table of Contents' table.
2) Clear the table data.
OnPrint of [Proj_Title] header on report
1) Fill the table with the description with assigned page numbers.

Data to fill description: [Proj_Title]

Please give me any tips you can.

Adam Kemp

Is your database split into FE and BE? I seem to remember some issues with
Seek.

I could post the code I use with a split database.

I tryied this on my report and printed 1202 pages to a pdf file. The page index
was 40 pages, 2 columns of 52 names. It was 4125 obituaries.

Option Compare Database
Option Explicit

Dim x As Integer
Dim rstIndex As DAO.Recordset

Public Function OpenForSeek(TableName As String) As Recordset
'This code was originally written by Michel Walsh.It is not to be altered
'or distributed, except as part of an application. You are free to use it
'in any application,provided the copyright notice is left unchanged.
'
'Code Courtesy of Michel Walsh

' Assume MS-ACCESS table
Set OpenForSeek = DBEngine.Workspaces(0).OpenDatabase _
(Mid(CurrentDb().TableDefs(TableName).Connect, _
11), False, False, "").OpenRecordset(TableName, _
dbOpenTable)
End Function


Function InitIndex()

' Called from the OnOpen property of the report.
' Opens the database and the table for the report.

Dim qd As DAO.QueryDef
Dim db As DAO.Database

Set db = CurrentDb()

' Delete all previous entries in Table of Contents table.
Set qd = db.CreateQueryDef("", "Delete * From [tblPrintedPageIndex]")
qd.Execute
qd.Close

' Open the table.

Set rstIndex = OpenForSeek("tblPrintedPageIndex")
rstIndex.index = "RecordID"
End Function


Function UpdateIndex(RecordID As Integer, PageNumber As Integer)
' PageNumber as Integer instead of Rpt as Report
' Pagenumber is a text box with control source: =[Page]+([FirstPageNo]-1)
' Call from the OnPrint property of the section containing
' the Table Of Contents Description field. Updates the Table Of
' Contents table.

rstIndex.Seek "=", ObituaryID

If rstIndex.NoMatch Then
rstIndex.AddNew
rstIndex!RecordID = ObituaryID
rstIndex!PrintPageNo = PageNumber
rstIndex.Update
End If

End Function


Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

x = UpdateIndex([ObituaryID], [PageNumber])

End Sub


Private Sub Report_Open(Cancel As Integer)

x = InitIndex()

End Sub