VBA - Microsoft  Access - Configure Page Settings for Forms

Posted on 2016-09-12
Last Modified: 2016-09-16
In configuring the page settings for a form...or actually a couple of forms...within MS Access, what is the best way to do this with VBA?

The form settings needed are Margins (all), Orientation, Printer Name, and Paper Size (30256 Shipping).  Currently this is being done by selecting the Form (or highlighting the Form Name) and clicking on Page Setup in the QAT.

Question by:Cook09
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 4
LVL 84
ID: 41795102
Do you mean Page Settings for Reports? You generally don't print Forms (you print Reports).

Author Comment

ID: 41795122
Yes, I want to print a Report, but I need to set the Margins, Label Printer type, and type of label for two of the four forms that have already been created.
LVL 84
ID: 41795807
Sorry, but I'm still confused - you mention a Report, but then write that you want to set values for "four forms". Do you mean reports?

I don't mean to be obtuse, but "Report" and "Form" are distinct objects in Access, and are managed differently. It's important to use the correct terms in an online forum, since we have no way of knowing what you see onscreen, and must rely only on what you write.

That said: the only way to modify Page settings and such for a Report is to open that report in Design view. Even if you have a dozen Reports that need to be modified, opening each of those manually and editing the properties would be much quicker than developing code to do it.

If you want to do that at runtime ... just don't. In order to do that you must deploy the .accdb/.mdb file, and everytime you make those changes your file will decompile. This can lead to bloat, performance issues, etc etc.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 41795977
What I have in Access are about 18 "Forms" which are used to print Labels.  Six of these forms are tied to certain printers...Dymo, Zebra, and PDF.  Yes, I agree that setting these Forms with the Page Setup function is the easiest way...but not for 200 field users, which we need to Remote into their computers and preform this setup. There are about three types of configurations among the 200 users.

I was able to find some Access code that would list all of the installed Printers on the system.  But, I'm looking for a way to set the Dymo_Label1_Form and the Dymo_Lalabel2_Form with Dymo as the Specific Printer, Margins needed, Landscape Orientation, and the Paper size set to "30256 Shipping."  The same type of settings are needed for the Zebra_Label1_Form and Zebra_Label2_Form.

From what I understand in your response is that there is no way to do this in VBA.  Although, to perform the Page Setup, all that is needed is to highlight the form, select Page Setup, and set the opening of the form is necessary.

Does Access have a back-door way to Record a Macro?
LVL 84
ID: 41796018
No, you can do this in VBA - my question was why are you doing this in VBA.

A Form does not have the Page Setup option, nor does it have Margins, etc. A Report has this, but you continue to insist you're using a Form.

You can use the Printer Object to perform many of these things during runtime.  You would do something like this;

dim prt As Printer
Set prt = Application.Printers("YourPrinter")
prt.BottomMargin = "0.5"
prt.TopMargin = "0.5"
etc etc

AFTER setting your Printer object, you would then print the report as needed. Assuming your report is set to use the correct printer (or the Default Printer) you should be good.

You can store printer-specific settings in a Table in the database, and you could read those settings as needed, for the specific printer.


Author Comment

ID: 41796200
Well, I would hate to disagree on a matter in which I'm requesting some assistance, but below is a Form, with the specific Page Setup information for it.  At the moment, we have to do this twice for each of these Access applications that are that is the same as 400 times.
Access Forms - Page SetupMaking the settings for a specific printer, doesn't seem to tie a particular form to a specific printer.  It may possibly default to the printer, but the page settings or margins are not carried through so the Labels are printed properly.
LVL 36

Expert Comment

ID: 41796429
Forms are not optimized for printing.  The dialog you are looking at is deceiving and I can't tell what version of Access you are using.   Here is a picture from A2016 and you can see that forms don't have a PageSetup option.DesignRibbon.JPG
If what you want is printed output, you should be using a report.  You can convert the forms to reports and that will probably give you more success.  Open each form in design view and choose the save as option.  You can choose to save them as reports.  Once the forms have been converted to reports, you will have better control over printing them.

Author Comment

ID: 41796469
Access version 2007 - 2013

We having been using this application for several years now and this is what we have.  
If it's not possible to set the Page Setup parameter's for each form...why are we able to do so?

