Print report - rendering error

Question

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.

VBA:

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:

Macro:

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 Detail_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?

Answer 1

Hi,

I will give this a try, thanks for your help.

Answer 2

There is no need to call a Print method.  When you call the OpenReport method without a View argument it sends the report to the current default printer.  The OpenArgs method in my example is used to pass various arguments which have been stored as a tokenised string in a hidden text box in the form to the report, which are then used in its Open event procedure to set the font, alignment etc.  Unless you want to control the format of your report in this way you don't need to bother about this.

Selecting a printer is done entirely independently of the report as all that's necessary is to set the default printer which access will use; it's not specific to a particular report.  This is done by opening the frmPrinterDlg form from another button on the main form before printing.  In your case if you did want to be able to select a printer you could open the dialogue form from the button on your report in report view and then include a button on the dialogue form to print the report by means of the OpenReport method.  You should put the following line in the report's Close event procedure to reset the printer to the Windows default:

    Set Application.Printer = Nothing

Answer 3

Thanks, I like the example. I have studied the code a bit to try to understand it. Where in the piece of code is the command that actually sends the report to the printer? When I click PRINT LABELS the only code I see is:

Private Sub cmdPrint_Click()

DoCmd.OpenReport "rptlabels", OpenArgs:=Nz(Me.txtArgs, "")

End Sub

I see that rptlabels has an OPEN event but I cannot see the print command. Could you help me understand?

Answer 4

Report view uses different event procedures to print preview or normal view.  Rather than opening the print dialogue to print the current object you could call the OpenReport method to print the report, filtering it if necessary by means of the WhereCondition argument, or by referencing parameters in its underlying query.  If you need to select a printer to which to direct it you can do so by changing the current printer used by Access.  You'll find an example of a means of doing this in the file LabelPrinter.zip in my public databases folder at:

https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

This little demo file includes a dialogue form which lists the installed printers, from which one can be selected.