Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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

Posted on 2007-03-28
Medium Priority
Last Modified: 2013-11-27
(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.
Question by:detroitdr
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
  • 9
  • 4
  • 3
LVL 75
ID: 18813122
One of my clients has an app using a DYMO ... I just emailed her this question ... I will post response asap ..


Author Comment

ID: 18813139
Thank-you very much.
LVL 75
ID: 18813164
I take it you are in Detroit ?  If so, does the name Joe Zimmer mean anything to you?

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.


Author Comment

ID: 18813506
Sorry that does not.
LVL 85
ID: 18814983
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).

Author Comment

ID: 18815096
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 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?

Author Comment

ID: 18815182
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)
LVL 85
ID: 18815223
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

Author Comment

ID: 18815626
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.

LVL 85
ID: 18817211
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.

Author Comment

ID: 18817526
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....

LVL 75
ID: 18817570
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.

LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1500 total points
ID: 18817961
<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.

Author Comment

ID: 18818177
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.)

Author Comment

ID: 18826571
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.

Author Comment

ID: 18826576
MX: Thx for trying! :>

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

670 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