Shows some more options that are available to you when using the Printer Object.
Access Archon #131
The AssignPrinter procedure given in Part 1 of this article will do fine if you need to ensure that a specific report will always be printed to a specific printer (especially if you want to print the report to a non-default printer). If you want maximum choice of printers when printing a report, you can use a dialog form like fdlgSelectPrinter in the sample Printer Object database.
In the original version of this dialog (which was included in a sample database for my book, Access 2002 Inside-Out ), the RowSource property of the cboSelectReport combo box was filled with printer names from the Form_Load event procedure. This code filled the cboSelectPrinter combo box’s list with all the printers you would get from the ListPrinters procedure (see Figure A) – most likely, more than half of the items won’t be printers appropriate for printing Access reports.
Figure A. The original printer selection dialog
In this case, I think that less is more, so I would recommend first running the ListPrinters code to get the list of printers (and their numbers) from the Immediate window, and then hard-coding the RowSource property of cboSelectPrinter with just the printers that are appropriate for printing Access reports. This will have to be done separately for each computer, since the available printers will differ. On one of my computers, the following string limits the drop-down list to just the two physical printers:
3;HP LaserJet 3200 Series PCL;5;HP Deskjet 9670
The syntax for a two-column list (the first column is bound, with width set to 0 so it won’t display) is the printer number, a semicolon, the printer name (no quotes), a semicolon, and so on for each printer you want to see in the list.
Helen Feddema has written some great ACCESS books
Search for her name at http://shop.office-watch.com/list.php?a=helen or check out her latest book at https://office-watch.com/l.asp?0764559044
For listing reports, iteration of the reports in the database works fine, at least if you want all the reports to be available for selection. The following code on the Form_Load event of the modified fdlgSelectPrinter form creates a string containing all the reports in the database, and writes the string to the RowSource property of cboSelectReport. (The original code that writes the printer names is included, commented out, in case you want to use it):
Private Sub Form_Load()
On Error GoTo ErrorHandler
Dim i As Integer
Dim cboReport As Access.ComboBox
Dim cboPrinter As Access.ComboBox
Dim obj As Access.AccessObject
Dim prj As Object
DoCmd.RunCommand acCmdSizeToFitForm
Set cboReport = Me![cboSelectReport]
cboReport.RowSource = “”
i = 0
Set prj = Application.CurrentProject
For Each obj In prj.AllReports
cboReport.AddItem i & “;” & obj.Name
i = i + 1
Next obj
Me![cboPaperSize].Value = 1
‘The following code fills the cboSelectPrinter combo box with all printer names
‘Remove the apostrophes to run the code
‘Set cboPrinter = Me![cboSelectPrinter]
‘cboPrinter.RowSource = “”
‘i = 0
‘For Each prt In Application.Printers
‘cboPrinter.AddItem i & “;” & prt.DeviceName
‘If prt.DeviceName = Application.Printer.DeviceName Then
‘intDefaultPrinter = i
‘End If
‘i = i + 1
‘Next prt
‘cboPrinter.value = intDefaultPrinter
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description
Resume ErrorHandlerExit
End Sub
Yet another technique is used to fill the drop-down list of the third combo box on the form, cboPaperSize. To select a paper size, you need either the appropriate named constant from the AcPrintPaperSize enum, or its numeric equivalent. For convenience, I entered these values into a table, tlkpPaperSize, along with user-friendly names for the sizes. This table is used as the row source of the combo box.
You could use a table to store report names and/or printer names; the disadvantage is that the table is static, while getting report names (or printer names) from the Registry, or the AllReports collection, is dynamic, so it will reflect any changes you have made. If you put report names in a table, and you add or delete a report, you will need to manually edit the table to reflect the change. A reports table, tlkpReports, is included in the sample database, if you want to try this method.
The new form has two option groups, for selecting orientation (portrait or landscape) and print mode (preview or print). The form is shown in Figure B.
Figure B. The new Select Printer for Report form
The code on the cmdOpenReport command button is listed below:
Private Sub cmdOpenReport_Click()
On Error GoTo ErrorHandler
Dim strReport As String
Dim intReportMode As Integer
intReportMode = Nz(Me![fraReportMode].Value, 1)
‘Get printer number from column 1 for use in referencing the printer
‘(column numbering is zero-based in code)
Set prt = Application.Printers(CLng(Me![cboSelectPrinter].Column(0)))
‘Get report name from column 2
strReport = Me![cboSelectReport].Column(1)
‘Get paper size from combo box value
prt.PaperSize = Me![cboPaperSize].Value
‘Get orientation from option group value
prt.Orientation = Me![fraOrientation].Value
‘Get report mode from option group value
intReportMode = Nz(Me![fraReportMode].Value, 1)
If intReportMode = 1 Then
DoCmd.OpenReport strReport, acViewPreview
Reports(strReport).Printer = prt
ElseIf intReportMode = 2 Then
DoCmd.OpenReport strReport, acViewDesign, , , acHidden
Reports(strReport).Printer = prt
DoCmd.OpenReport strReport, acViewNormal
End If
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description
Resume ErrorHandlerExit
End Sub
Note that although you can change the report orientation, this doesn’t actually change the report layout, so if a report was set up for portrait, and you print it landscape, it will have a very wide right margin (see Figure C), while if the report is set up for landscape, and you print it portrait, text on the right side will be cut off.
Figure C. A portrait-formatted report previewed in landscape orientation
References
The code in the sample database needs the following reference (in addition to the default references):
Microsoft Scripting Runtime
If you import code or objects into a database of your own, you may need to set this reference. References are set in the References dialog, opened from the VBA window. For more information on working with references, see Access Archon #107, Working with References.
Supporting Files
The zip file containing this article, in Word format, plus the supporting file(s), may be downloaded from the Access Archon page of my Web site. It is accarch131.zip, which is the last entry in the table of Access Archon columns for Access Watch.
|
Document Name |
Document Type |
Place in |
|
Printer Object.mdb |
Access 2002-2003 database |
Wherever you want |
