Multiple Access Reports

There's the odd problem of generating multiple reports from Access - such as individual invoices, individual rosters, etc. A single report as a pdf is not easily emailable or easily chopped up for each individual. The solution:

To create individual roster reports to be printed to pdf (see pdfcreator) based on the report timetable_roster: create a new blank report called, for example here, print_roster.

In the code of print_roster include, for example (SQL variations in bold):

Private Sub Report_Open(Cancel As Integer)

'  create roster reports

Dim rst As DAO.Recordset

Dim db As DAO.Database

Dim hold As String

Set db = CurrentDb

Set rst = db.OpenRecordset("SELECT roster.person FROM Roster GROUP BY roster.person;;")

 If rst.BOF Or rst.EOF = True Then GoTo jump_out

 rst.MoveFirst

Do While Not rst.EOF

person = rst!person

personwithapostrope=Replace(person,"'","''")

DoCmd.OpenReport "timetable_roster", acViewPreview, , "person='" & personwithapostrophe & "'", , person

DoCmd.OpenReport "timetable_roster", acViewNormal, , "person='" & personwithapostrophe & "'", , person

DoCmd.Close acReport, "timetable_roster"

rst.MoveNext

Loop

jump_out:

rst.Close

Set rst = Nothing

End Sub

Private Sub Report_Page()

DoCmd.Close acDefault, , acSaveNo

End Sub

In the code of the original timetable_roster report, include:

Private Sub Report_Open(Cancel As Integer)

' change report title

If Not IsNull(Me.OpenArgs) Then

        Me.Caption = OpenArgs

End If

End Sub