Print more than one Report

[Vitaminas]

I want to print for example a report for the first 10 clients automatically, but the reports have to be individual for each client.Anyone can help me?Thank you


Tiago Ferreira

 

[dhookom]

There is no such thing as "first 10 clients" without describing a method to sort your clients. I expect you would need to open a recordset of clients and loop through it running the report at each record.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[wemeier]

dhookom's right. There's no way to do FIRST 10. You can do TOP 10, but you'd have to have a sort field that will enumerate the customers in the order in which you want to pull them off.

Assuming you can do the above, the coding to generate the reports is relatively simple. Create VBA coding to open a recordset based on the distinct customers from your report's recordset, then loop through the customers. For each customer, run your report, filtering the customer to the current customer. For example:

Dim db as Database
Dim rst as Recordset
Dim strRecordSource as String

DoCmd.Echo False
DoCmd.OpenReport "rptCustomers", acViewPreview, , "'A'='Z'"
strRecordSource = Reports(ReportName).RecordSource
DoCmd.Close acReport, ReportName
DoCmd.Echo True

Set db = CurrentDb()
set rst = db.OpenRecordset("SELECT DISTINCT CustomerNumber FROM " & strRecordSource & ";")

While Not rst.EOF
    DoCmd.OpenReport "rptCustomers", acViewNormal, , "CustomerNumber=" & rst!CustomerNumber
    rst.MoveNext
Wend

rst.Close
Set db = Nothing

I've used CustomerNumber as the field containing the customer number and rptCustomers as the name of the report to print for each customer.

This assumes that the report's RecordSource is a query or table. If it is a SQL statement my code won't work as is. There's a way to fix it, however.

I couldn't find a way to get to the report's RecordSource unless the report was loaded, so I opened the report once in Preview mode with a filter that couldn't possibly be true (to reduce the time to load the report and format the pages), grab the RecordSource, then close the report. If you already know the report's RecordSource you can skip this code and assign the value to strRecordSource directly.

Cruising the Information Superhighway at the speed of light
[sub] (your mileage may vary)[/sub]