7 out of 8 rated this helpful - Rate this topic

Choosing Output Devices at Runtime in Access 2007

Office 2007

Summary: Learn how to select an output device for a report at runtime in Microsoft Office Access 2007 by using the built-in Printer object. (4 printed pages)

Applies to: 2007 Microsoft Office System, Microsoft Office Access 2007

Ken Getz, MCW Technologies, LLC

July 2009

If you use different devices (printer, fax, and so on) to output reports in your application, you can use the built-in Printer object and a little bit of VBA code to display a list of available printers and a list of available reports, and then print the selected report to the selected output device.

Download the sample database

Access 2007 makes it relatively simple to interact with the available printers and their settings. To experiment with the various ways to control printed output, load the sample database, SelectOutputDevice.accdb, and then open the sample form, frmSetPrintDestination.

Select one of the three supplied reports and then click Print to Current Destination to print the report to its current output device, or select a different device from the combo box at the bottom of the form and then click Print to Chosen Destination to print the report to a different device.

Figure 1. Sample form with options to select report and output device

Sample form to select report and output device

To provide a list of available reports in your application, add the following procedure to a standard module.

Private Sub GetReportList(ctl As Control)
    Dim item As AccessObject
    
    ' Clear the list before adding new items.
    ctl.RowSourceType = "Value List"
    ctl.RowSource = vbNullString
    For Each item In CurrentProject.AllReports
        ctl.AddItem item.Name
    Next item
End Sub...

In the Load event handler of your form, call GetReportList and pass a reference to a ListBox or ComboBox control.

To provide a list of output devices, add the following procedure to the standard module.

Private Sub GetPrinterList(ctl As Control)
    Dim prt As Printer
    For Each prt In Printers
        ctl.AddItem prt.DeviceName
    Next prt
    ctl = Application.Printer.DeviceName
End Sub

In the Load event handler of your form, call GetPrinterList and pass a reference to a ListBox or ComboBox control.

GetPrinterList loops through all the members of the Access Printers collection, and for each printer, adds the DeviceName property to the control.

Finally, the procedure uses the Application.Printer property to retrieve information about the default printer, and then sets the value of the control to that default.

All versions of Access from Access 2002 forward give you two ways to change the output device:

You can change the default printer in Access, then print your report to the new default printer. This is the easier of the two methods, generally works better, and the one that you will use in this example.
You can change the report's selected output device. This requires the extra step of selecting the report on screen, but gives you more flexibility.

If you have multiple output devices installed on your computer, you can use the sample form to choose a different output device from the combo box at the bottom of the form. The sample form will send the selected report to the output device that you choose; for example, you might select a fax driver.

Printing the Report

When you print the report, you must set the new printer, print the report to the new printer, and then reset the original device.

For example, when you click Print to Chosen Destination on the sample form, you execute the following code in the form's module.

Private Sub cmdChosen_Click()
    On Error Resume Next
    
    Dim reportName As String
    reportName = cboObjects.Value
    
    DoCmd.OpenReport reportName, View:=acPreview, WindowMode:=acHidden
    Set Reports(reportName).Printer = _
      Application.Printers(cboDestination.ListIndex)
    DoCmd.OpenReport reportName, View:=acViewNormal
End Sub

The code opens the report hidden and sets the Printer property of the report to be the report that you selected. Then, the code opens the report again, this time in normal view, which causes Access to print the report.

DoCmd.OpenReport reportName, View:=acPreview, WindowMode:=acHidden
Set Reports(reportName).Printer = _
  Application.Printers(cboDestination.ListIndex)
DoCmd.OpenReport reportName, View:=acViewNormal

In contrast, if you click Print to Current Destination, the form sends the report to the currently selected printer by simply calling the DoCmd.OpenReport method.

There is one other way to print a report to an output device other than the default device, but it only works if you have configured a report to print to the Access default printer. If you have, then you can change the default output device in Access to one of the other installed printers by setting the value of the Application.Printer property. The following code demonstrates how to change the default printer, open the report to print it, and then set the default printer back to its original value.

Set Application.Printer = _
  Application.Printers(cboDestination.ListIndex)
DoCmd.OpenReport strRptName, View:=acViewNormal
Set Application.Printer = Nothing

Note:

This technique only works if you configure the report to print to the default device; if you set your reports to a printer other than the default printer, you must select the output device for that report.

Choosing Output Devices at Runtime in Access video

Watch the Video

Video Length: 7:35

File Size: 8.31 MB

File Type: WMV

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD