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.
245
