Microsoft Access: Applications and Utilities

Provided by Allen Browne. Created September 2004.  Last updated: April 2010.

Printer Selection Utility

You can design a report to use one of your printers by choosing Page Setup from the Page Setup ribbon (Access 2010), the Report Tools ribbon (Access 2007), or the File menu (previous versions.) But that approach is useless if others use your report: you do not know what printers they will have installed.

This utility lets the end user assign one of their printers to each report. Whenever they open the report, it is sent to that printer. The utility works with MDE files and runtime versions also.

The utility also illustrates how to manipulate the Printer object and the Printers collection introduced in Access 2002.

Click to download the utility (30KB, Access 2002/3 mdb format, zipped). You can also view the code from this utility.

Overview

The utility has a function named OpenTheReport() to use instead of DoCmd.OpenReport. The function checks to see if the user has assigned a particular printer for the report, and assigns the Printer before the report opens.

To assign a printer, all the user has to do is preview the report, and click the custom Set Printer toolbar button. The utility remembers the choice, and uses that printer for that report in future.

Limitations

The utility works only with Access 2002 and later. Albert Kallal has one for earlier versions: Access 97 or Access 2000.

The utility does not let the user choose paper sizes. That can be done by opening the report in design view, and manipulating PrtMip. (Does not work for MDE.)

To use the correct printer, you must use the supplied function, OpenTheReport(). Docmd.OpenReport works if the report is previewed, but not if it is opened straight to print. Opening reports with the New keyword (for multiple instances) is not supported.

If you open several reports directly to print at once, the timing of the assignment of the Printer object may not operate correctly. To avoid this, program a delay between printing the reports, and include DoEvents.

Copying the utility into your database

To import the components of this utility into your database:

1. Unzip the file.
2. Open your database.
3. In Access 2007 and later, click the Access icon in the Import group of the External Data ribbon.
   In previous versions, choose File | Get External | Import.
   Choose PrinterMgt.mdb as the file to import from.
4. In the Import Objects dialog, click the Options button, and check the box Menus and Toolbars.
5. On the Forms tab of the dialog, click frmSetPrinter.
6. On the Modules tab, click ajbPrinter.
7. Click Ok.

The other form (frmSwitchboardExample) and the reports are included purely for demonstration purposes.

To prepare your report to use this utility, open it in design view, and set these properties of the Report:

If you will use this with most of your reports, you may wish to set up a default report.

In Access 2002, you must also include a reference to the Microsoft DAO 3.6 Library, by choosing References on the Tools menu from a code window. More information on references.

Syntax of OpenTheReport()

OpenTheReport() is the only function you need to learn. It is similar to the OpenReport method built into Access, but has several enhancements.

Firstly, it looks to see if you have specified a printer to use for the report, and assigns it before opening the report. (If you use the old OpenReport to print directly (no preview), it will not use the desired printer.)

Secondly, this function defaults to preview instead of printing directly.

Thirdly, it avoids the problem where the report is not filtered correctly if it is already open.

Fourthly, it does away with the FilterName argument that is rarely used, confusing, and inconsistent. Instead, it provides a simple way to pass a description of the filter to display on the report. Without an explanation of the filter, the printed report is meaningless, and printing the filter itself on the report may be too cryptic. You can therefore enter a description string. It is passed to the report through its OpenArgs. To display the description on the report, just add a text box with Control Source of: =[Report].[OpenArgs]

Fifthly, it avoids the need to trap Error 2501 in every procedure where you open a report. OpenTheReport() traps and discards this annoying error message that can come from the report's NoData event, an impatient user, or some other problem. If you need to know whether the report opened, check the return value of the function. It will be True on success, or False if the report did not open.

Examples:

Argument reference:

OpenTheReport() takes these arguments:

• strDoc - Name of the report to open.
• lngView - Optional. Use acViewPreview to open in preview (default), or acViewNormal to go straight to print.
• strWhere - Optional. A Where Condition to filter the report.
• strDescrip - Optional. A description you want to show on your report. (Passed via OpenArgs.)
• lngWindowMode - Optional. Use acWindowNormal for normal window (default), or acDialog for dialog mode.

Note that strDoc, strWhere, and strDescrip are strings - not variants.

The boring technical stuff

When you assign a printer for a report, the utility creates a new property named Printer2Use. The property is type dbText, and stores the name of the user's printer. The property is created on the report document:
    CurrentDb().Containers("Reports").Documents("MyReport")
since that property can be written and read without opening the report itself. The property is deleted if the user returns the report to the default printer. One advantage of using the custom property over a lookup table is that the assigned printer remains with the report even if the report is renamed, or duplicated.

It turns out that you cannot merely set the Printer object in the Open event of the report. That works if the report is previewed, but not if it is sent straight to print. This is the reason the report must be opened through the function that sets the printer before the report is opened.

The Printer setting is application-wide. If you have several reports open in preview at once, and switch between them, the utility needs to assign the correct printer to each one. The Activate and Deactivate events of the report achieve that.

To restore the Printer object, to the Windows default, use:
   Set Application.Printer = Nothing.
That destroys the object. Access then reconstructs it - from the default Windows printer.

View the code if you wish.