Solved

VBA code to Print Report to Default Printer

Posted on 2004-10-11
15
3,030 Views
Last Modified: 2008-02-07
Dear All:

I have a funky problem on a network whereby reports, although set to print to "Default Printer", are printing on other printers other than the user's default.  

Therefore I would like to add VBA code to each Report's Open event that tells the report to print to the default printer, even though this is already setup in the "Page Setup".

Does anybody know how to do this?
0
Comment
Question by:birstein
[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
  • 9
  • 2
  • 2
  • +2
15 Comments
 
LVL 6

Expert Comment

by:mcorrente
ID: 12280601
Application.Printer = Nothing
0
 
LVL 6

Expert Comment

by:mcorrente
ID: 12280607
that will reset the printer to the default printer for the local machine.
0
 
LVL 34

Expert Comment

by:flavo
ID: 12280612
I think only Access 2003 allows you easily to change printers.  You can use Excel to do it for you though (works for all versions from 97+)

In your refrences, Add Microsoft Excel Object Library ?.? (8.0 for 97, 9.0 for 2000, 10.0 for xp and 11.0 for 2003)

heres the code to do it

Dim xl As Excel.Application
Set xl = New Excel.Application
xl.ActivePrinter = "myPrinterName"
xl.Quit
Set xl = Nothing


Dave
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 6

Expert Comment

by:mcorrente
ID: 12280614
It seems that there is an aspect of your code that is changing the printer anyway, because it should default to this.
0
 
LVL 6

Expert Comment

by:mcorrente
ID: 12280641
I know that Access 2002 uses the Printers collection.  It may be the first version to do so.
0
 
LVL 34

Expert Comment

by:flavo
ID: 12280683
Maybe it is 2002, not too sure, im stuck in the dark ages at work with 97
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12281007
It is - the Printers collection is not available in 2000 (as far as I know).

Reports can "remember" the printer settings but NameAutocorrect can play havoc with these settings.
0
 
LVL 1

Author Comment

by:birstein
ID: 12283157
Access 2002 does use the Printers collection.

But I need to clarify that all of the reports open in Preview mode.

What I want to do is FORCE the reports to print to the user's default printer WHEN the user prints, even if the Page Setup has been set to "Specific Printer" by someone.

I tried to use the Reports UseDefaultPrinter property:

If Reports![VacationNotificationLetter].UseDefaultPrinter = False Then
          Reports![VacationNotificationLetter].UseDefaultPrinter = True
End If

the report says "Automation Error".
0
 
LVL 6

Expert Comment

by:mcorrente
ID: 12286310
The problem you're having is that you can't change it from the report's internal code.
0
 
LVL 6

Expert Comment

by:mcorrente
ID: 12286321
what you need to do is write an external procedure for opening the report in print preview and use that code to change the printer.
0
 
LVL 6

Expert Comment

by:mcorrente
ID: 12286363
Public Sub PreRptWthDefPrt()

Dim rpt As Report

Application.Printer = Nothing
DoCmd.OpenReport "NameOfReport", acViewPreview
Set rpt = Reports("NameOfReport")
rpt.Printer = Application.Printer

End Sub
0
 
LVL 6

Accepted Solution

by:
mcorrente earned 500 total points
ID: 12286381
Tested and works.  "Application.Printer = Nothing" sets the printer that the database uses back to the default window's printer (in case anyone has changed it since opening the database).  The code then opens the report in preview mode and sets the report printer to the access printer (which is now the default windows printer).
0
 
LVL 1

Author Comment

by:birstein
ID: 12294211
Dear MCorrente:

Your code works! However, I have one problem. I have two landscape reports and when I use your code its sets the printer to the default print in portrait mode, which, of course, is the default printer setting. Can I use your code AND modify it for just these two reports, so the report is set to landscape?

Thanks!

Kathryn
0
 
LVL 6

Expert Comment

by:mcorrente
ID: 12297701
Yep.  Just put the following line after the last line of the code above:

rpt.Printer.Orientation = acPRORLandscape
0
 

Expert Comment

by:osmjahir
ID: 12366410
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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…

739 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