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 85
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 85
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.
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.


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 85
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 38

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 38

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 85
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 38

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 38

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 85
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 85

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

635 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