Selecting printer paper source in Access 2002

Posted on 2006-04-05
Medium Priority
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


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.
Question by:LastToKnow0
  • 4
  • 2

Expert Comment

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


Expert Comment

ID: 16390784
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


Author Comment

ID: 16429260
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.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Accepted Solution

Willibob earned 750 total points
ID: 16429703
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.


Expert Comment

ID: 16443846

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.


Expert Comment

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


Author Comment

ID: 16472158
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.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

864 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