How to Export a report to .PDF to separate sheets based on numbers

[anoble1]

I currently have a report that runs in MS Access.
The reports gives totals on parts by a User (that have a unique number)
Example User 075 has 7 pages of items, then the next user etc.

Is there a way to have access break up the report when it moves to the next User or number? Instead of having 1 big report have it break it up into Each user has their own report?

[twinnyfo]

anoble1,

Yes, this is very possible. There may be other ways, but the two most likely would be to 1) execute the report, applying a filter to it (based on the User), and saving each report as an individual report; or 2) modifying the Report's query so that it uses the User in its criteria (either through a Global variable or TempVars Variable) and cycling through the Users, assigning the User to the Variable and running the Report with this new query and saving to PDF.

Option 1 is probably simpler--it just depends on how you have other things set up in your project.

Standing by for further assistance, as needed.....

[anoble1]

Yeah, thought about that. But was wondering on if it could be done automated. I would have a whole lot of reports if I broke them up by user. Wonder if there would be some VBA that could go through and export based on the number or person?

[twinnyfo]

Yes, both methods would be automated. What method are you using for exporting your Report right now? We can use that for a launching pad to where you want to go....

[anoble1]

I have no VBA right now. I will probably write something to export to a PDF file later today and tomorrow. Right now I am exporting the master and Extracting the pages manually.

[twinnyfo]

I see. Lets try something similar to this:

Make a copy of the query behind your report. Name the copy "qryReportUsers " or something similar (you can also modify this query so that it only returns the users and groups by User. Modify the other query to use as the criteria for your User Field the following:

WHERE User = TempVars.Item("User")

This is a Temporary global variable that Access introduced in 2007. You will see how it is used below. If you have an earlier version of Access, we can easily over come this, but we will assume 2007 and later for now.

Below is the very basics of what you will want to do, and hopefully will point you in the right direction of where you want to go with this.

Private Sub ExportReports()
    Dim db As Database
    Dim rst As Recordset
    Dim strSQL As String
    Dim strPath As String
    strPath = "C:\YourPath\YourFolder\"
    Set db = CurrentDb()
    strSQL = "SELECT User " & _
        "FROM qryReportUsers " & _
        "GROUP BY User;"
    Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
    If Not rst.RecordCount = 0 Then
        rst.MoveFirst
        Do While Not rst.EOF
            TempVars.Add "User", rst!User
            DoCmd.OutputTo acOutputReport, _
                "YourReport Name", acFormatPDF, _
                strPath & "YourReport" & rst!User & ".pdf"
            rst.MoveNext
        Loop
    End If
    rst.Close
    db.Close
    Set rst = Nothing
    Set db = Nothing
End Sub

Note that Line 6 is the folder where you want to save these PDF files.

Lines 8-10 refer to the query we modified earlier, but also groups, just in case you did not.

In lines 16-18, we are exporting the PDF. Rmeember, because we added the criteria User = TempVars.Item(" User"), the Report should only return those pages that apply to that particular User. It also saves the Report with the identifier for the User (there are many ways to play with this).

I hope this gets you pointed in the right direction.

[anoble1]

I think I can do something with that. Let me work with that info!

[twinnyfo]

Let us know how you get along!