Code to print a report to a specific Printer

[JohnLee]

Good day,

I hoping someone will be able to help me here, I need to be able to tell specific reports to print to specific printers within our organisation, without having to go through the motions of individually selecting the report and printer.

This is the code I currently have, which works fine when only one printer is the default

==================
START OF CODE
==================

'If the tblLetterRunDGI table has records in it then
If DCount("*", "tblLetterRunDGI2") > 0 Then
'Print the DGI rptLetterRun Report
DoCmd.OpenReport "rptLetterRunDGI", acNormal, "", ""
End If

'If the tblLetterRunDGS table has records in it then
If DCount("*", "tblLetterRunDGS2") > 0 Then
'Print the DGS rptLetterRun Report
DoCmd.OpenReport "rptLetterRunDGS", acNormal, "", ""
End If

'If the tblLetterRunSky table has records in it then
If DCount("*", "tblLetterRunSky2") > 0 Then
'Print the Sky rptLetterRun Report
DoCmd.OpenReport "rptLetterRunSky", acNormal, "", ""
End If

'Print the rptDailyLetterCount report
DoCmd.OpenReport "rptDailyLetterCount", acNormal, "", ""

==================
END OF CODE
==================

Does anyone know how I can direct the reports to different printers using VBA

Your assistance would be most appreciated.

John

 

[gemma-the-husky]

yuo cant do this easily in Access97

i understand its easier in later versions because theres a printer object, although i havent done it.

you could open the reports in preview, and let users pick their own output device (which also lets them select fax or email, so thats very useful too

 

[JohnLee]

Thanks for your reply Gemma,

But requiring the end user to choose printers is what I want to avoid, I have Access 2000, so will have look for the Printer Object and see what comes up.

John

 

[gemma-the-husky]

ive not got A2000 - im not sure if the printer object is available in that version - search around here- youre sure to find something on it, with sample code

 

[DCrake]

Simple Software Solutions

Hi

By default Access will sed a report to the default printer. However if you go into design mode of the report and nominate a specific printer by name then when the report is printed it will automatically use the nominated printer.

If the reports are being generated from different computers with slave printers ensure that the name of the printer matches the name chosen when setting up the report. When I say name I do not mean the Phyiscal name of the printer. Go into Control Panel > Printers and the name that appears underneath the icon is the name Access looks for. So for example if it is a Duplex Printer and it Original name is something like HP L9000x Duplex, just rename it to Office Duplex on all pcs as some pc's sometimes name them differently. This will prevent errors mentioned earlier.

If you are that way inclined Access also has the ability if you are using a twin bin laser to get it to print the first page from the upper bin (Letter Headed Paper) and the subsequent pages to the lower bin (Continuation sheets).


CodeMaster:

 

[gemma-the-husky]

i think what John was after was for access to be able to (internally) get a list of printers, and dfirect a report as appropriate

eg, a report that prints invoices on different printers depending on the operating department say

i thought later versions of access provided a means to programatically change the printers at runtime

 

[DCrake]

Simple Software Solutions

Gemma

So what you are saying is that different departments can print the same reports but depending on what department they work in denotes the printer it is printed out on

Does this imply that each department has slave printers not network printers or a print server.

Access can retieve a list of printers connected to a pc but the user would still have to select the printer as the destination prior to printing. Why you ask? well what happens if a department buys a new printer and installs it locally you would have to change the application code to change the printers name.

David

 

[JohnLee]

Hi Dcrake/Gemma,

Thanks for both your responses, Dcrake your 2nd post sounds like what I need to do, so whereabouts in the report design would I specify the nominated printer.

John

 

[DCrake]

Simple Software Solutions

http://http://support.microsoft.com/kb/319317/en-us

Will show you how to retrieve the printers attached to a pc.
Put these into a list box (whatever) and get the user to select from list then reassing the printer against the report.

Long time since I have done this but the logic works

David

 

[JohnLee]

Hi,

unfortunately your link did not work, so wasn't able to view the info, however as I know what printers to target and what printers the appropriate PC's can access, I don't need to find out that using code. What I need to know as indicated in your 2nd post, is where in the report design mode do I tell the report which printer it is to print to. I've been looking at the properties menu of my reports and can't see anything there, that clearly indicates this.

Your assistance most gratefully received.

John

 

[gemma-the-husky]

using access cookbook (my bible!)

access 2002 exposes a printers collection

in an info box it says that some examples that took 100s of lines of code in A2000 now take only a handful of lines in A2002.

there are demo codes for A2000 with the book CD, but not printed in the book

 

[DCrake]

Simple Software Solutions

Go to the design of a report and click on File > Page Setup

Then click on the Page tab

There are two radar buttons at the bottom left; click on Use Specific Printer which will enable the printer button.

Click on the Pritner button, this will open a printer dialog box for you to select a printer. This can be local or network.

Hope this points you in the right direction

David

 

[JohnLee]

Hi David,

Thanks very much for that, something new I didn't know about and so simple too.

John

 

[gemma-the-husky]

i see - i thuoght you wanted to pick a different printer automatically at run time, not just set a different printer as standard

 

[JohnLee]

Hi,

Yes, that's right and this method solves it, no additional code to write. When my code triggers the report to print, the report will automatically print to the printer set in the page setup. I've tested it and it works a treat.

John

 

[DCrake]

Simple Software Solutions

Printers Object Members
Here are some of the names of the members of the Printer Object:

Application – This property is used to access the application object and related properties.
Item - Returns a specific member of the collection by position or index.
Count – Returns the number of printers in the collection.
Parent - Returns the parent of the Printers collection, which is the Application object.

To get a list of all the printers on a particular system, you need to return the printers collection. Because all information about printers is contained in the printers collection, you need to use the printers property of the application object to return the printers collection. Once you have the printers collection, you simply loop through it and list all the printers that is contained therein. The code snippet below list all the currently installed printer names in a combo box:

Dim prt As Access.Printer
Dim i As Integer
Dim cboPrinter As Access.ComboBox

DoCmd.RunCommand acCmdSizeToFitForm
Set cboPrinter = Me![cboSelectprinter]
cboPrinter.RowSource = ""
i = 0
For Each myprinter In Application.Printers
cboPrinter.AddItem i & ";" & myprinter.DeviceName
If myprinter.DeviceName = Application.Printer.DeviceName Then
intDefaultPrinter = i
End If
i = i + 1
Next
cboPrinter = intDefaultPrinter
'Me![cboPaperSize] = 1

End Sub

You can do almost anything once you have the printer collection returned, except that you cannot delete or add printers to the printers collection through code. Adding or removing printers to the collection is done by the user either by installing a new printer or removing one. Another useful feature of the printer collection is that you can set the paper size and orientation through code. Below is another code snippet that shows how this is done. The code assumes that you have combo boxes that enable you to select printers (as per the code above), select a predetermined report (listed in cboSelectReport):

Dim myprinter As Printer
Dim strReport As String

Set myprinter = Application.Printers(CLng(Me![cboSelectPrinter]))
strReport = Me![cboSelectReport]
myprinter.PaperSize = Me![cboPaperSize]
myprinter.Orientation = Me![fraOrientation]
DoCmd.OpenReport strReport, acViewPreview
Reports(strReport).Printer = myprinter
Exit Sub

 

[JohnLee]

Hi David,

Thanks very much for that, most useful indeed.

John