CODEWITHSUNDEEP
ms-accessvba
wizofwor
wizofwor

Reputation: 937

Changing printer in VBA for Access also change page layout

I'm trying to provide a control for printer selection on a form.

Combobox named cbxPrinterList is listing the printer names. CompanyHistory report should be printed from selected printer when user click on Command1 button. How can I enforce it to print in Landscape.

Following code works well with one exception. The report is designed in landscape orientation. But it is printing in portrait. 

Private Sub Command1_Click()

   Dim reportName As String
   reportName = "CompanyHistory"

   Dim vPrinter As Access.Printer

   Set vPrinter = Application.Printers(cbxPrinterList.ListIndex)
   vPrinter.Orientation = acPRORLandscape

   DoCmd.OpenReport reportName, View:=acViewPreview, WindowMode:=acHidden
   Set Reports(reportName).Printer = vPrinter

   DoCmd.OpenReport reportName, View:=acViewNormal
   Set Application.Printer = Nothing

   ' Close report without saving.
   DoCmd.Close ObjectType:=acReport, ObjectName:="Invoice", Save:=acSaveNo

End Sub

Upvotes: 1

Views: 3408

Answers (1)

Jericho Johnson
Jericho Johnson

Reputation: 759

You might be better off taking a slightly different approach to changing the target printer to fix this problem. Instead of opening the report and changing the reports printer, try temporarily changing the default printer in Microsoft Access and then changing it back. I have never had any problems with this approach in the past.

First off, I don't know how your combo box is set up, but I hope that it provides a list of the names of the printers, so that the chosen one will return the windows DeviceName of the printer. Here is some example code to fill the combo box with the list of available printers. You could put this in the Form_Open event.

Dim prt As Access.Printer

'Populate the Printer Combo Box
For Each prt In Application.Printers
    Me.cbxPrinterList.AddItem prt.DeviceName
Next prt
Set prt = Nothing

One more issue you may be having is that the report may be set for a specific printer instead of the default printer. In design view of your report, check the Page Setup to make sure that the "Printer for CompanyHistory" is set to "Default Printer" (instead of "Use specific printer). I have found that when a report is saved for a specific printer, it can cause the layout to have problems when changing to a different printer at runtime.

Microsoft Access, Report Designer, Page Setup dialog

Then, revise your code as follows to change the default printer before printing.

Dim DefaultPrinter As String
Dim ReportPrinter As String
Dim prt As Access.Printer
Dim reportName As String

reportName = "CompanyHistory"
ReportPrinter = cbxPrinterList

'Get the current default printer so we can restore it after printing
DefaultPrinter = Application.Printer.DeviceName
'No need to change the printer if they chose the current default
If ReportPrinter <> DefaultPrinter Then
    For Each prt In Application.Printers
        If prt.DeviceName = ReportPrinter Then
            Set Application.Printer = prt
            Exit For
        End If
    Next prt
End If
DoCmd.OpenReport reportName, acViewNormal
If ReportPrinter <> DefaultPrinter Then
    Set Application.Printer = Application.Printers(DefaultPrinter)
End If

Upvotes: 1

Related Questions