table of contents off by one page

[crobaseball]

I have a very bizarre prob. I have some code to create a table of contents/index for a report which has "chapters" which can be added or dropped depending on whether we choose to stock something or not. And I'm getting the strangest issue. The very first "chapter" starts fine on Page 1. But the next item we sell (Chap 2), then starts on Page 3, instead of Page 2. Here's what's strange. Every other item is then one page off. Any ideas? Below is my code.

Option Compare Database

Dim db As Database
Dim TocTable As Recordset
Dim intPageCounter As Integer
                    

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()
    
    'Resets the page number back to 1
    intPageCounter = 1
    'Delete all previous entries in Table of Contents table.
    Set qd = db.CreateQueryDef("", "Delete * From [Index for Pricebook]")
    
    qd.Execute
    qd.Close
    
    'Open the table.
    Set TocTable = db.OpenRecordset("Index for Pricebook", dbOpenTable)
    
    TocTable.Index = "Series"
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!Series = TocEntry
        TocTable![Page Number] = intPageCounter
        TocTable.Update
    End If
End Function
Function UpdatePageNumber()
   intPageCounter = intPageCounter + 1
End Function

The InitToc fx is called upon the opening of the pricebook, the UpdateToc fx is called upon printing. There's something in here where I think I'm calling the functions incorrectly, but I'm not sure quite what the error is.

[June7]

Don't need db.CreateQueryDefs just to delete records from table - can be simply:

CurrentDb.Execute "DELETE FROM [Index for Pricebook]"

I've never used Seek method, only FindFirst. What does it accomplish here?

What is TocEntry?

[crobaseball]

It's some older code which I pulled from Microsoft. It was defining things with DAO, small things like that. So the Seek method might just be older. I'm a newbie and am happy to use boilerplate from other sources.

TocEntry is the "Chapter" name. When I call the function it looks like: =UpdateToc([Series],[Report])

So basically, the "Seek" is searching for duplicates to the "Chapter" title, and as long as there isn't a match, then it updates the Table of Contents. Does that make more sense?

[June7]

Sorry, can't see anything wrong with code. Can you step debug?

[crobaseball]

Yeah, I'm not sure what the heck is wrong either. I need to experiment with how I call the function and see if that makes any difference

[crobaseball]

Ok, so when I take the report to Print Preview, the very first "chapter", and only the first "chapter" appears in the "Index for Pricebook" table. Is there something about calling the function On Print where the first series would be created in the index, but then the rest of it then called upon the actual printing, causing everything to be off one page?

[June7]

Possibly. You are doing something I've never encountered before. What happens if output direct to printer?

If you want to provide db for analysis follow instructions at bottom of my post.

[crobaseball]

Ok, so it's actually 2 db's. The first is where the Data lives, the 2nd, "Sales - Print - Test TOC" is where the reports live, and where the code is for this particular problem. The report I'm working with to test the TOC is called "Sales_Pricebook_DealerA". Let me know what you think. Thanks!!

[crobaseball]

Whoops, I was playing around with options, and you'll notice that the code has the 1st page set to ZERO!!! Ideally, I want this to start at 1, for the 1st page to then be 1. Feel free to change that in the code!!!

Also, the explanation was a touch short, but I was in a coff shop that was closing. A bit more explanation:

As mentioned, I have 2 db's, one where the data lives, the 2nd linked to the tables where the reports are. You access (pun intended) the report through the form "Print Pricebook Options" and click on the button "Print Preview Dealer A Pricebook". Essentially, this 2nd database will have all tables, reports, and queries hidden and will only show this form for our sales force to print from. Since the pricebook can get large, it's important that I take them to print preview so they see it first before killing our printer by printing the wrong thing.

When you get to the Print Preview view of the report, then open the "Index for Pricebook" table, you'll see that there is only the one record. And then if you print the report (I print it as a pdf), when you then open the table, the rest of it is populated out. Clearly, there's something in the On Print portion of the event which isn't working properly between Print Preview and the actual Printing.

Hope this explanation helps.