none
2-sided / Duplex Printing in Access RRS feed

  • Question

  • Hi everyone,

    I have read Two sided Label printing and Setting default printer preferences from MS Access post before, however, I still have some question.

    Here is my task, I have requested to setup access report to print with duplex printing, and I wrote some VBA code in report_open event, this code did well when user print with default printer, however, when user select another printer in print dialog, the duplex setting set. I did tried to set all printers in Application.Printers collection to duplex setting, but still no luck with it.

    Does Access have any event can I insert some VBA code when user change printer, or before it print to printer? (I have tested OnFormat, OnPrint event, but seems these event is too late for changed duplex setting)

    System Env:
    MS Access 2003 (with Access 2000 file format)
    Windows XP
    Canon / HP Printer with Duplex support (Canon and HP printer driver)

    Code for testing:

    Public Function SetPrinterDuplex(rptReport As Report)
    
        Dim prtLoop As Printer
        Dim defaultP As Printer
        
        Set defaultP = Application.Printer
        
        For Each prtLoop In Application.Printers
            With prtLoop
            
                Application.Printer = prtLoop
            
                If rptReport.Printer.Orientation = acPRORLandscape Then
                    prtLoop.Duplex = acPRDPVertical
                    Application.Printer.Duplex = acPRDPVertical
                Else
                    prtLoop.Duplex = acPRDPHorizontal
                    Application.Printer.Duplex = acPRDPHorizontal
                End If
            End With
        Next
        
        Application.Printer = defaultP
        
    End Function

    Wednesday, February 22, 2012 3:00 AM

Answers

  • Hi Cerberus,

    Unfortunately, we don't have an event that will fire after the user selects a printer.  The best thing I can think of is to have the user select the printer from a dropdown and then open/print the report.  The following msdn article has some sample code that demonstrates how to retrieve a list of installed printers.

    http://msdn.microsoft.com/en-us/library/ff835733.aspx

    Also, Allen Browne has a utility that will allow the user to set the printer for a report.  I don't believe it discusses duplex printing specifically, but it may help.  Here's the link:

    Printer Selection Utility
    http://allenbrowne.com/AppPrintMgt.html

    I hope this helps!


    Sharon M, Microsoft Online Community Support

    • Marked as answer by Cerberus Wang Thursday, March 8, 2012 2:04 AM
    Tuesday, March 6, 2012 9:12 PM
    Answerer

