Solved

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
16
2,851 Views
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)

help?

Thank-you in advance.
0
Comment
Question by:detroitdr
  • 9
  • 4
  • 3
16 Comments
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
One of my clients has an app using a DYMO ... I just emailed her this question ... I will post response asap ..

mx
0
 

Author Comment

by:detroitdr
Comment Utility
Thank-you very much.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
I take it you are in Detroit ?  If so, does the name Joe Zimmer mean anything to you?

mx
0
 

Author Comment

by:detroitdr
Comment Utility
Sorry that does not.
0
 
LVL 84
Comment Utility
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).
0
 

Author Comment

by:detroitdr
Comment Utility
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?
0
 

Author Comment

by:detroitdr
Comment Utility
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)
0
 
LVL 84
Comment Utility
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
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:detroitdr
Comment Utility
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"

or

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

0
 
LVL 84
Comment Utility
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.
0
 

Author Comment

by:detroitdr
Comment Utility
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....

:<
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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.

mx
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
Comment Utility
<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.
0
 

Author Comment

by:detroitdr
Comment Utility
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.)
0
 

Author Comment

by:detroitdr
Comment Utility
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.
0
 

Author Comment

by:detroitdr
Comment Utility
MX: Thx for trying! :>
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 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

14 Experts available now in Live!

Get 1:1 Help Now