Solved

VBA - Microsoft  Access - Configure Page Settings for Forms

Posted on 2016-09-12
17
42 Views
Last Modified: 2016-09-16
Experts,
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.

Thanks..
0
Comment
Question by:Cook09
  • 7
  • 6
  • 4
17 Comments
 
LVL 84
ID: 41795102
Do you mean Page Settings for Reports? You generally don't print Forms (you print Reports).
0
 

Author Comment

by:Cook09
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.
0
 
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.
0
 

Author Comment

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

Does Access have a back-door way to Record a Macro?
0
 
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.

PrinterObject;
https://msdn.microsoft.com/en-us/library/office/ff837177.aspx?f=255&MSPPError=-2147217396
0
 

Author Comment

by:Cook09
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 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.
0
 
LVL 34

Expert Comment

by:PatHartman
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.
0
 

Author Comment

by:Cook09
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.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 34

Expert Comment

by:PatHartman
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.
0
 
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.
0
 
LVL 34

Expert Comment

by:PatHartman
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.
0
 

Author Comment

by:Cook09
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?
0
 
LVL 34

Expert Comment

by:PatHartman
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.
0
 
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.
0
 

Author Comment

by:Cook09
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...."
     https://msdn.microsoft.com/en-us/library/office/ff845464(v=office.14).aspx
    https://msdn.microsoft.com/en-us/library/office/ff191806(v=office.14).aspx

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

Accepted Solution

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

Forms("Form_ShipLabelDymo").SomeProperty

or

Forms![Form_ShipLabelDymo].SomeProperty

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.
0
 

Author Closing Comment

by:Cook09
ID: 41802189
Thanks....
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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 …

757 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