Selecting printer paper source in Access 2002

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

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

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." 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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

I have the following working using mainly HP printers.

Dim sRptName as string
DoCmd.OpenReport sRptName, acViewDesign, , , acHidden
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

LastToKnow0Author Commented:
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.

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.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

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.

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

LastToKnow0Author 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.