Printing to non-default paper size

[Keithr]

This one seems to be a common problem, so here goes (requires a bit of
advanced coding!)

In the Open event of the report, include code to set the size of paper
required by reading it from the current default printer:

Private Sub Report_Open(Cancel As Integer)
Me.Printer.PaperSize = PrinterPaperSize(DefaultPrinterName)
End Sub

The calls for default paper size (above) use a new module containing the
following code:

Option Compare Database
Option Explicit

Declare Function GetDefaultPrinter Lib "winspool.drv" Alias
"GetDefaultPrinterA" _
(ByVal pszBuffer As String, ByRef size As Long) As Boolean

Public Function DefaultPrinterName() As String
Dim DefPrtName As String, lpSize As Long

lpSize = 255
DefPrtName = String(lpSize, Chr(0))
If GetDefaultPrinter(DefPrtName, lpSize) Then
DefaultPrinterName = Left(DefPrtName, lpSize - 1)
End If
End Function

Public Function PrinterPaperSize(ByVal PrtName As String) As AcPrintPaperSize
Dim Prt As Printer, PSize As AcPrintPaperSize
Set Prt = Application.Printers(PrtName)
If Prt Is Nothing Then Exit Function
PrinterPaperSize = Prt.PaperSize
End Function

which determines the current default printer on the local PC and gets the
paper bin size in use.

To print the report out, use a command button on a form running the
following code:

Private Sub cmdPrint_Click()
DoCmd.Echo False
DoCmd.OpenReport "My Report", acViewPreview
DoCmd.RunCommand acCmdQuickPrint
DoCmd.Close acReport, "My Report"
DoCmd.Echo True
End Sub

The report window does not appear and the report prints directly to the
default printer without hanging up the print queue until someone overrides
the paper size error message.

Enjoy!