Defining a report format for each report for all printers

Posted on 2001-07-15
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

Comment Utility
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 50 total points
Comment Utility
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

Comment Utility
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

Comment Utility
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now