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

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 50 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

837 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