output to multiple pdf files

[geoffishere]

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

 

[SteveSchapel]

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.

 

[geoffishere]

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

 

[SteveSchapel]

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.