Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to print to a specific printer using VBA code in MS Access

Posted on 2006-06-21
10
Medium Priority
?
458 Views
Last Modified: 2012-06-27
I have a report that prints Bills of Lading and must be printed on a specific Bill Of Lading Form. We have several dedicated printers only for these forms.  I look up the user name in a table and get the name of the printer each user should use.  I need to be able to direct the report to this printer.  I DO NOT WANT TO CHANGE THE DEFAULT PRINTER, only for this one form in this one application.
0
Comment
Question by:MGothelf
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 16952850
I think in Access you can specify a printer at report level
(When in design view of the report go to page setup, under the page tab you will see an option to specify a printer for the report,  Access will remember the printer and always print that report on that printer).
0
 
LVL 26

Accepted Solution

by:
dannywareham earned 2000 total points
ID: 16952884
0
 

Author Comment

by:MGothelf
ID: 16954374
This is not a DESIGN question.  Every time the report is run, the program must look up the user to get the right printer.  There are over a dozen different users running the application, and 5 different printers.  The printer is different each time, depending on which user is running the program.
0
Industry Leaders: 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!

 
LVL 26

Expert Comment

by:dannywareham
ID: 16957851
When you open the print dialog, there's a list of available printers in a dropdown list at the top.
Is this what you mean?
0
 

Author Comment

by:MGothelf
ID: 16958925
Thats not what I mean.  

I don't want the user to have to select the printer at all.  I have a table of users and each user is in a department and each department has a specific printer assigned.  I want my application to change the printer.  

For example, users 'SMITH' and 'JONES' are in department 'ACCOUNTING', and ACCOUNTING is assigned printer PRT1, but user 'KELLY' is in department 'HR', and HR is assigned printer PRT2.  So when SMITH runs the report it prints on PRT1 and when KELLY runs the report it prints on PRT2, without the users having to do anything.  

I am looking for a way to change the printer assignment in VBA code within the application.

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 17052180
MGothelf,

What you are asking can be accomplished.

Based on you Question History here, my feeling is that you just need to be pointed in the right direction.

In Access 2002 and beyond, the printer object is more "Exposed" in VBA.
For example, the code:
   
    MsgBox Application.Printers.DeviceName

…Will display the name of the current Printer

In other words, with VBA Code, you can see a list of all installed printers, change printers, Manipulate report settings like Margins. (And yes, even: “Choose an Output Device at Runtime) In Access 2000 an earlier you really cant.

Check out the book "Access Cookbook" by Ken Getz ... et al, put out by O'Reilly (ISBN: 0-596-00084-7)

It has an excellent chapter on Access Printer control; the book also has a CD with the examples. You can modify the code and drop it right into your app!

Good luck!

Hope this helps
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17053569
ok, I just answered a question recently on setting printers as I just found a way to do it

Now if u say u need to lookup printers based on a user

what if u created  a table with user's windows logon id in a table
and alongside that, u have their default printer

simple way to get user id (without API)



    Dim tmpPrinter as Printer
    Dim sUser as String
    Dim sPrinterName as String

    sUser = Environ("Username")
    sPrinterName = DLOOKUP("PrinterName","tblUserPrinters","UserName='" & sUser & "'")
   
    Set tmpPrinter = Application.Printer
    Application.Printer = Application.Printers(sPrinterName)

'Do your stuff, then reset back to original printer
    Application.Printer = tmpPrinter


Now I know u said u dont want to change default printer, but as access prints to default printer, a temporary change is what u might have to do



0
 

Author Comment

by:MGothelf
ID: 17053694
Thanks for your help.  The problem was not in the VBA code to get the default, it was how to set it and problem was that many of my users have Access 2000, so many of the simple functions in Access 2002 and 2003 are not available,so I had to do it brute force.  The link provided by dannywareham actually led me pretty close to the solution.
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 17056561
I'm glad you got it sorted, MGothelf

Good luck with the project
:-)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 17056657
MGothelf,

BTW:

The Access 2000 Devlopers Handbook (desktop) has a great chapter on printer control in Access 2000

Jeff
0

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

618 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