How do I get each page of a report to be output as a separate PDF file?

I have a report in Access that I output as a PDF file by using the following command:

 DoCmd.OutputTo acOutputReport, "QueryAgenda", acFormatPDF, strStartDir & "Appeals_Committee_Agenda.pdf", False

What I would like to do is to be able to output each page of the report as a separate PDF file instead of a single PDF file with multiple pages which is what I am getting now.

Is there any way for me to accomplish this?

Thanks.
geeta_m9Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try

Change ID to the Filter suiting you

DoCmd.OpenReport "QueryAgenda", acViewPreview , , "ID=" & Me.ID, , acHidden
DoCmd.OutputTo acOutputReport, "QueryAgenda", acFormatPDF, strStartDir & "Appeals_Committee_Agenda_" & "ID=" & Me.ID & ".pdf", False
DoCmd.Close acReport, "QueryAgenda"

Open in new window


Regards
0
geeta_m9Author Commented:
I am assuming that this means I have to use a recordset with an SQL statement (to select a distinct ID) and loop through?
0
Jeffrey CoachmanMIS LiasonCommented:
<No Points wanted...>

<I am assuming that this means I have to use a recordset with an SQL statement (to select a distinct ID) and loop through? >
Yes...

Amending Rgonzo1971's code...

Dim rst as dao.recordset
set rst=Currentdb.openrecordset("TheRecordSourceForTheReport")

rst.movefirst
Do Until rst.eof
    DoCmd.OpenReport "QueryAgenda", acViewPreview , , "ID=" & Me.ID, , acHidden
    DoCmd.OutputTo acOutputReport, "QueryAgenda", acFormatPDF, strStartDir & "Appeals_Committee_Agenda_" & "ID=" & Me.ID & ".pdf", False
    DoCmd.Close acReport, "QueryAgenda"
    rst.movenext
Loop

msgbox "Done"

rst.close
set rst=nothing
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

geeta_m9Author Commented:
The report uses a query. In the report query, wouldn't I have to set the criteria in the ID column as equal to the Me.ID. Otherwise won't I end up with the entire report for all the individuals each time?
0
Jeffrey CoachmanMIS LiasonCommented:
If the report/query normally displays all records, then no criteria is needed.

The code  Rgonzo1971 posted simply filters the report for each ID

Git it a try, then let him/her know...
0
geeta_m9Author Commented:
I created a module with the following code:

Public Sub IndividualCommitteeReport()
Dim Sql As String
Dim rs As Recordset
Dim strStartDir As String
Dim pidValue As String
Dim pidVal As String

strStartDir = CurrentProject.Path & "\Reports\Appeals Committee\"

Sql = "SELECT pid FROM QueryAgenda"
Set rs = CurrentDb.OpenRecordset(Sql)

  rs.MoveLast
  rs.MoveFirst

  Do Until rs.EOF = True
    pidValue = rs.Fields(0)
    TempVars.Add "pidVal", pidValue
    DoCmd.OutputTo acOutputReport, "CommitteeIndividualReport", acFormatPDF, strStartDir & "Appeals_Committee_Report_" & rs.Fields(0) & ".pdf", False
    rs.MoveNext
  Loop
rs.Close

End Sub


And in the query, I put the criteria in the pid column equals to the pid value for the particular record in the loop, i.e., TempVars. pidVal and that seemed to do the trick.
0
geeta_m9Author Commented:
Thank you for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.