VBA - Microsoft Access - Configure Page Settings for Forms

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.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Do you mean Page Settings for Reports? You generally don't print Forms (you print Reports).
Cook09Author Commented:
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.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Cook09Author Commented:
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 parameters...no opening of the form is necessary.

Does Access have a back-door way to Record a Macro?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.

Cook09Author Commented:
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 installed....so 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.
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.
Cook09Author Commented:
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.
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.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
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.
Cook09Author Commented:
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?
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.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
Cook09Author Commented:
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?"
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cook09Author Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.