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
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?

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.


Author Comment

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

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Suggested Solutions

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

730 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