Print PDF Reports Filename & Specify A Path

  • Thread starter Thread starter Antney
  • Start date Start date
A

Antney

Hi,

I've figured out how to print Access reports to PDF but now that I've
printed them, I want, in VBA code, to figure out how I can get the VBA code
to automatically print each reports name, instead of me having to type it in
once the 'File Name' dialog box pops up and then send it automatically to a
specifed folder instead of me again, having to specify a path. Can anyone
help? Thanks!

BTW, in my 'qrySchools', which links the school id with the
'rptStudentDataSheet_0708' school id, I do have the school name. That's the
file name I want to use for each respective report.

Here is my code:

Option Compare Database
Option Explicit

Private Sub cmdPrintReports_Click()
On Error GoTo Err_cmdPrintReports_Click

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("qrySchools", dbOpenSnapshot)

With rs
Do Until (.EOF Or .BOF) = True
DoCmd.OpenReport "rptStudentDataSheet_0708",
View:=acViewNormal, _
WhereCondition:="School = " & rs("School")
rs.MoveNext
Loop
End With

Exit_cmdPrintReports_Click:
'Cleanup
On Error Resume Next
rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
Exit Sub

Err_cmdPrintReports_Click:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in Test subroutine..."
Resume Exit_cmdPrintReports_Click
End Sub
 
Assuming your are using Stephans pdf system, then:

strReport = "rptStudentDataSheet_0708"
With rs
Do Until (.EOF Or .BOF) = True
DoCmd.OpenReport strReport, acViewNormal, ,"School = " &
rs("School")
reports(strReports).Visible = false
strDocName = "c:\MyoutPutdir\" & !SchoolName & ".pdf"
Call ConvertReportToPDF(strReport, , strDocName, False,
False)
DoCmd.Close acReport, strReportName
rs.MoveNext
Loop
End With
 
Albert,

Thanks for the code, I appreciate it! As I'm trying to get it to work, I got
a 'Sub or Function not defined' and it's referring to 'Call
ConvertReportToPDF'. How do I define this?

Thanks!
 
Antney said:
Albert,

Thanks for the code, I appreciate it! As I'm trying to get it to work, I
got
a 'Sub or Function not defined' and it's referring to 'Call
ConvertReportToPDF'. How do I define this?
Click to expand...

The convertReportToPDF is a free pdf creator that you can find here:
http://www.lebans.com/reporttopdf.htm

The beauty of the above system is that it is free, and does not even require
you to install a pdf printer driver or anything (so, it is a VERY nice
solution).
 
Back
Top