VBA code needed to Talk to Access about getting the paper size number it assigned to a Dymo label (paper size)

(It was suggest I ask in the VBA section.. this appears to be the closest to VBA for Access)

I'm using Access 2003. I have different reports printing on different printers. One of those printers is a DYMO 400. I do not want to specify a printer for the report as this MDE will be going to many workstations and the printers could be on differnt ports etc. I have found the solution and now need to "automate it"
I use the following to print to the printer currently:

set application.printer = application.printers("DYMO LableWriter 400")
stDocNam = "PrintLabel"
docmd.echo off
docmd.openreport stDocName, acViewPreview (yes i know i can ,, hidden)
with reports("PrintLabel).printer
.papersize = 143
.leftmargin = .233 * 1440
.itemsizeheight = 2.2083 x 1440
end with

OK so here is what I need to automate. ... I only found out that the Paper size for the DYMO Printer 400 is 143 because I had to make another report, choose specific printer...(Dymo 400)... select the label size (Shipping 20256)... then open the report, go to Alt-F11, CTRL-G and typed ?reports("printlabel").printer.papersize
This now gives me what THIS machine has for the Dymo 400 20256 Shipping address label .

I want to have some kind of a script that would find out what number access has asigned to a Dymo Printer Page (but it's really a label...30256 shipping lable). Because this number changes on differnt machines depending on what numbers have already been used by other printers, I want to find it dynamically.

Again my number ...143.... would only be for my system but deploying a MDE to other stations with different configs my method won't work unless I can "talk" to Access and ask it what number represents for the Dymo 400 a shipping lable #30256.

(Prdev won't work in MDE's)


Thank-you in advance.
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.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
One of my clients has an app using a DYMO ... I just emailed her this question ... I will post response asap ..

detroitdrAuthor Commented:
Thank-you very much.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
I take it you are in Detroit ?  If so, does the name Joe Zimmer mean anything to you?

Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

detroitdrAuthor Commented:
Sorry that does not.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Not sure what you mean ... the value of PaperSize is either (a) a value from the AcPrintPaperSize enum or (b) a value that you pass to this method. Here's what online help has to say for it:

Returns or sets an AcPrintPaperSize constant indicating the paper size to use when printing. Read/write.

If a user is using the same printer, even on a different machine, the size would still be 143 ... or do different DYMO LableWriter 400 printers using label #30256 have different paper sizes? If so, then you'll have to provide some facility for your endusers to be able to change that at runtime (much like the Page Setup dialog in most Windows applications).
detroitdrAuthor Commented:
Ya this is a little hard to explain.

Example. Three workstations.

Workstation#1. First installed printer = Dymo. (Dymo has 40 labels (paper sizes))
                        Second installed printer = HP IIISi (this supports 20 paper sizes)

Workstation #2. First installed printer = HP IIISi
                          Second installed printer = Dymo

Workstation #3: Only Dymo installed printer.

From what I can tell .... Access starts at the same number to assign a value to each printers paper sizes but that value changes depending on installed printers and the order they were installed.
With #1 let say out paper is # 39, for #2 it would be 39 + 20 = 59 and on #3 it may be 39.
Again I don't know exactly how access assigns them but I have proven that the number changes per workstation depending on the printers and order of installation. (to double verify that... a value can only got up to 255 and there are many printers and sizes...so it doesn't make sense for Dymo shipping to always be #39....  )

So here i've rewritten perhaps more clearly.

-you have a Report in Access
-you are making a MDE
-you are not selecting a specific printer (for obvious reasons regarding deployment)
-you are using non standard paper (Dymo labels are SMALL)
-you can not automatically query Access to find out the paper size Number assigned
   to an installed printer for that printers paper. (eg Dymo Shipping label paper 30256)
  **If you could say .printer.papersize = "30256 Shipping Labels" that would solve my
  problem, but you can't. You have to find out the value assigned to that label by Access
-you CAN do it yourself manually.. but that defeats the purpose.

You can do it manually by using the MDB, copy the report, select the specific printer in page setup, select the paper size (what label you want...30256 Shipping when using Dymo printer), then open the report, press Alt-F11, Ctrl-G, and type ?reports("your report name").printer.papersize
this will tell you for this computer (with its own installed printers) what number Access assigned to that "paper size" (label) eg #143

That is what I want to do automatically as the order of installed printers and what printers installed will change that value from system to system..... and in my opinion.. if you can't select non-standard paper sizes (2.23" x 4" Dymo shipping labels) then giving us commands to change margins,row spacing,item size height and width,orientation and itemlayout is useless. (Again because it appears you can't do the manual query above to find out the unique number on a workstation for the lable as the pre-defined papersizes don't cover Custom.....

Did that help clear up my question?
detroitdrAuthor Commented:
And just to make the most important statement from above to stand out, I'll re-type it here:

If you could say .printer.papersize = "30256 Shipping Labels" that would solve my
  problem, but I don't believe you can. Instead I believe you can only say
.printer.papersize = 157
(You have to find out the value assigned to that papersize (label) by Access)
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The PaperSize value comes from the AcPrintPaperSize enum (see the Object browser to view all the members) and  that doesn't change at all (this is a hardcoded value in Access). Manufacturers could perhaps replace that value with one that is more to their liking, but AFAIK they would not be allowed to add to that enum.

But you can set the various sizing aspects of a printers paper. I just checked a few of the items, but all were "get/set" properties according to online help. You'd want to set the .PaperSize to acPRPSUser (which is #256) and then set the various properties ... personally I'd try something like this first:

Dim prt As Printer

Set prt = Application.Printers("DYMO blah blah")
With prt
  .PaperSize = acPRPSUser
  .leftmargin = .233 * 1440
  .itemsizeheight = 2.2083 x 1440
End With

Set Application.Printer = prt

<not print the report>

Set Application.Printer = Nothing
detroitdrAuthor Commented:
Thank-you for your response!

from my testing... itemsizeheight doesn't actually change the papersize... that is the issue.

the paper defaults to some address size page... and unless I can send a papersize command... it just doesn't work.

What I need to do is be able to either send the following (which won't work)
.printer.papersize = "30256 Shipping Label"


.printer.papersize = 256   'custom
.printer.paperlength = 4"   'yes I know this ins't a command but that's what I'm trying to do
.printer.paperwidth = 2.23"
the itemsizeheight doesn't seem to affect the size of the paper just the size of the item to print onto the paper.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I see ... you're right, that would only affect the size of the item, but I have to ask: Why would that matter? If the item size fits the paper, then it really wouldn't matter what papersize?

Also: can you not alter your report to show a Detail section that is 4" high? This would effectively limit your papersize to a Height of 4 before a page break ...

I've used the Dymo label printer but it was loooong ago ... so I may not be thinking on the right track.
detroitdrAuthor Commented:
I agree that it shouldn't really matter what papersize as long as the default printer setting is larger then the "label" size... however for what ever reason... it just doesn't work.  

If I force the paper size (from my manual method above) it works fine.... and being that I want to push this out I really don't want to have this being an issue.

A hard one huh???

(and yes I agree a page break will occur if I limit to 2.23" high which is the label size...)

Feeling hopeless....

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Sorry det ... my client has no clue about your issue.  I didn't do that part of her app ... actually, some poeple at SageKey did ... so, I don't think I will be of much help, sorry.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<I agree that it shouldn't really matter what papersize as long as the default printer setting is larger then the "label" size.>

I'd think that it would have to be EXACTLY the right size, not larger or smaller.
Another thing:

<I do not want to specify a printer for the report as this MDE will be going to many workstations and the printers could be on differnt ports etc>

If you specify the printer, Access will handle the ports and such for you (as would any Windows app). If you set the report printer to the "DYMO xxx" printer, then when you deploy it to Workstation zzz (which has a DYMO printer installed), Access will just use that printer. The trick is that the printer must be named exactly shown ...

Have you tried installing this on a workstation, sort of a "testbed" situation? I deploy apps to users with HUGE networks with (literally) hundreds of printers, and Access has a very good track record of finding and using the correct printer.

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
detroitdrAuthor Commented:
Ahhh... I will try that...

Just to clarify your suggesting to actually specifiy the Dymo printer for that report, and just ensure that all workstations have the same name as my report settings for the printer... that way it won't matter what port the printer is on?

(I believe i may have tried this and the port caused it to be an issue.... however I will check again this evening.)
detroitdrAuthor Commented:
Well from what i can tell, this will work. By ensuring the printer name is exactly the same (case sensitive as well).

Although this doesn't actually fix the problem of how to set the page size... it is (for now) an acceptable band-aid until a more programic way is forthtold.

Thank-you for your help.
detroitdrAuthor Commented:
MX: Thx for trying! :>
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.