We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Selecting printer paper source in Access 2002

LastToKnow0
LastToKnow0 asked
on
Medium Priority
856 Views
Last Modified: 2008-01-09
Here is my situation:  I have a multi-user access application.  These users have several different network printers available to them.  I want to store a printer name and paper tray in a settings table, and have all users print using those settings on certain reports.

My problem comes when I try to set the paper source.  How can I set the printer paper source from within code?

Here is what I have already done to try to do this:

I have tried using the Printer.PaperBin property (on both application.Printer and <Report>.Printer), but using different constants (such as acPRBNAuto or acPRBNMiddle) had no effect on the actual paper source.

Research on the web suggested that the built in constants for tray selection may not cover the actual options provided by the printer, and that I should enumerate the available trays using the DeviceCapabilities API.  I added some code to do this, as in

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k2/html/ODC_acc10_Printers.asp

however, there is one tray whose number is 15, and the rest (the ones I want) are numbered 257+, and the PaperBin property only accepts values under 256.

I then added a routine to change the paper source using the SetPrinter API, using the code from http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_21622398.html

I have the same problem as the linked question; the final call to SetPrinter is returning 0.  Of course, my code already implements the accepted solution.  

I added a error routine to look at Err.LastDLLError, and the final Setprinter call is returning error code 5, which is "Access is denied."  http://support.microsoft.com/?kbid=230743 has some info concerning printer permission, and I assume that this is the same sort of issue.  Changing use permissions or creating a local print driver as suggested in that article is not an option however.  There must be a way to change the paper source without changing permissions, as Access successfully chooses trays from its printing dialog box.
Comment
Watch Question

Commented:
Can you not set the printer details within the form or report you are trying to print?

Mike

Commented:
I have the following working using mainly HP printers.

Dim sRptName as string
sRptName="YourReport"
DoCmd.OpenReport sRptName, acViewDesign, , , acHidden
Reports(sRptName).Printer.PaperBin=iBinNumber
DoCmd.Save acReport, sRptName

I use the following bin numbers and they work for most models of HP printer.

Bin number 15 = Auto select
Bin number 1=Upper (or manual feed)
Bin number 3=Middle
Bin number 2=Lower

Apologies if you are already using the above code.

Hope this helps

Bill

Author

Commented:
mikem:
The printer needs to be able to be changed on site so that all reports print to a specific printer.  I'd like the person choosing the printer to be able to modify the settings table and have this effect.  Unless I'm not understanding what you're saying, choosing specific printer settings at design time won't accomplish that.

Willibob:
Your code appears to open the report in design mode.  Will that work if the code is compiled into an MDE file?

Also, I have tried setting the PaperBin property using all of the acPRBN* constants, which include ones that resolve to integers 1 through 15.  These have had no apparent effect on the actual bin used.
Commented:
The crucial part is that you have to open the report, make the change and then save the report for it to stick. Design view is not available in an MDE so it will not work in one of those.

Can I suggest some light reading? The Access Desktop Developer's Handbook and the Access Enterprise Developer's Handbook. Cracking reference books. Mine unfortunately are at home but I will consult them this evening to see if there's anything that can help.

Bill

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Commented:
LastToKnow0

You can't set this at the Report level in a n MDE but you can set the Application.Printer and Application.Printer.PaperBin prior to printing the report.

You'll need to store the original settings so you can restore them after you've printed.

I've tested this with an MDE, previewing the report and it does work (I've only tested the Application.Printer bit not the Application.Printer.PaperBin bit but I would expect that to work also as long as a valid PaperBin is used).

Let me know if you need some sample code.

Hope this helps.

Bill

Commented:
BTW: as far as I am aware, the Application.Printer interface is only available in Access2002 onwards.

Bill

Author

Commented:
I am now using a solution similar to Willibob's suggestion:  I am opening the report in design mode during development, setting the printer tray, and saving.  However, I am now using the prtDevMode property rather than the paperbin property in order to access trays > 256.  This is still not optimal, because I have to recompile the mde for the client if I want to change the tray number, but it is working for now.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.