ConvertReportToPDF not Distinguishing Between Schools

[Antney]

Can anyone help me out with this???!!!

I finally figured out how to convert my Access reports to PDF, thanks
Stephen, but it's not separating the schools into their own PDFs. It
separates the PDFs by their school name, which is great and what I want, but
ALL of the schools are in each PDF file!

I need only that school in its' own PDF file.

Also, I'd like to reset my query at the end, if that's possible?

I DID NOT change any of Stephen's code in his modules or class!

Here is my code behind my command print button:

Option Compare Database
Option Explicit

Private Sub cmdReportToPDF_Click()
On Error GoTo Err_cmdReportToPDF_Click

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

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

With rs
Do Until (.EOF Or .BOF) = True

Dim qdf As DAO.QueryDef

'strReport = "rptStudentDataSheet_0708"
strDocName = "C:\Documents and Settings\anthony.johnson\My
Documents\Anthony\ " & !SiteName & ".pdf"

Set qdf = db.QueryDefs("qrySchools")
qdf.SQL = "SELECT * FROM tblStudentDataSheet_0708 WHERE
School= " & rs("School")
qdf.Close
Set qdf = Nothing
blRet = ConvertReportToPDF("rptStudentDataSheet_0708",
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

Here is Stephen Lebans' module code:

 

[Graham Mandeno]

Hi Antony

There are some strange things in your code:

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

This opens a recordset on the query names "qrySchools". Presumably this has
one record per school.

Set qdf = db.QueryDefs("qrySchools")
qdf.SQL = "SELECT * FROM tblStudentDataSheet_0708 WHERE School= " _
& rs("School")
qdf.Close

This modifies the SQL for qrySchools so that it selects every record from
tblStudentDataSheet_0708 with a school matching the school in the current
record of qrySchools.

Now, did that last sentence make your head spin? Is does mine! It is
incestuous to say the least!

I think I see what you are trying to do - modify the query that is the
RecordSource for your report so that it includes only one school and then
export that PDF, then repeat for the next school. However, I am prepared to
bet a week's income (not a lot, BTW!) that the RecordSource of
rptStudentDataSheet_0708 is NOT qrySchools.

I think what you should do is leave the RecordSource query alone and instead
open the report with a WhereCondition to filter by the required school:

strReport = "rptStudentDataSheet_0708"
strDocFolder = "C:\Documents and Settings\anthony.johnson\My
Documents\Anthony\"
Do Until rs.EOF
strDocName = strDocFolder & rs!SiteName & ".pdf"
strFilter = "School=" & rs!School
DoCmd.OpenReport strReport, acViewPreview, , strFilter, acHidden
If Reports(strReport).HasData Then
Call ConvertReportToPDF(strReport, vbNullString, strDocName, , ,
150)
End If
DoCmd.Close acReport, strReport
rs.MoveNext
Loop
rs.Close

I'm assuming on the "strFilter=" line that the filtering field is named
School in both queries and that it is a numeric field. You may need to
adjust that line to suit.

 

[Antney]

Thank you Graham!

Hi Antony

There are some strange things in your code:

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

This opens a recordset on the query names "qrySchools". Presumably this has
one record per school.

Set qdf = db.QueryDefs("qrySchools")
qdf.SQL = "SELECT * FROM tblStudentDataSheet_0708 WHERE School= " _
& rs("School")
qdf.Close

This modifies the SQL for qrySchools so that it selects every record from
tblStudentDataSheet_0708 with a school matching the school in the current
record of qrySchools.

Now, did that last sentence make your head spin? Is does mine! It is
incestuous to say the least!

I think I see what you are trying to do - modify the query that is the
RecordSource for your report so that it includes only one school and then
export that PDF, then repeat for the next school. However, I am prepared to
bet a week's income (not a lot, BTW!) that the RecordSource of
rptStudentDataSheet_0708 is NOT qrySchools.

I think what you should do is leave the RecordSource query alone and instead
open the report with a WhereCondition to filter by the required school:

strReport = "rptStudentDataSheet_0708"
strDocFolder = "C:\Documents and Settings\anthony.johnson\My
Documents\Anthony\"
Do Until rs.EOF
strDocName = strDocFolder & rs!SiteName & ".pdf"
strFilter = "School=" & rs!School
DoCmd.OpenReport strReport, acViewPreview, , strFilter, acHidden
If Reports(strReport).HasData Then
Call ConvertReportToPDF(strReport, vbNullString, strDocName, , ,
150)
End If
DoCmd.Close acReport, strReport
rs.MoveNext
Loop
rs.Close

I'm assuming on the "strFilter=" line that the filtering field is named
School in both queries and that it is a numeric field. You may need to
adjust that line to suit.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Can anyone help me out with this???!!!

I finally figured out how to convert my Access reports to PDF, thanks
Stephen, but it's not separating the schools into their own PDFs. It
separates the PDFs by their school name, which is great and what I want,
but
ALL of the schools are in each PDF file!

I need only that school in its' own PDF file.

Also, I'd like to reset my query at the end, if that's possible?

I DID NOT change any of Stephen's code in his modules or class!

Here is my code behind my command print button:

Option Compare Database
Option Explicit

Private Sub cmdReportToPDF_Click()
On Error GoTo Err_cmdReportToPDF_Click

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

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

With rs
Do Until (.EOF Or .BOF) = True

Dim qdf As DAO.QueryDef

'strReport = "rptStudentDataSheet_0708"
strDocName = "C:\Documents and Settings\anthony.johnson\My
Documents\Anthony\ " & !SiteName & ".pdf"

Set qdf = db.QueryDefs("qrySchools")
qdf.SQL = "SELECT * FROM tblStudentDataSheet_0708 WHERE
School= " & rs("School")
qdf.Close
Set qdf = Nothing
blRet = ConvertReportToPDF("rptStudentDataSheet_0708",
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

Here is Stephen Lebans' module code: