I have several users in several areas that print the same report, but need to
print to 4 different destinations, and these destinations are not their
default printers. I wrote code that works, but it is not as efficient as I
would like it to be for a couple of reasons:

1) I have to open the report in print preview first, then select the
printer. I would rather assign the printer to the report and have it stay
that way while the user is using the database.
2) When assigning the printer to the report that is open in print preview, I
have to use the Set rpt.Printer code 3 times because it is case sensitive.

The code below uses a control called PickPrintingOptions that pulls the
user's preferred printer from a list numbered 1 to 4, then assigns the
printer.

Private Sub PrintPickButton_Click()
Dim rpt As Report
DoCmd.OpenReport "Pick", View:=acViewPreview, windowmode:=acHidden
Set rpt = Reports("Pick")
Select Case PickPrintingOptions
Case 1
On Error Resume Next
Set rpt.Printer = Application.Printers("\\Clark66\Warehouse
LaserJet")
Set rpt.Printer = Application.Printers("\\clark66\Warehouse LaserJet")
Set rpt.Printer = Application.Printers("\\CLARK66\Warehouse
LaserJet")
Case 2
On Error Resume Next
Set rpt.Printer = Application.Printers("\\Clark66\Warehouse Copier")
Set rpt.Printer = Application.Printers("\\clark66\Warehouse Copier")
Set rpt.Printer = Application.Printers("\\CLARK66\Warehouse Copier")
Case 3
On Error Resume Next
Set rpt.Printer = Application.Printers("\\Clark66\Main Office
Copier")
Set rpt.Printer = Application.Printers("\\clark66\Main Office
Copier")
Set rpt.Printer = Application.Printers("\\CLARK66\Main Office
Copier")
Case 4
On Error Resume Next
Set rpt.Printer = Application.Printers("\\Clark66\Human Resources
Copier")
Set rpt.Printer = Application.Printers("\\clark66\Human Resources
Copier")
Set rpt.Printer = Application.Printers("\\CLARK66\Human Resources
Copier")
End Select
DoCmd.OpenReport ("Pick")
DoCmd.Close acReport, "Pick"
End Sub
--


Brian Scheele
IT Manager
Clark Filter
3649 Hempland Road
Lancaster, PA 17601-1323

what version of ms-access?

In access 2002 and later, there is a built in printer object, and it lets
you switch the printer with ease.

You can use:

Set Application.Printer = Application.Printers("HP LaserJet Series II")

So, to save/switch, you can use:

dim strDefaultPrinter as string

get current default printer.
strDefaultPrinter = Application.Printer.DeviceName

switch to printer of your choice:

Set Application.Printer = Application.Printers("HP LaserJet Series II")

do whatever...print the 4 reports.


Switch back.

Set Application.Printer = Application.Printers(strDefaultPrinter)

I think using the Application.Printer is a good deal easier, and you don't
have to open the report first.....

so, set the printer....print the reports...and then set the printer back to
what it was before...

If you are using a version of ms-access prior to access 2002, then you could
use this printer switch code of mine here:

http://www.members.shaw.ca/AlbertKal.../msaccess.html


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(E-Mail Removed)

I am using Access 2007.

If I understand correctly, this is code to change my default printer from
withing Access, then I print reports, then it switches it back to whatever it
was.

If this is the case, the solves have the problem (which was much more of a
nuissance than the one that remains).

The remaining problem is that when I use
Set Application.Printer = Application.Printers("\\SERVERNAME\PRINTERSHARE")

My users may have varying names of the printer share where SERVERNAME could
appear as Servername or servername.

As you can see in my original code, I was just walking through the
possibilities using the On Error Resume Next statements.

Thanks for your input. I will give it a try tomorrow.
--


Brian Scheele
IT Manager
Clark Filter
3649 Hempland Road
Lancaster, PA 17601-1323


"Albert D. Kallal" wrote:

> what version of ms-access?
>
> In access 2002 and later, there is a built in printer object, and it lets
> you switch the printer with ease.
>
> You can use:
>
> Set Application.Printer = Application.Printers("HP LaserJet Series II")
>
> So, to save/switch, you can use:
>
> dim strDefaultPrinter as string
>
> get current default printer.
> strDefaultPrinter = Application.Printer.DeviceName
>
> switch to printer of your choice:
>
> Set Application.Printer = Application.Printers("HP LaserJet Series II")
>
> do whatever...print the 4 reports.
>
>
> Switch back.
>
> Set Application.Printer = Application.Printers(strDefaultPrinter)
>
> I think using the Application.Printer is a good deal easier, and you don't
> have to open the report first.....
>
> so, set the printer....print the reports...and then set the printer back to
> what it was before...
>
> If you are using a version of ms-access prior to access 2002, then you could
> use this printer switch code of mine here:
>
> http://www.members.shaw.ca/AlbertKal.../msaccess.html
>
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> (E-Mail Removed)
>
>
>

This worked for me, thanks!

Below is what I ended up with after adapting the code you provided. It is a
procedure prints a specified report to a printer specified by a number. The
code could probably be made shorter, but any performance gains would probably
be insignificant. Still to deal with the case sensitivity, which is why I
use the On Error Resume next and then follow with setting the printer 3
different ways. I can live with that. SERVER07 can appear 3 different ways
depending on which computer is running it.



Private Sub PrintReportToUserSpecifiedPrinter(ByVal strReportName As String,
ByVal intPrinterChoice As Integer)
' PrinterChoice comes from the radio button values in the options

Dim strDefaultPrinter As String, strReportDestination As String

strDefaultPrinter = Application.Printer.DeviceName 'Get the current
default printer

Select Case intPrinterChoice
Case 10
strReportDestination = "BOL Program - Main Office Copier"
Case 11
strReportDestination = "BOL Program - HR Copier"
Case 12
strReportDestination = "Main Office Color LaserJet"
Case 13
strReportDestination = "DCC Sales LaserJet"
Case 20
strReportDestination = "Warehouse LaserJet"
Case 21
strReportDestination = "Warehouse Copier"
Case 22
strReportDestination = "Main Office Copier"
Case 23
strReportDestination = "Human Resources Copier"
Case 30
strReportDestination = "Main Office 6x4 Label Printer"
Case 31
strReportDestination = "Main Office Label Printer"
Case 32
strReportDestination = "HR Label Printer"
Case Else
strReportDestination = strDefaultPrinter
End Select
On Error Resume Next
Set Application.Printer = Application.Printers("\\SERVER07\" &
strReportDestination)
Set Application.Printer = Application.Printers("\\Server07\" &
strReportDestination)
Set Application.Printer = Application.Printers("\\server07\" &
strReportDestination)
On Error GoTo 0
DoCmd.OpenReport strReportName
Set Application.Printer = Application.Printers(strDefaultPrinter)

End Sub
--


Brian Scheele
IT Manager
Clark Filter
3649 Hempland Road
Lancaster, PA 17601-1323


"Albert D. Kallal" wrote:

> what version of ms-access?
>
> In access 2002 and later, there is a built in printer object, and it lets
> you switch the printer with ease.
>
> You can use:
>
> Set Application.Printer = Application.Printers("HP LaserJet Series II")
>
> So, to save/switch, you can use:
>
> dim strDefaultPrinter as string
>
> get current default printer.
> strDefaultPrinter = Application.Printer.DeviceName
>
> switch to printer of your choice:
>
> Set Application.Printer = Application.Printers("HP LaserJet Series II")
>
> do whatever...print the 4 reports.
>
>
> Switch back.
>
> Set Application.Printer = Application.Printers(strDefaultPrinter)
>
> I think using the Application.Printer is a good deal easier, and you don't
> have to open the report first.....
>
> so, set the printer....print the reports...and then set the printer back to
> what it was before...
>
> If you are using a version of ms-access prior to access 2002, then you could
> use this printer switch code of mine here:
>
> http://www.members.shaw.ca/AlbertKal.../msaccess.html
>
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> (E-Mail Removed)
>
>
>

A follow-up question to anyone who can help:

After following Albert's advice, I was able to successfully change printers,
however it looks like print jobs will come from tray 1 only. the problem is
that I need the paper bin used to be tray 3, which is the large capacity tray
on the copier. It is pulling the wrong paper size from tray 1 only, no
matter what I have in the printer defaults or printer preferences.

I have tried messing around with the application.printer and
application.printers settings but could not get anywhere.
--


Brian Scheele
IT Manager
Clark Filter
3649 Hempland Road
Lancaster, PA 17601-1323


"Albert D. Kallal" wrote:

> what version of ms-access?
>
> In access 2002 and later, there is a built in printer object, and it lets
> you switch the printer with ease.
>
> You can use:
>
> Set Application.Printer = Application.Printers("HP LaserJet Series II")
>
> So, to save/switch, you can use:
>
> dim strDefaultPrinter as string
>
> get current default printer.
> strDefaultPrinter = Application.Printer.DeviceName
>
> switch to printer of your choice:
>
> Set Application.Printer = Application.Printers("HP LaserJet Series II")
>
> do whatever...print the 4 reports.
>
>
> Switch back.
>
> Set Application.Printer = Application.Printers(strDefaultPrinter)
>
> I think using the Application.Printer is a good deal easier, and you don't
> have to open the report first.....
>
> so, set the printer....print the reports...and then set the printer back to
> what it was before...
>
> If you are using a version of ms-access prior to access 2002, then you could
> use this printer switch code of mine here:
>
> http://www.members.shaw.ca/AlbertKal.../msaccess.html
>
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> (E-Mail Removed)
>
>
>

"Brian Scheele" <(E-Mail Removed)> wrote in message
news:E3A7910B-A79C-480E-8030-(E-Mail Removed)...
>A follow-up question to anyone who can help:
>
> After following Albert's advice, I was able to successfully change
> printers,
> however it looks like print jobs will come from tray 1 only. the problem
> is
> that I need the paper bin used to be tray 3, which is the large capacity
> tray
> on the copier. It is pulling the wrong paper size from tray 1 only, no
> matter what I have in the printer defaults or printer preferences.
>
> I have tried messing around with the application.printer and
> application.printers settings but could not get anywhere.
> --

I don't have a handy solution...but I recall several others did. I going to
suggest you create a new question. This one not only scrolled down, but the
subject is such that it unlikey it will pull in someone who has a quick
answer handy..

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(E-Mail Removed)