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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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 …

685 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