Printing multiple reports

[Gater]

Hi all, I have an access 2002 database I use for organising jobs in our workshop. I have a table that uses the auto number field as a unique job number and each record is the job along with various other details. At the moment to print a "job sheet" report for each job, I run a query that the criteria asks for a job number (the auto number assigned to the record), then click ok to preview the "job sheet" report. From there I just print the report as normal. Is it possible to somehow enter a job sheet number "from" and "to" to enable me to print multiple job sheets in one go or batch? The one at a time approach works but can take quiet a while when organising many jobs at once.


Many thanks,
Gater

[June7]

Options:

1. one report with filter criteria to include records that meet the "from" and "to" criteria - design report to force page break for each job

2. VBA looping structure to print each job as individual report

[Gater]

Hi june7,
I think I prefer the option 2 solution. How would I go about this?

[June7]

Open a recordset of the desired job number, then in looping structure, open report filtered to one job number, print report, close report, move to next job number record and repeat. Something like:

Dim rs As DAO.Recordset
If Not IsNull(Me.tbxStart) And Not IsNull(Me.tbxEnd) Then
Set rs = CurrentDb.OpenRecordset("SELECT JobNumber FROM tablename WHERE JobNumber BETWEEN " & Me.tbxStart & " AND " & Me.tbxEnd & ";")
While Not rs.EOF
DoCmd.OpenReport "reportname", , , "JobNumber=" & rs!JobNumber
DoCmd.Close acReport, "reportname"
rs.MoveNext
Wend
End If

[Gater]

Thanks for that. Would this code be placed in a command button on click property?

[June7]

That's a common location for code like this.

[Gater]

Many thanks