All replies

  • Hi Cerberus,

    I think the idea of setting every printer as Duplex is good. However, looks like the logic of above code could not set every printer Duplex. You have the if restriction depends on prtReport.

    To set Duplex for every printer, you can:

    1. Iterate Application.Printers collection, above code is good.
    2. Set Duplex for a specific Printer
    Sub SetPrinter(strFormname As String) 
     
     DoCmd.OpenForm FormName:=strFormname, view:=acDesign, _ 
     datamode:=acFormEdit, windowmode:=acHidden 
     
     With Forms(form1).Printer 
     
     .TopMargin = 1440 
     .BottomMargin = 1440 
     .LeftMargin = 1440 
     .RightMargin = 1440 
     
     .ColumnSpacing = 360 
     .RowSpacing = 360 
     
     .ColorMode = acPRCMColor 
     .DataOnly = False 
     .DefaultSize = False 
     .ItemSizeHeight = 2880 
     .ItemSizeWidth = 2880 
     .ItemLayout = acPRVerticalColumnLayout 
     .ItemsAcross = 6 
     
     .Copies = 1 
     .Orientation = acPRORLandscape 
     .Duplex = acPRDPVertical 
     .PaperBin = acPRBNAuto 
     .PaperSize = acPRPSLetter 
     .PrintQuality = acPRPQMedium 
     
     End With 
     
     DoCmd.Close objecttype:=acForm, objectname:=strFormname, _ 
     Save:=acSaveYes 
     
     
    End Sub 
    
    Refer to:

    <http://office.microsoft.com/client/helppreview14.aspx?AssetId=HV080203054&lcid=1033&NS=MSACCESS%2EDEV&Version=14&tl=2&queryid=&respos=1&HelpID=vbaac10%2Echm12861>

    Let me know if there's anything unclear please. I'll try to assist.


    Forrest Guo | MSDN Community Support | Feedback to us

    Friday, February 24, 2012 9:55 AM
    Moderator
  • Hi, have you been able to figure out the problem? the above code is for your reference.

    Regards,


    Forrest Guo | MSDN Community Support | Feedback to us

    Monday, February 27, 2012 3:13 AM
    Moderator
  • Hi Forrest,

    Sorry for late reply, and thanks for your help too

    I did try similar code for report before (in On Activate event) :

    Public Function SetPrinterDuplex(rptReport As Report)
        Dim prtLoop As Printer
        Dim defaultP As Printer
        
        Set defaultP = Application.Printer
        
        For Each prtLoop In Application.Printers
            With prtLoop
            
                Application.Printer = prtLoop
            
                If rptReport.Printer.Orientation = acPRORLandscape Then
                    rptReport.Printer.Duplex = acPRDPVertical
                    prtLoop.Duplex = acPRDPVertical
                    Application.Printer.Duplex = acPRDPVertical
                Else
                    rptReport.Printer.Duplex = acPRDPHorizontal
                    prtLoop.Duplex = acPRDPHorizontal
                    Application.Printer.Duplex = acPRDPHorizontal
                End If
            End With
        Next
        
        Application.Printer = defaultP
        
        DoCmd.Save acReport, rptReport.Name
    End Function

    However, these code works perfect if user use default printer,
    but when user choose other printer (e.g printer A) to print this report, duplex setting will be reset to duplex setting in printer A


    Monday, February 27, 2012 8:16 AM
  • Hi,

    Forms and reports stores printer information as the object definition. So when we change the default printer settings, forms and reports' related printer settings is not affected. This behavior is documented in this KB: http://support.microsoft.com/kb/290293

    Based on this KB, we see that there're two workarounds, one of them is:

    Reattach printer to report after you changed printer settings. Code snippet like

    Sub ChangePrinterSettingsForReport()
       Dim rpt As Access.Report
       Dim prtr As Access.Printer
       
       Set Application.Printer = Nothing
       Set prtr = Application.Printer
       
       'Set the default printer's orientation to landscape
       prtr.Orientation = acPRORLandscape
    
       'Set the default printer's paper size to legal
       prtr.PaperSize = acPRPSLegal
    
       'Print Preview the Alphabetical List of Products Report
       DoCmd.OpenReport "Alphabetical List of Products", acPreview
       Set rpt = Reports("Alphabetical List of Products")
    
       'Set the Printer property of the report to the
       'Application.Printer object
       Set rpt.Printer = prtr
    
       'Uncomment the following line if you wish to save the object
       'with the current settings
       'DoCmd.Save acReport, rpt.Name
    
    End Sub

    Let me know if there're anything unclear, I'll try to assist.


    Forrest Guo | MSDN Community Support | Feedback to us


    Tuesday, February 28, 2012 1:55 PM
    Moderator
  • Hi Forrest,

    thanks for reply, but I'm sorry, maybe my presentation skill is not too good, here is my testing setup and steps:

    Setup:

    • Printer A (Duplex Setting: Single)
    • Printer B (Duplex Setting: Duplex) (Default Printer)

    Step:

    1. Open Access and run code with above code (both mine and your code)
    2. Open report in print preview mode
    3. Select File -> Print
    4. In Print Dialog, default is Printer B, but I change it to Printer A
    5. OK and print to Printer A

    Here, we got single print report,
    but if we don't change to Printer A, the result is fine

    However... my task is make sure the report is duplex printing in any printer

    ------------------------------------------------- Separator ------------------------------------------

    I have put some code in Detail.OnPrint event, and use following code

    Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
        MsgBox "Printer: " & Application.Printer.DeviceName & ";" & Application.Printer.Duplex & ", Report: " & Me.Printer.DeviceName & ";" & Me.Printer.Duplex
    End Sub

    In print preview, I got "Printer: PrinterB;2, Report: PrinterB;2"

    But in print (step like above), I got "Printer: PrinterB;2, Report: PrinterA;1"


    Wednesday, February 29, 2012 1:56 AM
  • Hi Cerberus Wang<abbr class="affil"></abbr>,

    You need to loop through the Application.Printers collection and change Printer.Duplex property before sending report to print.

    Nadia

    Wednesday, February 29, 2012 2:32 AM
  • Hi Cerberus Wang<abbr class="affil"></abbr>,

    You need to loop through the Application.Printers collection and change Printer.Duplex property before sending report to print.

    Nadia

    Hi Nadia,

    I did try to loop through the application.printers collection and change printer.duplex property, but no luck, I put code in Report.OnActivate event, before print, printerA.duplex and report.printer.duplex is 2, however, when print to printer, they change back to 1

    I want to change duplex property before sending to printer, but I can't find any event to place my vba code, detail.onprint or format event is too late for printer setting, any recommendation?

    Wednesday, February 29, 2012 2:57 AM
  • Hi,

    The Report.Page event is fired before printing, could this help in this case?

    http://office.microsoft.com/client/helppreview14.aspx?AssetId=HV080203338&lcid=1033&NS=MSACCESS%2EDEV&Version=14&tl=2&queryid=&respos=1&HelpID=vbaac10%2Echm13882

    Best Regards,


    Forrest Guo | MSDN Community Support | Feedback to us

    Wednesday, February 29, 2012 5:55 AM
    Moderator
  • Hi Forrest,

    I did tried

    • report - open, activate, deactivate, page
    • detail: format, print
    • report header: print

    unfortunately, seems these event is fire after access sent header information to printer, which too late for duplex setting

    Wednesday, February 29, 2012 6:53 AM
  • Cerberus, I'll try to get experienced people help on this question. Please wait some time.

    Regards,


    Forrest Guo | MSDN Community Support | Feedback to us

    Wednesday, February 29, 2012 7:19 AM
    Moderator
  • Hello,

    Put this function in the module and call before opening report:

    Public Function SetPrinterDuplex()
        Dim prtLoop As Printer
     
        For Each prtLoop In Application.Printers
                If prtLoop.Orientation = acPRORLandscape Then
                    prtLoop.Duplex = acPRDPVertical
                Else
                    prtLoop.Duplex = acPRDPHorizontal
                End If
        Next
     End Function

    Remove your SetPrinterDuplex function from report code alltogether.

    Nadia

    Wednesday, February 29, 2012 3:26 PM
  • Hi Forrest,

    Thanks very very much, I will wait and keep trying

    Hi Nadia,

    I tried to loop all printers object and set it to duplex:

    • open mdb
    • on report print
    • on report activate

    But as I said before, duplex setting changed after I change printer from default to other printer

    Thursday, March 1, 2012 7:07 AM
  • Hi Cerberus,

    Unfortunately, we don't have an event that will fire after the user selects a printer.  The best thing I can think of is to have the user select the printer from a dropdown and then open/print the report.  The following msdn article has some sample code that demonstrates how to retrieve a list of installed printers.

    http://msdn.microsoft.com/en-us/library/ff835733.aspx

    Also, Allen Browne has a utility that will allow the user to set the printer for a report.  I don't believe it discusses duplex printing specifically, but it may help.  Here's the link:

    Printer Selection Utility
    http://allenbrowne.com/AppPrintMgt.html

    I hope this helps!


    Sharon M, Microsoft Online Community Support

    • Marked as answer by Cerberus Wang Thursday, March 8, 2012 2:04 AM
    Tuesday, March 6, 2012 9:12 PM
    Answerer
  • Instead of changing the printer mode, which affects all subsequent jobs, I wrote the following line to print the report in duplex without changing the printer mode permanently (Note: substitute 'myreport' with the actual name of your report):

    ===

    DoCmd.OpenReport "myreport", acViewPreview,,,acHidden

    Reports("myreport).Printer.Duplex = acPRDPHorizontal

    DoCmd.OpenReport "myreport", acViewPreview

    ===

    HTH

    Wednesday, August 15, 2012 9:06 PM