Save multiple reports to PDF (1 Viewer)

[urjudo]

Hello,
I have a question how to save multiple reports to PDF. How do I save them into individual page by order# instead save them all into one page by individual order#? I have a form that users enter Order date and Location. When I click Save Reports, if I have four order#, it save four reports but each report combined all four reports together. Is any way I can save as individual Report? Any help would be grate appreciate!

Here are my code:

Option Compare Database
Option Explicit
Dim RecordOne as Boolean
Dim stDocName as string
Dim NumberofRecords as Integer
Dim FilePath as String

Private Sub MoveToNextRecord()
   DoCmd.GoToRecord, , acNext

Private Sub SavetheRecord()
FilePath = Environ("UserProfile") & "\OneDrive\Order\"

If Me.Location = "PA"
   stDocName = "rptOrderA
   DoCmd.OutPutTo acOutputReport, "rptOrderA", acFormatPDF, FilePath & Me.Docket & "-rptOrderA.pdf"

   stDocName = "rptOrderB
   DoCmd.OutPutTo acOutputReport, "rptOrderB", acFormatPDF, FilePath & Me.Docket & "-rptOrderA.pdf"
Else 
    stDocName = "rptOrderA
    DoCmd.OutPutTo acOutputReport, "rptOrderA", acFormatPDF, FilePath & Me.Docket & "-rptOrderA.pdf"
End if

Private Sub SaveReports_Click()
On Error GoTo Err_SaveReports_Click
FilePath = Environ("UserProfile") & "\OneDrive\Order\"

For x= 1 To NumberofRecords
      SavetheRecord
      If x < NumberofRecords then
             MoveToNextRecord
     End if
Next x

stDocName = "rptConfirmLetter"
 DoCmd.OutPutTo acOutputReport, "rptConfirmLetter", acFormatPDF, FilePath & Me.Docket & "-rptOrderA.pdf"

DoCmd.Close acForm, "frmOrderBatch"

Exit_SaveReports_Click:
    Exit Sub
Err_SaveReports_Click:
   Msgbox Err.Description
   Resume Exit_SaveReports_Click
End Sub

Thank you for your time!

 

[theDBguy]

Hi. Not sure I fully understand the situation, but it sounds like you want to create a loop, so you can create/save a single report/pdf on each pass in the loop.

 

[urjudo]

@theDBguy ,
Yes, that's exactly what I want. Sometimes there are 10 orders in one OrderDate. For example, if user enter 12/7/20, Dept: Fiscal (sorry on the description I put Location, it should be dept, but the code is correct for the location). so it might have 5 different order#, each report would have it's own order#, I want to be able to save each report with it's owe order# once and not five time with all the report together and in different order#. Hope I explanation clear. Thanks

 

[theDBguy]

@theDBguy ,
Yes, that's exactly what I want. Sometimes there are 10 orders in one OrderDate. For example, if user enter 12/7/20, Dept: Fiscal (sorry on the description I put Location, it should be dept, but the code is correct for the location). so it might have 5 different order#, each report would have it's own order#, I want to be able to save each report with it's owe order# once and not five time with all the report together and in different order#. Hope I explanation clear. Thanks

Right. So, you could try setting up a recordset of all the records you want to print and then loop through each one and create separate PDFs.

 

[urjudo]

@theDBguy ,
Sorry, how to I set up a recordset for that?

 

[theDBguy]

@theDBguy ,
Sorry, how to I set up a recordset for that?

Sorry as well. Since we cannot see your database, we can only give you ideas. I can't give you exact instructions on how to create something that will work for your situation. As an example though, here's how you might create a recordset.

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

strSQL = "SELECT * FROM TableName WHERE SomeField='SomeCriteria'"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

With rs
    Do While Not .EOF
        'do something with this record
        .MoveNext
    Loop
    .Close
End With

Set rs = Nothing
Set db = Nothing

Hope that helps...

 

[Uncle Gizmo]

I answered a similar question a while back, and I placed it on my blog here:-

Generate Multiple Reports - Nifty Access

Generate Multiple Reports This blog came about because of an answer I gave to a particular OP's question. Therefore the code isn't generic, it is designed for a specific situation, like most examples! However I hope there's enough information here for you to follow what's happening. Multiple...

www.niftyaccess.com

The blog and videos might give you some idea of how to develop something similar yourself.

There is a downloadable example file is available for free to Access World Forum members. Contact me for details on how to get it for free ...

 

[urjudo]

@theDBguy , Thanks for the example!
@Uncle Gizmo , great info but do I have to create table for it or if anyway I don't need to use a table? Thanks!