ConvertReportToPDF - Someone Help Please!!!

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

Antney

Hi,

I've listed several messages but I haven't found the right answer.

I'm trying to convert a report I have to a PDF. I want the code to label
each report respectively and place it in My Documents.

I've looked at Lebans code, which I am trying to use but to NO AVAIL! I've
copied over his dll's into the folder where my db is. I've copied over his
modules and classes into my db. I copied over his code, for the command
button. Everytime I hit the command button, my report starts to print from
the default printer. When I change the default to PDF, it brings up the
dialog box, which I don't want and then after the first report runs, I get an
error saying that the title is incorrect. What I am trying to do is print all
of my schools (110), label them respectively and place them all in a folder
of my choosing. I want to only click the print button once. I've copied over
my code. This is the only code I have for the print button and again, I have
Leban's modules and classes in my db along with the dll's in the folder.

If anyone could help, without referring me back to Leban's website, It would
be much appreciated.

Thanks!!!

Here's my code:

Option Compare Database
Option Explicit

' The function call is:
Public Function ConvertReportToPDF( _
Optional strReport As String = "", _
Optional SnapshotName As String = "", _
Optional strDocName As String = "", _
Optional ShowSaveFileDialog As Boolean = False, _
Optional StartPDFViewer As Boolean = True, _
Optional CompressionLevel As Long = 150, _
Optional PasswordOpen As String = "", _
Optional PasswordOwner As String = "", _
Optional PasswordRestrictions As Long = 0, _
Optional PDFNoFontEmbedding As Long = 0, _
Optional PDFUnicodeFlags As Long = 0 _
) As Boolean
End Function

Private Sub cmdReportToPDF_Click()
On Error GoTo Err_cmdPrintReports_Click

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strReport As String
Dim strDocName As String
Dim blRet As Boolean

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

With rs
Do Until (.EOF Or .BOF) = True
DoCmd.OpenReport strReport, acViewNormal, , "School = " &
rs("School")
Reports(strReport).Visible = False
strDocName = "C:\Documents and Settings\anthony.johnson\My
Documents\Anthony\ " & !SiteName & ".pdf"
blRet = ConvertReportToPDF(strReport, , strDocName, False,
False)
DoCmd.Close acReport, strReport
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
 
You're opening the report with

DoCmd.OpenReport strReport, acViewNormal, , "School = " &
rs("School")

what's why it's opening.

What you need to do is base the report on a query, then use querydefs to
change the query as you loop through the recordset

With rs
Do Until (.EOF Or .BOF) = True
strDocName = "C:\Documents and Settings\anthony.johnson\My
Documents\Anthony\ " & !SiteName & ".pdf"

Dim qdf As DAO.QueryDef
Set qdf = db.QueryDefs("YOUR_QUERY_NAME")
qdf.SQL = "SELECT * FROM TABLENAME WHERE School='" & rs(“Schoolâ€)
qdf.Close
Set qdf = Nothing


blRet = ConvertReportToPDF(strReport, , strDocName, False, False)

rs.MoveNext
Loop
End With

So this code changes the actual query that the report is based on for each
record in the recordset.
 
Danny,

Thank you so much for pointing that out to me. I've changed my code and it
seems to want to work fine but it is not outputting the files. When I open up
the query, the code is running the query, deleting the 'SiteName' field in
the query and placing each schools # under 'School' while changing the 'Group
By' to 'Where'. It seems to want to cycle through each school, which I think
I understand that part but it doesn't output anything. Can you help me out?
Thanks!

Here is my revised code:

Option Compare Database
Option Explicit

' The function call is:
Public Function ConvertReportToPDF( _
Optional strReport As String = "", _
Optional SnapshotName As String = "", _
Optional strDocName As String = "", _
Optional ShowSaveFileDialog As Boolean = False, _
Optional StartPDFViewer As Boolean = True, _
Optional CompressionLevel As Long = 150, _
Optional PasswordOpen As String = "", _
Optional PasswordOwner As String = "", _
Optional PasswordRestrictions As Long = 0, _
Optional PDFNoFontEmbedding As Long = 0, _
Optional PDFUnicodeFlags As Long = 0 _
) As Boolean
End Function

Private Sub cmdReportToPDF_Click()
On Error GoTo Err_cmdReportToPDF_Click

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strReport As String
Dim strDocName As String
Dim blRet As Boolean

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

With rs
Do Until (.EOF Or .BOF) = True
strDocName = "C:\Documents and Settings\anthony.johnson\My
Documents\Anthony " & !SiteName & ".pdf"

Dim qdf As DAO.QueryDef

Set qdf = db.QueryDefs("qrySchools")
qdf.SQL = "SELECT * FROM tblStudentDataSheet_0708 WHERE
School= " & rs("School")
qdf.Close
Set qdf = Nothing
blRet = ConvertReportToPDF(strReport, , strDocName, False,
False)
rs.MoveNext
Loop
End With

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

Err_cmdReportToPDF_Click:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in Test subroutine..."
Resume Exit_cmdReportToPDF_Click
End Sub
 
So once the code has ran what happens when you open the query... is it
displaying the last school in the recordset?
 
Danny,

Also, I wanted to mention that I have not changed the code, in any way,
within the modules or classes, was I supposed to?

Also, I've only placed part of Lebans' code, 'Function ConvertReportToPDF',
within my cmd button code:

Option Compare Database
Option Explicit

' The function call is:
Public Function ConvertReportToPDF( _
Optional strReport As String = "", _
Optional SnapshotName As String = "", _
Optional strDocName As String = "", _
Optional ShowSaveFileDialog As Boolean = False, _
Optional StartPDFViewer As Boolean = True, _
Optional CompressionLevel As Long = 150, _
Optional PasswordOpen As String = "", _
Optional PasswordOwner As String = "", _
Optional PasswordRestrictions As Long = 0, _
Optional PDFNoFontEmbedding As Long = 0, _
Optional PDFUnicodeFlags As Long = 0 _
) As Boolean
End Function

Private Sub cmdReportToPDF_Click()
On Error GoTo Err_cmdReportToPDF_Click

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strReport As String
Dim strDocName As String
Dim blRet As Boolean
Dim strSQLBase As String

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

With rs
Do Until (.EOF Or .BOF) = True
strDocName = "C:\Documents and Settings\anthony.johnson\My
Documents\Anthony\ " & !SiteName & ".pdf"

Dim qdf As DAO.QueryDef

Set qdf = db.QueryDefs("qrySchools")
qdf.SQL = "SELECT * FROM tblStudentDataSheet_0708 WHERE
School= " & rs("School")
qdf.Close
Set qdf = Nothing
blRet = ConvertReportToPDF("strReport", vbNullString,
"strDocName", False, False, 150, "", "", 0, 0, 0)
rs.MoveNext
Loop
End With

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

Err_cmdReportToPDF_Click:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in Test subroutine..."
Resume Exit_cmdReportToPDF_Click
End Sub

My code still doesn't label and output the files I need. I'm thinking that I
need to change some of the module coding???

Thanks again!!!
 
Stephen,

I did what you suggested but I still can't get it to output. It seems to run
fine but when I go into the folder where I directed it, nothing is there.

Can you help me out?

Thanks.
 
Stephen,

I got it to work. Only one thing, it is separating out the schools for me
and labeling them but the code is putting ALL of the schools in each PDF
instead of placing each school in its' own PDF.

Any thoughts?

Thanks.
 
Back
Top