Printing a report - rendering problem

[triplee23]

Hi,

I have a Access invoice solution and have created the invoice (report). I have added som VBA code to insert page numbering. See my code below. The code uses a table to store the number of pages for each section (record). The values are then fetched from the table into variables on the report.

Option Compare Database

Public DB As Database
Public GrpPages As Recordset


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
               
    'Find the group.
    GrpPages.Seek "=", Me![kp_InvoiceID]

    If Not GrpPages.NoMatch Then
        'The group is already there.
        If GrpPages![PageNumber] < Me.Page Then
            GrpPages.Edit
            GrpPages![PageNumber] = Me.Page
            GrpPages.Update
        End If
    Else
        'This is the first page of the group. Therefore, add it.
        GrpPages.AddNew
        GrpPages![kp_InvoiceID] = Me![kp_InvoiceID]
        GrpPages![PageNumber] = Me.Page
        GrpPages.Update
    End If
    
End Sub

Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
    
    Page = 0
        
End Sub

Private Sub GroupHeader3_Format(Cancel As Integer, FormatCount As Integer)
    
    'Set page number to 1 when a new group starts.
    Page = 1
    
End Sub

Function GetGrpPages()
   
   'Find the group name.
   
   GrpPages.Seek "=", Me![kp_InvoiceID]
   
   If Not GrpPages.NoMatch Then
      GetGrpPages = GrpPages![PageNumber]
   End If
   
End Function


Private Sub Report_Open(Cancel As Integer)
   
   Set DB = DBEngine.Workspaces(0).Databases(0)
   DoCmd.SetWarnings False
   DoCmd.RunSQL "Delete * From [tblInvoiceGroupPages];"
   DoCmd.SetWarnings True
   Set GrpPages = DB.OpenRecordset("tblInvoiceGroupPages", DB_OPEN_TABLE)
   GrpPages.Index = "PrimaryKey"
   
End Sub

I want to print the report (invoice) and have on the REPORT VIEW created a PRINT button. The button uses the simple macro action:

RunMenuCommand - PrintObject

I get the following error after having clicked OK in the print dialog box:

"A custom macro in this report has failed to run, and is preventing the report from rendering."

If I open the report directly in PREVIEW mode, no problems or errors and I am able to print just fine with the ordinary print menu button.

I am quite sure that it is the details_format giving the error message, when I empty it, I am able to print (but the page numbering is of course missing). It is something about how the report (print) is prepared and the order of things. I also think it is the GrpPages that has a missing object to the database table, but adding the Report_open code does not help.

This has been driving me crazy, any ideas?