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

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.

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

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.

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.

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!

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.

Featured Post

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…

In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another.
Base the dependent combo on a query for its row source:
Add a reference to the first combo on the form as criteria i…

Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…