Changing report/printer layout and margins via VBA code

Posted on 2005-04-15
Last Modified: 2008-01-09
I am currently working on an app (Using Access 2003) where I have a need to specify a printer for Reports and a printer for Labels. The problem comes when I print a report then a label the labels margins and layout are different i.e. report layout is in Portrait and the labels need to be printed in Landscape. I have tried using the use specific printer and keeping the label printer set as the default in windows, but I am still having problems especially when using the runtime files.

Below is the code I am currently using;

        'Create recordset for Printing.
        Dim rstLocalInfo As DAO.Recordset
        Set rstLocalInfo = CurrentDb.OpenRecordset("select [ReportPrinterName],[LabelPrinterName] FROM tblSiteInfo")

        ' Get the Printer object for the selected printer.
        strTempPrinter = rstLocalInfo!LabelPrinterName
        ' get current default printer.
        strDefaultPrinter = Application.Printer.DeviceName

        ' switch to printer of your choice:
        Set Application.Printer = Application.Printers(strTempPrinter)

        '<print report>
        DoCmd.OpenReport "rptLabel", acViewNormal

        'Swtich back to former default.
        Set Application.Printer = Application.Printers(strDefaultPrinter)

        Set rstLocalInfo = Nothing

I have found a couple of things out there but not sure how to tweak them to my needs.

Here is one of them;

Application.ActiveSheet.PageSetup.Orientation = xlLandscape

Any help is much appreciated
Question by:jonesy_33
    LVL 19

    Accepted Solution

    One other option is to default your report "rptlabel" to the specific printer and page settings you want it to print to.


    Author Comment

    I have tried that and I am still having trouble.

    What I really need is a way to specifically assign the layout and margins for each item I want to print no matter which printer I want to print on and I would like to do it using code. This way I can specify what printer (like I am currently doing) and then for reports set them to portrait with a Top margin of 0.2 inches and a Bottom Margin of 0.2 inches.  and when I want to print a label I can set it to print in Landscape with a Top margin of .04 and a bottom margin of .054...

    Author Comment

    The error only seems to take place in the runtime environment and is;

    "The expression On Click you entered as the event property setting produced the following error: Invalid procedure call or argumnet"

    I hopes this help you help me :-)

    Author Comment

    Got it!

    The printer name in my table was in uppercase and the printer name in Windows was in Lower case, Dooh!


    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    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 …
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    755 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

    19 Experts available now in Live!

    Get 1:1 Help Now