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.






















"Antney" wrote:

> 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

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

"Danny Seager" wrote:

> 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.
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> "Antney" wrote:
>
> > 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

So once the code has ran what happens when you open the query... is it
displaying the last school in the recordset?

"Antney" wrote:

> 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
>
> "Danny Seager" wrote:
>
> > 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.
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > "Antney" wrote:
> >
> > > 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

Yes.

"Danny Seager" wrote:

> So once the code has ran what happens when you open the query... is it
> displaying the last school in the recordset?
>
> "Antney" wrote:
>
> > 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
> >
> > "Danny Seager" wrote:
> >
> > > 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.
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > "Antney" wrote:
> > >
> > > > 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

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!!!

"Danny Seager" wrote:

> So once the code has ran what happens when you open the query... is it
> displaying the last school in the recordset?
>
> "Antney" wrote:
>
> > 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
> >
> > "Danny Seager" wrote:
> >
> > > 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.
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > "Antney" wrote:
> > >
> > > > 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

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 Lebans" wrote:

> If you look at the code behind the sample form in the MDB you downloaded
> from my site you will see that the function declaration for
> ConvertReportToPDF is COMMENTED OUT. Why do you have it uncommented in the
> code behind your form within the General Declarations area at the top of
> your form? THis function is ALREADY declared in the modReportToPDF code
> module.
>
> I don't understand how your code even compiles. The compiler should complain
> about duplicate public functions with the same name.
> Let me duplicate your mods here and test.
> There's something wrong here. The compiler accepts the two public functions
> with the same name.
>
> Placed behind your form:
> Dim bl As Boolean
> bl = Me.ConvertReportToPDF("", "", False, False)
> bl = Application.Modules.modReportToPDF.ConvertReportToPDF("", "", False,
> False)
>
> So I guess the compiler accepts ConvertReportToPDF as two seperate
> functions, one as a Method of your form's Class, the other as a function of
> the modReportToPDF code module. Who new.
>
> Your issue is that you have declared ConvertReportToPDF within the code
> behind your form. But your declaration of ConvertReportToPDF does absolutely
> nothing. You need to use the ConvertReportToPDF function as declared in
> modReportToPDF. You have several hundred lines of code missing from your
> declaration.
>
> So simply comment out your declaration of ConvertReportToPDF at the top of
> the code behind your form.
>
> --
>
> HTH
> Stephen Lebans
> http://www.lebans.com
> Access Code, Tips and Tricks
> Please respond only to the newsgroups so everyone can benefit.
>
>
> "Antney" <(E-Mail Removed)> wrote in message
> news:00278144-F8AE-4190-87EF-(E-Mail Removed)...
> > 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!!!
> >
> > "Danny Seager" wrote:
> >
> >> So once the code has ran what happens when you open the query... is it
> >> displaying the last school in the recordset?
> >>
> >> "Antney" wrote:
> >>
> >> > 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
> >> >
> >> > "Danny Seager" wrote:
> >> >
> >> > > 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.
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > > "Antney" wrote:
> >> > >
> >> > > > 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.

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.

"Stephen Lebans" wrote:

> If you look at the code behind the sample form in the MDB you downloaded
> from my site you will see that the function declaration for
> ConvertReportToPDF is COMMENTED OUT. Why do you have it uncommented in the
> code behind your form within the General Declarations area at the top of
> your form? THis function is ALREADY declared in the modReportToPDF code
> module.
>
> I don't understand how your code even compiles. The compiler should complain
> about duplicate public functions with the same name.
> Let me duplicate your mods here and test.
> There's something wrong here. The compiler accepts the two public functions
> with the same name.
>
> Placed behind your form:
> Dim bl As Boolean
> bl = Me.ConvertReportToPDF("", "", False, False)
> bl = Application.Modules.modReportToPDF.ConvertReportToPDF("", "", False,
> False)
>
> So I guess the compiler accepts ConvertReportToPDF as two seperate
> functions, one as a Method of your form's Class, the other as a function of
> the modReportToPDF code module. Who new.
>
> Your issue is that you have declared ConvertReportToPDF within the code
> behind your form. But your declaration of ConvertReportToPDF does absolutely
> nothing. You need to use the ConvertReportToPDF function as declared in
> modReportToPDF. You have several hundred lines of code missing from your
> declaration.
>
> So simply comment out your declaration of ConvertReportToPDF at the top of
> the code behind your form.
>
> --
>
> HTH
> Stephen Lebans
> http://www.lebans.com
> Access Code, Tips and Tricks
> Please respond only to the newsgroups so everyone can benefit.
>
>
> "Antney" <(E-Mail Removed)> wrote in message
> news:00278144-F8AE-4190-87EF-(E-Mail Removed)...
> > 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!!!
> >
> > "Danny Seager" wrote:
> >
> >> So once the code has ran what happens when you open the query... is it
> >> displaying the last school in the recordset?
> >>
> >> "Antney" wrote:
> >>
> >> > 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
> >> >
> >> > "Danny Seager" wrote:
> >> >
> >> > > 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.
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > > "Antney" wrote:
> >> > >
> >> > > > 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.