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,882 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
ID: 18813122
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
ID: 18813139
Thank-you very much.
0
 
LVL 75
ID: 18813164
I take it you are in Detroit ?  If so, does the name Joe Zimmer mean anything to you?

mx
0
 

Author Comment

by:detroitdr
ID: 18813506
Sorry that does not.
0
 
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).
0
 

Author Comment

by:detroitdr
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 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
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)
0
 
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
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

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

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

Author Comment

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

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

mx
0
 
LVL 84

Accepted Solution

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

Author Comment

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

Author Comment

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

Author Comment

by:detroitdr
ID: 18826576
MX: Thx for trying! :>
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Password on a button in Access 2013 7 34
Error in query expression 3 35
Problem to With line 4 39
VBA code won't run Delete Query 5 20
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

911 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

20 Experts available now in Live!

Get 1:1 Help Now