M
Matt Wickham
We have some issues using Access 2003 or 2007 to print labels using custom
paper sizes. Correct paper size selection is important because any report
that is not the default letter size or other standard size will not print as
expected on a certain printer, depending on the report size and margins
selected in design mode. A simple way of saying this is that if you feed a
non-standard sized form through the center of a laser printer that is set up
for letter sized paper, it will not print correctly. Your paper size and
margin selection must match the report design.
When designing reports in Access 2003, you can select an existing custom
paper size with File -> Page Setup. According to other posts in this forum
for Access 2003, if you need a custom paper size such as labels (which might
be 4" X 9" or whatever custom size you might think of), you had to add these
through the Control Panel -> Printers -> Select Printer -> Printer Preference
-> Paper Options -> Custom...You would define your custom paper size and give
it a name which you would then select as your paper size in the Page Setup of
your report design. This paper size would then be used when the report was
printed, and everything works fine, at least for that one system where you
set up the paper size.
If you design and setup a report using a custom paper size on one system,
and you move the application to another system, you have to set up the paper
size on the target system as well. This is alot of trouble and problematic
for an application that needs to be distributed to more than one system. If
a user does not have access to the Control Panel, it becomes very difficult.
The only way around this would seem to be custom VBA code that manipulates
the Printers object to add a custom paper size. This code would have to be
somewhere in the Event Procedure for a custom button to run the report, or
perhaps in the activation of the main application or form.
In Access 2007, you have a Page Setup tab. There is a Size Icon on the
"smart" ribbon which has microhelp (tooltip) that says "To apply a specific
paper size to all sections in the document, click More Paper Sizes.". This
would be great, and would work like other Office applications like Word,
which easily allow users to define a custom paper size without using the
Control Panel. However, contrary to its own text, there is apparenly no such
selection of More Paper Size when you use the Size Icon or the Page Setup
Icon. This appears to be a bug in the Access 2007 tooltip text. So we have
the same situation in Access 2007 as in Access 2003, we have to use custom
code or the Control Panel to create custom paper sizes. So Microsoft appears
to have missed a great opportunity to fix a major deficiency in Access 2007
compared to other office apps. And even if Microsoft fixed this, it probably
would not work for Access runtime applications.
One kludgy workaround is to select a "standard" paper size close to what you
want, and then use the margin setup to effectively eliminate unused space on
the report. For example, in my case, I want to print 1-up labels 4" X 9" fed
through the center of a laser printer. Perhaps I could select a standard #10
envelope 4.12" by 9.5" and set left/right margins of .06" each and top/bot
margins of 1". But alas, my HP laser print driver thinks it needs more
margin than .06 inches, and though I could probably fool around and get
something to work, wouldnt it be easier and more maintainable to set some
custom setting? We know from experience printing labels on Word that any
laser printer will support odd label sizes as long as they meet the minimum
width and length supported by the printer.
So if we search MSDN, we find a decent article on programming printer
settings which should work for Access 2002 and above
http://msdn.microsoft.com/en-us/library/aa139946(office.10).aspx. We note
that we should be able to set AcPrintPaperSize to one of about 40 predefined
paper sizes, and one user-defined paper size "acPRPSUser". It appears that
we can set the ItemSizeHeight and ItemSizeWidth to a long integer value in
twips (1/1440 of an inch?). The sample code available for download with that
article did not specifically consider custom paper size however, and assumes
a very limited set of hard-coded standard sizes.
So the question for the forum is, does any smart person out there have any
solution or custom VBA code to set up a custom paper size so that it could be
used for a report without having a user have to access the Control Panel?
Your assistance would be greatly appreciated, and apparently earn you
considerable fame, since there appears to be no posted solution here or
elsewhere for this issue.
Any suggestions or experience you can relate would be helpful. Thanks in
advance.
paper sizes. Correct paper size selection is important because any report
that is not the default letter size or other standard size will not print as
expected on a certain printer, depending on the report size and margins
selected in design mode. A simple way of saying this is that if you feed a
non-standard sized form through the center of a laser printer that is set up
for letter sized paper, it will not print correctly. Your paper size and
margin selection must match the report design.
When designing reports in Access 2003, you can select an existing custom
paper size with File -> Page Setup. According to other posts in this forum
for Access 2003, if you need a custom paper size such as labels (which might
be 4" X 9" or whatever custom size you might think of), you had to add these
through the Control Panel -> Printers -> Select Printer -> Printer Preference
-> Paper Options -> Custom...You would define your custom paper size and give
it a name which you would then select as your paper size in the Page Setup of
your report design. This paper size would then be used when the report was
printed, and everything works fine, at least for that one system where you
set up the paper size.
If you design and setup a report using a custom paper size on one system,
and you move the application to another system, you have to set up the paper
size on the target system as well. This is alot of trouble and problematic
for an application that needs to be distributed to more than one system. If
a user does not have access to the Control Panel, it becomes very difficult.
The only way around this would seem to be custom VBA code that manipulates
the Printers object to add a custom paper size. This code would have to be
somewhere in the Event Procedure for a custom button to run the report, or
perhaps in the activation of the main application or form.
In Access 2007, you have a Page Setup tab. There is a Size Icon on the
"smart" ribbon which has microhelp (tooltip) that says "To apply a specific
paper size to all sections in the document, click More Paper Sizes.". This
would be great, and would work like other Office applications like Word,
which easily allow users to define a custom paper size without using the
Control Panel. However, contrary to its own text, there is apparenly no such
selection of More Paper Size when you use the Size Icon or the Page Setup
Icon. This appears to be a bug in the Access 2007 tooltip text. So we have
the same situation in Access 2007 as in Access 2003, we have to use custom
code or the Control Panel to create custom paper sizes. So Microsoft appears
to have missed a great opportunity to fix a major deficiency in Access 2007
compared to other office apps. And even if Microsoft fixed this, it probably
would not work for Access runtime applications.
One kludgy workaround is to select a "standard" paper size close to what you
want, and then use the margin setup to effectively eliminate unused space on
the report. For example, in my case, I want to print 1-up labels 4" X 9" fed
through the center of a laser printer. Perhaps I could select a standard #10
envelope 4.12" by 9.5" and set left/right margins of .06" each and top/bot
margins of 1". But alas, my HP laser print driver thinks it needs more
margin than .06 inches, and though I could probably fool around and get
something to work, wouldnt it be easier and more maintainable to set some
custom setting? We know from experience printing labels on Word that any
laser printer will support odd label sizes as long as they meet the minimum
width and length supported by the printer.
So if we search MSDN, we find a decent article on programming printer
settings which should work for Access 2002 and above
http://msdn.microsoft.com/en-us/library/aa139946(office.10).aspx. We note
that we should be able to set AcPrintPaperSize to one of about 40 predefined
paper sizes, and one user-defined paper size "acPRPSUser". It appears that
we can set the ItemSizeHeight and ItemSizeWidth to a long integer value in
twips (1/1440 of an inch?). The sample code available for download with that
article did not specifically consider custom paper size however, and assumes
a very limited set of hard-coded standard sizes.
So the question for the forum is, does any smart person out there have any
solution or custom VBA code to set up a custom paper size so that it could be
used for a report without having a user have to access the Control Panel?
Your assistance would be greatly appreciated, and apparently earn you
considerable fame, since there appears to be no posted solution here or
elsewhere for this issue.
Any suggestions or experience you can relate would be helpful. Thanks in
advance.