setting printer for pdf creation then reset default printer

[Pat]

I have a need to create a pdf file from a report, but i also have a need to be able to print the report.

How would i set the printer to a pdf creater like PrimoPDF then after creating the pdf, reset the printer to the default again.

[JohnH]

If you use Stephan Leban's ConvertReportToPDF then you don't need to worry about changing printers.

But here is some code on how to change printers.

[HansV]

First, run this code:

Code: Select all

Sub ListPrinters()
  Dim i As Integer
  For i = 0 To Application.Printers.Count - 1
    Debug.Print i, Application.Printers(i).DeviceName
  Next i
End Sub

Find the number corresponding to the PrimoPDF printer. Let's say it's number 4.

You can now use

Code: Select all

  Dim prt As Printer
  ' Store current printer in variable
  Set prt = Application.Printer
  ' Switch to PDF
  Set Application.Printer = Application.Printers(4)
  ' Print something to PDF
  ...
  ' Restore original printer
  Set Application.Printer = prt

[Pat]

Thank you John and Hans

[Pat]

If you use Stephan Leban's ConvertReportToPDF then you don't need to worry about changing printers.

But here is some code on how to change printers.

When using LeBans ConvertReportToPDF can you nominate a report and use a where clause too?
Or do i need to make a special query to use criteria?

[HansV]

You can only specify a report name. The code uses DoCmd.OutputTo to produce a snapshot file which in its turn is converted to PDF. OutputTo doesn't let you specify a where clause.

[Pat]

Thanks Hans, i thought as much after looking at the code.

Looks like i will have to change the SQL of the query behind the report or use a predetermined query.
Or maybe use PDF995, even this way i need to setup PDF995 as a printer on each PC that will use the database.

Thinks i'll go with the SQL or fixed query idea.

Another question: Does it work in an MDE database?

[HansV]

Yes, it should, as long as the two DLLs that come in the ReportToPDF zip file are in the same folder as the (.mdb or .mde) database or in the user's Windows32 folder.

[Pat]

Thank you i'll try it.

[Pat]

Another question, I copied the modReportToPDFinto my database from LeBans mdb and i get the following error:
User defined type not defined
on the following command:
Set clsDialog = New clsCommonDialog

What dll or ocx do i need to use in references?

[Pat]

I needed to copy the module clsCommonDialog from LeBans mdb to mine.

[HansV]

Alternatively, you could use Application.FileDialog - this has been available since Access 2002. But the class module works well so there is no real need to switch.

[JohnH]

When using LeBans ConvertReportToPDF can you nominate a report and use a where clause too?
Or do i need to make a special query to use criteria?

The code uses OutputTo which like (SendObject) does not support a Where clause, but there is a way around this that I use often.

If you open the report in Preview Mode using a Where clause, then run the code while the report is open, the currently open report is the one used by OutputTo (or SendObject), and so the Where clause remains in effect.

[Pat]

Bugger, oh excuse me.
I have just completed changing queries behind the report to insert a WHERE clause.
I seem to remember doing what you have said to do years ago.
Oh well i could undo all that code i guess, it's not that much anyway.

Thanks John.

[Pat]

I have decided to go with your suggestion John, and it works beautifully.

[Mark L]

Looks like i will have to change the SQL of the query behind the report or use a predetermined query.
Or maybe use PDF995, even this way i need to setup PDF995 as a printer on each PC that will use the database.

Thinks i'll go with the SQL or fixed query idea.

In the long run, I think you'll find it better to get away from using the WHERE parameter in docmd.openReport. I'll use a named query as recordsource for a report, but then form the SQL in code and modify the query prior to opening the report. Besides being able to use "ConvertReportToPDF" (which I use also), it is also useful when I want to export the query to Excel, or when I have an SQL Server backend, and it is more efficient to use a pass-through query.

[JohnH]

In the long run, I think you'll find it better to get away from using the WHERE parameter in docmd.openReport.

Personally, I will keep using them 95% of the time, where I have no need to Export to Excel.

One comment that has been made is that some people are moving away from "ConvertReporttoPDF" as Access 2010 is said to have removed support for Snapshots.
Access 2010 has removed Snapshot viewer, and removed all reference to snapshots in the interface, but they have not actually removed snapshots.
So ConvertReporttoPDF does still work with 2010.