---

MS Access 2003: Set up a report to print on legal size paper using VBA code

This MSAccess tutorial explains how to set up a report to print on legal size paper using VBA code in Access 2003 (with step-by-step instructions).

Question: In Microsoft Access 2003/XP/2000/97, how do I programmatically set up a report so that it will print on legal size paper (ie: 8.5 x 14)?

Answer: You can set up a report to print on legal size paper using the subroutine below:

Open your Access database, click on the Modules tab and create a new Module. Paste in the following code:

Type gtypStr_DEVMODE
   RGB As String * 94
End Type

Type gType_DEVMODE
   strDeviceName As String * 16
   intSpecVersion As Integer
   intDriverVersion As Integer
   intSize As Integer
   intDriverExtra As Integer
   lngFields As Long
   intOrientation As Integer
   intPaperSize As Integer
   intPaperLength As Integer
   intPaperWidth As Integer
   intScale As Integer
   intCopies As Integer
   intDefaultSource As Integer
   intPrintQuality As Integer
   intColor As Integer

   intDuplex As Integer
   intResolution As Integer
   intTTOption As Integer
   intCollate As Integer
   strFormName As String * 16
   lngPad As Long
   lngBits As Long
   lngPW As Long
   lngPH As Long
   lngDFI As Long
   lngDFr As Long
End Type

Sub SetToLegal(pReport As String)

   Dim LDevString As gtypStr_DEVMODE
   Dim LDM As gType_DEVMODE
   Dim LDevModeExtra As String
   Dim LRpt As Report

   On Error GoTo Err_Execute

   'Open report in Design view
   DoCmd.OpenReport pReport, acDesign
   Set LRpt = Reports(pReport)

   'Change paper size to legal
   If Not IsNull(LRpt.PrtDevMode) Then

      LDevModeExtra = LRpt.PrtDevMode
      LDevString.RGB = LDevModeExtra
      LSet LDM = LDevString

      '5=legal, 1=standard
      LDM.intPaperSize = 5
      LSet LDevString = LDM
      Mid(LDevModeExtra, 1, 94) = LDevString.RGB
      LRpt.PrtDevMode = LDevModeExtra

   End If

   'Save report changes (suppress system messages temporarily)
   DoCmd.SetWarnings False
   DoCmd.Save acReport, pReport
   DoCmd.Close acReport, pReport
   DoCmd.SetWarnings True

   Exit Sub

Err_Execute:
   MsgBox "Changing paper size to legal failed."
End Sub

You can then call this subroutine as follows:

Private Sub Command0_Click()

   SetToLegal "Report1"

End Sub

This example changes the paper to legal-size for the report called Report1.

Note: For this subroutine to work, you must have the permissions to modify the report object.