[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Changing report/printer layout and margins via VBA code

Posted on 2005-04-15
Medium Priority
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
  • 3
LVL 19

Accepted Solution

Eric Sherman earned 500 total points
ID: 13793354
One other option is to default your report "rptlabel" to the specific printer and page settings you want it to print to.


Author Comment

ID: 13793817
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

ID: 13794065
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

ID: 13794720
Got it!

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


Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
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 …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

834 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