Print Query-Report Sections so the Printer will STAPLE each??

[kev100 via AccessMonster.com]

My office has one of those super network printer/copiers that has a staple
option in the printer settings.

I can print Multiple copies of a document, and if the Staple option is
selected, it will staple each copy. It's pretty nifty, actually.

We use these databases very often. They have thousands of records. One of
the record fields in a street name.

I have a report set up based on a query. The query sorts all the records by
street name.

Most databases have about 70 different street names.

The end result of the query is 100 or so pages filled with line by line of
the records (all in order by the street).

It would be GREAT if the report output would insert some sort of "document
break" (for lack of a better term) whenever the street name changed. This
would (I'm assuming) flag the printer to staple each section of the report.

Simply having a page break is not enough, I don't think. To the printer, it
has to appear as either a second copy, or an entirely new print job.

This may actually be 2 questions, I suppose....but if anyone has found a
solution to this, any advice would be greatly appreciated.

Thanks

 

[John Spencer]

If you are not using the Reports SORTING and GROUPING dialog to set up the
sorting, then you could be in for a surprise at some time in the future.

Access reports don't use the sort clause in a query. They may, but then again
the report could rearrange the order of the data. So I would fix that.

Back to your main problem. I think that you will have to use VBA and break your
report into multiple reports (each street a separate report) to get the
functionality you are looking for.

I would use your original query to return just the Street names into a recordset
and then call your original report in a loop. Something like the following
UNTESTED Aircode.


Private Sub ButtonDoManyReports_Click()

Dim rstAny As DAO.Recordset
Dim dbAny As DAO.Database
Dim strWhere As String

Set dbAny = CurrentDb()
Set rstAny = dbAny.OpenRecordset("SELECT DISTINCT Street FROM SomeTable
WHERE SomeField = ?????")

While Not rstAny.EOF
strWhere = "Street=" & Chf(34) & rstAny!Street & Chr(34)
DoCmd.OpenReport "FAQ", acViewNormal, , strWhere
rstAny.MoveNext
Wend

End Sub