If you take a look at what I posted, the form is not open. This is how we  configure the page settings for each of the forms that need to be setup for use by a printer. First we have to put the Page Setup function on the QAT, as it is not available  within the standard toolbar.

It is currently a "pain," to have to manually set each Form's Page Setup.
LVL 36

Expert Comment

ID: 41796500
When you added the PageSetup to the QAT, where did you get it from?  I only see the option in the Report objects.  That could explain why although you can add it, it doesn't work the way you want it to.

How many forms would need to be converted to reports?  I think that is going to be your path to success.
LVL 84
ID: 41796527
Well, I would hate to disagree on a matter in which I'm requesting some assistance, but below is a Form, with the specific Page Setup information for it.
First we have to put the Page Setup function on the QAT
That's why you see it and we don't. You modified the QAT to show the basic Page Setup feature, which would configure the settings for the default printer.

The VBA I suggested should do this for you, but you cannot set these for each Form. You'd have to use the methods I suggest if you want to try and automate this.
We having been using this application for several years now and this is what we have
We hear that quite a bit, but the answer is always - do it the right way, whether it's been working or not. As both Pat and I have said: Reports are for printing, not forms. While you can sometimes work around these issues, you'll inevitably find something that won't work.
LVL 36

Expert Comment

ID: 41796632
And, it's not like converting a form to a report is difficult.  You just have to open it and save as.  I suggested renaming the original forms so when you "save as" you can use the original name.  Don't forget though that you would have to change any OpenForm methods to OpenReport.

Author Comment

ID: 41798812
If a Form is built like a label, and printed on a physical label from label printer, is this not printing from a Form? Maybe I'm just not understanding this.

If all that is needed is to set the parameters for the installed printer, how then should the code be written. It needs to loop through the installed printers, if it finds one that begins with "Dymo", it will set the appropriate configuration for it, and if it begins with "Zebra", another configuration is applied?
LVL 36

Expert Comment

ID: 41798994
The process you need will work fine as long as the objects you are modifying are REPORTS.  Do not be surprised that it doesn't work for forms.  Forms and reports are completely different objects intended for different uses.  That means they have different properties and different events.  Your house's alarm system can make a phone call to report a problem to your monitoring service.  Does that mean you should be able to use it as a phone or a camera?  Learn to use Access as it was designed for optimal results.  When you try to bend Access to your will, you will become old before your time.

Remember, when you put that setup button on the QAT, you DID NOT get it from the form ribbons.  You got it from the report ribbons.  That should be a clue.
LVL 84
ID: 41799389
If a Form is built like a label, and printed on a physical label from label printer, is this not printing from a Form? Maybe I'm just not understanding this.
Not in the Access world (at least not in my Access world). You're "printing a Form", not "printing from a Form".

As I mentioned earlier, you can use the Printer object to cycle through all the settings for the default printer, or for any printer in the system that Access recognizes. To cycle through printers:

Dim prt As Printer

For each prt in Application.Printers
  If prt.Name= "SomeName" then
    <modify settings here>
  End If
Next prt

Note this would make those changes for the session of Access, and those changes would not "stick". You'd have to run this each time the application opens. If you want the changes to stick, you'd have to convert your forms to reports, then open each report in Design view and make the changes, and then save them.

But you can't do that with a Form, since a Form doesn't have any method for doing that.

Author Comment

ID: 41799788
I appreciate the "printing a form," and "printing from a form" perspective. Yes, we are printing a form.  As far as "making it stick,"  how we currently do this is set individual Page Setup parameters for each form that we will print.

Would you please comment on the Microsoft pages:
"How to: Work with Form and Report Printer Settings"...and "How to set...."

One example they use is for a Report...but what would be the syntax for a specific Form, "Form_ShipLabelDymo?"
LVL 84

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 41800658
You would refer to a Form like this:




But that's not going to help you modify the Printer settings. To do that, you must use the Printer object I referred to earlier, and the link that you refer to lets you know exactly that. You open the object, then open and set a Printer object, then "print" the object, then close the Printer object.

Author Closing Comment

ID: 41802189

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
vba search, delete, and enter 0 for entire sheet row A 13 29
Access 2010 7 41
VBA Delete sql "Too Few Parameters" 8 23
Hash on Excel 13 40
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

756 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