Hi all

Is there a way of doing this. I have a report which contains a page for each member of staff.I currently can view individual pages using a form which filters the report.

I am using 2007 so i can easily output the whole report to Pdf, what i need to do is output each indivual staff report to a seperate Pdf file ideally named by a field in the report.

Is this possible, i guess i would have to use vba

Many Thanks

Geoff

Geoff,

Yes, you would have to use VBA. You would need to open a recordset, based on the list of staff members, and loop through this recordset to adjust the SQL of the query that the report is based on, to select the data for each staff one by one.

If you need more explicit help with this, please provide the following information:
The name of the report.
The name of the query that the report is based on.
The SQL view of that query.
The name of the table that contains your employees, and the name of that table's primary key field.
The proposed name of the outputted PDF files.

Thanks.

Hopefully this makes sense

The name of the report.

Analysis

The name of the query that the report is based on.

the report is made up of 5 subreports, so the basic report is baisically built around my staff table, with another table directorates there to help me filter. The sub reprts are then generatted on the StaffIIDfield

The SQL view of that query.

SELECT Directorates.DirectorateID, Staff.[First Name], Staff.Surname, Staff.[Pro-rata], Directorates.Directorate, Staff.StaffID, Staff.Type
FROM Directorates INNER JOIN Staff ON Directorates.DirectorateID = Staff.Directorate
WHERE (((Directorates.DirectorateID)<>4) AND ((Staff.Type)=1));


sorry if i have not shown this right

The name of the table that contains your employees, and the name of that table's primary key field.

Staff contains my employees and primaryid is StaffID

The proposed name of the outputted PDF files.

Preferably first name and surname fields which are in the report

Geoff,

Ok, thanks. Here is an "air code" attempt at a skeleton procedure:

Dim dbs As DAO Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim baseSQL As String
Dim rptSQL As String
Set dbs = DBEngine(0)(0)
Set rst = dbs.OpenRecordset("SELECT StaffID FROM Staff", dbOpenSnapshot)
Set qdf = dbs.QueryDefs("NameOfTheQueryTheMainReportIsBasedOn")
baseSQL = "SELECT Directorates.DirectorateID, Staff.[First Name], Staff.Surname, Staff.[Pro-rata], Directorates.Directorate, Staff.StaffID, Staff.Type" & _
" FROM Directorates INNER JOIN Staff ON Directorates.DirectorateID = Staff.Directorate" & _
" WHERE (Directorates.DirectorateID <> 4) AND (Staff.Type = 1)"
With rst
Do Until .EOF
rptSQL = baseSQL & " And StaffID = " & !StaffID
qdf.SQL = rptSQL
DoCmd.OutputTo acOutputReport, "Analysis", acFormatPDF, "C:\YourFolder\" & ![FirstName] & "_" & [Surname] & ".pdf"
.MoveNext
Loop
.Close
End With
qdf.SQL = baseSQL
Set qdf = Nothing
Set rst = Nothing
Set dbs = Nothing

You would need to substitute the actual name of the query that the main report is based on.

Hope that's heading in the right direction.