使用 VBA 的 MS Access 打印报告

MS Access Print Report using VBA

提问by Icode4food

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.

我有一个非常 VBA 密集的报告。当我预览它时，一切都很棒，但是当我在预览后打印出来时，事情就变得古怪了。我花了很多时间来缩小可能性范围，并以一定程度的信心得出结论，这是 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.printoutcommand so that I can set the page range, collation etc.

到目前为止，我打印报告的方法是使用docmd.openreport "report". 然后我使用该docmd.printout命令，以便我可以设置页面范围、排序规则等。

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

谢谢，杰夫

采纳答案by dde

long ago, i had a very difficult case. i had to do some field creations, and moving and formatting and this could only be done one way. i took a bold approach and it turned to be the only way: i opened the report hidden and in design mode, had vba do it's stuff, and when done, the report was changed to normal and visible for display and printing.

很久以前，我有一个非常困难的案例。我不得不做一些现场创作，移动和格式化，这只能以一种方式完成。我采取了一个大胆的方法，它变成了唯一的方法：我打开隐藏的报告并在设计模式下，让 vba 做它的东西，完成后，报告被更改为正常可见以进行显示和打印。

回答by David-W-Fenton

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.

遗憾的是没有办法在代码中完全整洁地完成它，但自从引入 DoCmd.OpenReport 方法的 WindowMode 参数后，它仍然可以完成。这使得可以在打印预览模式下打开报表并将其隐藏。然后可以设置报表的打印机对象的属性（例如输出打印机和方向），然后使用 DoCmd.PrintOut 打印页面范围。

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.

您不能在报告的 OnOpen 事件中执行此操作，因为更改对布局有影响的任何内容都不会为您提供正确的结果。例如，如果在 OnOpen 事件中，您从纵向更改为横向，您将无法准确计算报表中有多少页，因为在 OnOpen 事件触发时报表尚未格式化. 但是，对于除页面之外的所有内容，都可以。

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)}

_{（来源：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:

正如您在上面看到的，我使用 OpenArg 参数打开此对话框，该参数是报告的名称。在对话框的 OnLoad 事件中，我初始化窗体上的控件：

  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.

我使用表单的 .Tag 属性作为报表名称，然后根据该属性执行所有操作，包括动态更改报表属性，这是可能的，因为报表在预览模式下打开，但不可见。

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

例如，我在布局选项组后面有这个 AfterUpdate 事件：

  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.

我更改页面范围编号的原因是因为更改方向很可能会更改页数。与 OnOpen 事件不同，对在打印预览模式下以不可见方式打开的报表格式属性的更改会立即发生。

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).

我使用对话框表单的标准方法，即让“取消”和“继续”按钮将表单的 .Visible 属性设置为 False，从而允许调用代码继续。对于取消按钮，我将表单的 .Tag 属性设置为“取消”，并在代码在调用上下文中继续时检查 .Tag 属性（见上文）。

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.

因此，这不如直接在 Printer 对象上设置页面范围那么好，但它可以完成工作。

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.

在生产代码中需要更改的一件事是确保 PrintReport 函数中有一个错误处理程序，这样如果出现问题，Application.Echo 可以重新打开（否则，用户可能会卡在空白屏幕上并且无法工作）。另一种方法是在调用 DoCmd.SelectObject 方法时让报告显示在屏幕上。但是如果我对用户隐藏报告预览，我会想一直走下去。

For more information on this, you should investigate the .Printer object in the Object Browser (F2 in the VBE), and MS Knowledge Base article 290293is 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 sitethat clarified a few things.

有关这方面的更多信息，您应该调查对象浏览器中的 .Printer 对象（VBE 中的 F2），MS 知识库文章 290293有助于解释 Application.Printers 集合和 Application.Printer 对象之间的交互。与特定报告相关联。我还在Office 网站上找到了一个小教程，其中澄清了一些事情。

回答by Thomas

One solution is to set the printer options in the design of the report, save those changes and the print it. The downside is that this will tie the report to a specific printer unless you go into the design and change it.

一种解决方案是在报告设计中设置打印机选项，保存这些更改并打印出来。缺点是这会将报告绑定到特定的打印机，除非您进入设计并对其进行更改。

DoCmd.OpenReport "ReportName", acViewDesign, Null, Null, acHidden

Dim oRpt As Report
Set oRpt = Reports(0)
oRpt.UseDefaultPrinter = False
oRpt.Printer = Application.Printers("printer name")

With oRpt.Printer
    .PaperBin = acPRBNAuto
    .PaperSize = acPRPSLetter
    .Copies = 1
    .PrintQuality = acPRPQMedium
End With

DoCmd.Close acReport, "ReportName", acSaveYes
DoCmd.OpenReport "ReportName", acViewNormal

Set oRpt = Nothing