Go Premium for a chance to win a PS4. Enter to Win


Defining a report format for each report for all printers

Posted on 2001-07-15
Medium Priority
Last Modified: 2008-02-01
Hi!  I'd like to format each of my reports automatically so the user won't have to change the paper orientation and size every time ('cause sometimes someone changes the paper format and orientation and the next user who wishes to print it must go change it back...

Any idea?


Question by:Et
LVL 54

Expert Comment

ID: 6283640
That's not "straight forward"  possible in access.
You can set the page-settings using "File/Page Settings", but these will be lost when changing a printer...

Check this link for how to solve this from VBA:
(BTW there's a lot of access info/samples out there!)


Accepted Solution

bclark100898 earned 200 total points
ID: 6283651
One of the releases of Access 97 did not retain the printer settings.  I wrote the following code to ensure the correct settings are used.  Something similar should work for you.  More information is available at


look for   PrtDevMode

Also search Access help for PrtDevMode

Option Compare Database
Option Explicit

Type str_DEVMODE
    RGB As String * 94
End Type

Type type_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

Public Function PageSetup(ReportName As String, Orientation, PaperSize)
    'kluge - everytime we switch from on printer to an other (or to another pc using a different printer)
    'access sets the pages to letter, portrait, letter
    ' called by the functions in module Menu Functions
    Dim DevString As str_DEVMODE
    Dim DM As type_DEVMODE
    Dim strDevModeExtra As String
    Dim rpt As Report
    Dim PaperTray As String
    ' Opens report in Design view.
    DoCmd.OpenReport ReportName, acDesign
    Reports(ReportName).Visible = False
    Set rpt = Reports(ReportName)
    If Not IsNull(rpt.PrtDevMode) Then
        strDevModeExtra = rpt.PrtDevMode
        DevString.RGB = strDevModeExtra
        LSet DM = DevString
        DM.lngFields = DM.lngFields Or _
            &H1 Or _
            &H2 Or _

         'DM.intOrientation    ' Initialize fields.
        Select Case Orientation
            Case "Portrait"
                DM.intOrientation = 1
            Case "Landscape"
                DM.intOrientation = 2
        End Select
        Select Case PaperSize
            Case "Letter"
                DM.intPaperSize = 1
                PaperTray = "Automatic"
            Case "Legal"
                DM.intPaperSize = 5
                PaperTray = "Manual"
        End Select
        Select Case PaperTray
            Case "Automatic"
                DM.intDefaultSource = 7
            Case "Manual"
                DM.intDefaultSource = 4
        End Select
        LSet DevString = DM            ' Update property.
        Mid(strDevModeExtra, 1, 94) = DevString.RGB
        rpt.PrtDevMode = strDevModeExtra
    End If

    DoCmd.Close acReport, ReportName, acSave
End Function

'to use::::
Public Function OpenReport(ReportName As String, Orientation, PaperSize, Optional WhereClause)
    'kluge - everytime we switch from on printer to an other (or to another pc using a different printer)
    'access sets the pages to letter, portrait, letter
    PageSetup _
        ReportName:=ReportName, _
        Orientation:=Orientation, _
    'if the report is cancelled because of no data, an error results in the OpenReport method
    On Error GoTo ReportError
    With DoCmd
        .OpenReport ReportName, acPreview, , WhereClause
        .RunCommand acCmdFitToWindow
    End With

    Exit Function

    'Error 2501 means that the OpenReport action was cancelled.
    'Ignore it.
    If Err.Number <> 2501 Then

        MsgBox "Run Time Error '" & Err.Number & "':" & Chr(13) & Chr(13) & _
               Err.Description & Chr(13), , _
               "Microsoft Visual Basic"
    End If

    Resume FunctionExit

End Function


Expert Comment

ID: 6283674
When using the print devmode, it will be unavailable if you compile your program into an mde.
But as pointed out above, the print devemode is the only solution for altering printer settings in access.

Author Comment

ID: 6283850
Great!  It worked very well but I have 3 sligths changes to propose, for whoever needs to use those functions.  First of all, because the PageSetup function opens the report in design mode, it looks better if you put the following lines before and after to hide that process :

doCmd.Echo False
DoCmd.Echo True

Second, still in the PageSetup function, I removed the  lines regarding the PaperTray to be sure it is set to DeafultTray (I didn't find the appropriate constant in Access help).  I needed to do tyhis because we have more than 20 printers (laser and inkjet) and some have legal paper in low tray, other in upper tray, etc...  I configured the tray manually in every report instead.

Finally, I added a parameter (integer) to the OpenReport function so the user can choose to send to the printer immediately or to print preview it.  So, instead of only these lines that open the report :

   'if the report is cancelled because of no data, an error results in the OpenReport method
   On Error GoTo ReportError
   With DoCmd
       .OpenReport ReportName, acPreview, , WhereClause
       .RunCommand acCmdFitToWindow
   End With

I now have this :

'if the report is cancelled because of no data, an error results in the OpenReport method
On Error GoTo ReportError
If ViewMode = acPreview Then
    With DoCmd
        .OpenReport ReportName, acPreview, , WhereClause
        .RunCommand acCmdFitToWindow
    End With
    DoCmd.OpenReport ReportName, acNormal, , WhereClause
End If

So I thank you bclark and the only thing that is very bad is the non availability using a mde, as said by SE...  

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question