Clicky

I am trying to find a simple way to automate printing several reports to pdf in MS Access with no user intervention. The PDF files need to be saved in a specific location with different filenames and the current date. I have read through some of the past responses, but there seem to be a lot of different ways to get this done, and it is a bit confusing.  I have Access 2003 and Adboe Reader 8, and cannot download any files or modify any registry.

I have set up some code to try to do this (see attached).  The code creates a query for the unique reports to be printed for the day, then prints one of two versions of the report to pdf depending on the department.  It works for the most part, but has the following problems:

1) This requires that the default printer be set to pdf and that the pdf port be set to a specific location where the files will be saved. Multiple users are going to be running this and I want to avoid them having to set their default printers and specify a port manually every time they need to run this database.
2) I sort of cobbled this code together, and I am not sure it is the cleanest way to get the report to print to pdf. The way it is set up to save a generic file then rename it to the specific report name, there is a possibility that one file may be overlaid by the next report before it is renamed. I don't know if there is a better way to save directly using the correct report name.

Is there any way to avoid the manual setting of printer and pdf port, and possibly avoiding the renaming issue?

Thank you.
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
Function fncPrintTickets()

    Dim rstTicket As DAO.Recordset
    Dim mySQL, mySQL2, myClient As String
    Dim myDate As Date

    mySQL = "SELECT DISTINCT [Temp].[TID], [Tbl Special Report].[Special Report], [Temp].[Department1] " _
            & "FROM [Tbl Special Report] INNER JOIN [Temp] ON [Tbl Special Report].[Special Report ID] = [Temp].[Special Report ID]" _
            & "GROUP BY [Temp].[TID], [Tbl Special Report].[Special Report], [Temp].[Department1]"
            
    If DCount("[Special Report]", "qryTicket") > 0 Then
        Set rstTicket = CurrentDb.OpenRecordset(mySQL)
        myDate = Forms![Frm Tickets]![Frm Tickets Subform Detail].Form![Date]
        
        Do Until rstTicket.EOF
            
            If DCount("[Revised]", "Temp", "[Revised] = Yes") Then
                mySQL2 = rstTicket.Fields("Special Report").Value & " REV"
            Else
                mySQL2 = rstTicket.Fields("Special Report").Value
            End If
        
            If rstTicket![Special Report] = "Standard" Then
                DoCmd.OpenReport mySQL2, , , "[Department1]= '" & [rstTicket]![Department1] & "'"
                Name "W:TicketsTicket.pdf" As "W:Tickets" & rstTicket![TID] & " - " & rstTicket![Department1] & " - " & Format(myDate, "mmddyyyy") & ".pdf"
            Else
                DoCmd.OpenReport mySQL2
                Name "W:TicketsTicket.pdf" As "W:Tickets" & rstTicket![TID] & " - " & rstTicket![Special Report] & " - " & Format(myDate, "mmddyyyy") & ".pdf"
            End If
        
        DoEvents
    
        rstTicket.MoveNext
            Loop
        rstTicket.Close

        Set rstTicket = Nothing
        mySQL = " "

    End If

End Function

asked 07/28/2011 04:49

Kay64's gravatar image

Kay64 ♦♦


8 Answers:
Try this instead.
http://www.lebans.com/reporttopdf.htm
(it simply creates the PDF, there is no need to change the "Printer")

I really don't have time to delve into all of your code, but this should cover #1.

;-)

JeffCoachman
link
boag2000's gravatar image

boag2000

I had looked at that solution originally, and it seems to do a whole lot more than I need.  I was hoping there was a simpler solution that would just automatically set the printer and port.

Thanks.
link
Kay64's gravatar image

Kay64

In reality it only really does one thing... Convert a report to a PDF.

Yes, the code is complex...
Yes, it has a lot of options...
...but in the end, only one line of code is all that is needed.
All it really does is create the PDF.
(...and it's free!)
...so in this case what the issue with using it?

(I was hoping there was a simpler solution that would just automatically set the printer and port.)
...I'm confused, you call setting the printer and port "Simpler"?

JeffCoachman

link
boag2000's gravatar image

boag2000

The simplest way I know in with Access 2003 and prior is Lebans' free toop that boag2000 suggested.


It generally takes one line of code to do what you want.   You just import the modules and place the DLL's in the folder with the front end.  I have been using the code for years. I have never even looked at the code in the modules. I have never found a need or desire to look at the code or understand how it works.  All I need to understand is how to call the functions I need to use.
link
TheHiTechCoach's gravatar image

TheHiTechCoach

^
LOL

And like the Ken Getz Dialog box code, ...It just works...
:-)

Jeff
link
boag2000's gravatar image

boag2000

Here is some code to change to the Adobe printer, then change back (so you don't have to worry about changing the default printer):
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
Public Sub PrintToSpecificPrinter(strPrinter As String, strReport As String)
'Created by Helen Feddema 12-Feb-2010
'Last modified by Helen Feddema 12-Feb-2010

On Error GoTo ErrorHandler

   Dim prtCurrent As Printer
   Dim prtDefault As Printer
   
   'Save current default printer
   Set prtDefault = Application.Printer
   Debug.Print "Current default printer: " & prtDefault.DeviceName
   
   'Select a specific printer as new default printer
   Application.Printer = Printers(strPrinter)
   
   'Print the report
   DoCmd.OpenReport strReport
   
   'Set printer back to former default printer
   Application.Printer = prtDefault
      
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & _
      Err.Description
   Resume ErrorHandlerExit

End Sub

================================

Public Function ListPrinters()
'Lists Access printer names as used in VBA code
'to the Immediate window
  
   Dim prt As Access.Printer
   
   For Each prt In Application.Printers
      Debug.Print prt.DeviceName
   Next prt
   
End Function
link
Helen_Feddema's gravatar image

Helen_Feddema

I used to use that method before Access 2007 introduced built-in PDF support.
link
Helen_Feddema's gravatar image

Helen_Feddema

Helen,

How would you set the Path/FileName, ...etc of the PDF?

Jeff
link
boag2000's gravatar image

boag2000

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×2
×44

Asked: 07/28/2011 04:49

Seen: 923 times

Last updated: 08/25/2011 01:44