MS Access Print Report using VBA

I have a very VBA intensive report. When I preview it everything is great but when I print it after previewing things go wacky. I have spent many hours narrowing down the possibilities and I have conclude with a certain level of confidence that it is a bug in MS Access. Up to this point my method for printing reports was to open the report using docmd.openreport "report" . I then use the docmd.printout command so that I can set the page range, collation etc. Is there a way to print a report directly and still be able to set options like page rage, collate etc without doing a preview first? Thanks, Jeff

3 Answers 3

There is unfortunately no way to do it entirely neatly in code, but it can still be done since the introduction of the WindowMode parameter of the DoCmd.OpenReport method. This makes it possible to open a report in print preview mode and have it be hidden. You can then set properties of the report's Printer object (such as the output printer and orientation), and then use DoCmd.PrintOut to print a page range.

One thing to note:

You can't do this in the report's OnOpen event, because changing anything that has an effect on the layout will not give you correct results. For instance, if in the OnOpen event, you changed from Portrait to Landscape orientation, you won't have an accurate count of how many pages there are in the report, because the report hasn't been formated at the time the OnOpen event fires. For everything but pages, though, it's OK.

The way I would implement this is with a public function and a dialog form. The function would look something like this:

 Public Function PrintReport(strReport As String) As Boolean ' open report in PREVIEW mode but HIDDEN DoCmd.OpenReport strReport, acViewPreview, , , acHidden ' open the dialog form to let the user choose printing options DoCmd.OpenForm "dlgPrinter", , , , , acDialog, strReport With Forms!dlgPrinter If .Tag <> "Cancel" Then Set Reports(strReport).Printer = Application.Printers((!cmbPrinter)) Reports(strReport).Printer.Orientation = !optLayout Application.Echo False DoCmd.SelectObject acReport, strReport DoCmd.PrintOut acPages, !txtPageFrom, !txtPageTo PrintReport = True End If End With DoCmd.Close acForm, "dlgPrinter" DoCmd.Close acReport, strReport Application.Echo True End Function 

The dialog form would look something like this:

_{(source: dfenton.com)}

As you can see above, I open this dialog with an OpenArg parameter, which is the name of the report. In the dialog's OnLoad event, I initialize the controls on the form:

 Dim varPrinter As Printer Dim strRowsource As String Dim strReport As String If Len(Me.OpenArgs) > 0 Then strReport = Me.OpenArgs Me.Tag = strReport For Each varPrinter In Application.Printers strRowsource = strRowsource & "; " & varPrinter.DeviceName Next varPrinter Me!cmbPrinter.RowSource = Mid(strRowsource, 3) ' first check to see that the report is still open If (1 = SysCmd(acSysCmdGetObjectState, acReport, strReport)) Then With Reports(strReport).Printer Me!cmbPrinter = .DeviceName Me!optLayout = .Orientation End With Me!txtPageTo = Reports(strReport).Pages End If End If 

I use the form's .Tag property for the report name, and then do everything based on that, including making changes to report properties on the fly, which is possible because the report is open in preview mode, but not visible.

For instance, I have this AfterUpdate event behind the Layout option group:

 With Reports(Me.Tag) .Printer.Orientation = Me!optLayout Me!txtPageTo = .Pages End With 

The reason I change the page range numbers is because changing the orientation will most likely change the number of pages. Unlike in the OnOpen event, changes to a the format properties of a report open invisibly in Print Preview mode happen immediately.

I use my standard methods for dialog forms, which is to have the Cancel and Continue buttons set the form's .Visible property to False, which allows the calling code to continue. For the Cancel button, I set the form's .Tag property to "Cancel" and check the .Tag property when the code continues in the calling context (see above).

So, this isn't as great as it would be to be able to set the page range on the Printer object directly, but it gets the job done.

One thing that would need to be changed in production code is making sure there was an error handler in the PrintReport function so that if something went wrong, Application.Echo can be turned back on (otherwise, the user might be stuck with a blank screen and unable to work). The alternative would be to just let the report appear onscreen when the DoCmd.SelectObject method is invoked. But if I'm hiding the report preview from the user, I would want to go all the way.

For more information on this, you should investigate the .Printer object in the Object Browser (F2 in the VBE), and MS Knowledge Base article 290293 is helpful in explaining the interactions between the Application.Printers collection and Application.Printer object and the ones associated with a particular report. I also found a little tutorial on the Office site that clarified a few things.