how can I create multiple documents from 1 report?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to distribute schedules to several clients and currently have a report
that is grouped by client name. After I run the report I convert it to
either word or a pdf and then manually go in and save each page as an
separate document for each client. Is there a way to have the report
automatically generate and name a separate document for each client?
 
You would have to create a separate report for each client. Not a different
report, but a copy filtered for the client. You could do something like this:

strSQL = "SELECT DISTINCT Client FROM ClientTable;"

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

Do While Not rst.EOF
strWhere = "Client = '" & rst!Client & "'"
Docmd.OpenReport "MyReport", , ,strWhere
MoveNext
Loop

rst.Close
Set rst = Nothing
 
Thank you, I am what I would call an (advanced) basic user and have not
written any code at all and instead usually can get what I need by using the
macros so I have a few questions:

1. I assume this is a code I could attach to a button on my form?
2. the ClientTable would include all of the possible client names -- and
the names need to be in a field call "Client"?
3. Any other variables in the code that I need to modify to fit my table,
report and field names?
 
See answers below
--
Dave Hargis, Microsoft Access MVP


kim.in.denver said:
Thank you, I am what I would call an (advanced) basic user and have not
written any code at all and instead usually can get what I need by using the
macros so I have a few questions:

1. I assume this is a code I could attach to a button on my form?
Click to expand...
Yes, it would go in the button's click event
2. the ClientTable would include all of the possible client names -- and
the names need to be in a field call "Client"?
Click to expand...
You would use whatever table you have that has the clients. The field you
would use would be the field that has the unique client code. You would use
whatever the name that field is. The names I used are just for example. You
would need to substitute the your actual names.
3. Any other variables in the code that I need to modify to fit my table,
report and field names?
Click to expand...

There are no other fields or variables that I know of.
 
getting an error -- compile error: sub or function not defined

The debugger highlights "MoveNext" and then Private Sub Command0_Click().

What do I have wrong?

Private Sub Command0_Click()
strSQL = "SELECT DISTINCT Client FROM Interests;"

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

Do While Not rst.EOF
strWhere = "Client = '" & rst!Client & "'"
DoCmd.OpenReport "Client Interests", , , strWhere
MoveNext
Loop

rst.Close
Set rst = Nothing
End Sub
 
beautiful, works like a charm!

Next question, how do I change the output to either print preview or ideally
word or pdf?
 
Word I don't know about. I have never done that. For pdf, you need a pdf
printer driver installed and you have to set the print device to it. for
preview, use the view argument of the OpenReport method.

docmd.OpenReport "ReportName", acViewPreview
 
last question....for print to pdf option, it asks me to name each report as
it converts it, since I don't know which client list it is preparing, not
sure how to name them. If I use the print preview option, it only prepares
the report for the first record.

I found the OututTo syntax but would like the file name to be a variable
that uses the "client" for the name. For example, if my first record is
"client" Jim Jones, I would want the file output name to be jim jones.xls.
And then if the second record is Sally Smith, the file output name should be
sally smith.xls.

Any ideas?

This is the code.....
strSQL = "SELECT DISTINCT Client FROM Interests;"

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

Do While Not rst.EOF
strWhere = "Client = '" & rst!Client & "'"
DoCmd.OutputTo acOutputReport, "Client Interests", acFormatXLS, ,
strWhere
rst.MoveNext
Loop

rst.Close
Set rst = Nothing
 
I see you are using the Outputto method rather than the Openreport method.
My answers were based on the OpenReport method. I don't use the Outputto
method.
I don't think I can help with that.
 
Back
Top