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

-- 
Dave Hargis, Microsoft Access MVP


"kim.in.denver" wrote:

> 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?
> 
> 

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?

"Klatuu" wrote:

> 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
> 
> -- 
> Dave Hargis, Microsoft Access MVP
> 
> 
> "kim.in.denver" wrote:
> 
> > 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?
> > 
> > 

See answers below
-- 
Dave Hargis, Microsoft Access MVP


"kim.in.denver" wrote:

> 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?  
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"?  
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?

There are no other fields or variables that I know of.
> 
> "Klatuu" wrote:
> 
> > 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
> > 
> > -- 
> > Dave Hargis, Microsoft Access MVP
> > 
> > 
> > "kim.in.denver" wrote:
> > 
> > > 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?
> > > 
> > > 

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

"Klatuu" wrote:

> 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
> 
> -- 
> Dave Hargis, Microsoft Access MVP
> 
> 
> "kim.in.denver" wrote:
> 
> > 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?
> > 
> > 

Sorry kim,  it should be
    rst.MoveNext
-- 
Dave Hargis, Microsoft Access MVP


"kim.in.denver" wrote:

> 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
> 
> "Klatuu" wrote:
> 
> > 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
> > 
> > -- 
> > Dave Hargis, Microsoft Access MVP
> > 
> > 
> > "kim.in.denver" wrote:
> > 
> > > 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?
> > > 
> > > 

beautiful, works like a charm!

Next question, how do I change the output to either print preview or ideally 
word or pdf?

"Klatuu" wrote:

> Sorry kim,  it should be
>     rst.MoveNext
> -- 
> Dave Hargis, Microsoft Access MVP
> 
> 
> "kim.in.denver" wrote:
> 
> > 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
> > 
> > "Klatuu" wrote:
> > 
> > > 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
> > > 
> > > -- 
> > > Dave Hargis, Microsoft Access MVP
> > > 
> > > 
> > > "kim.in.denver" wrote:
> > > 
> > > > 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?
> > > > 
> > > > 

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
-- 
Dave Hargis, Microsoft Access MVP


"kim.in.denver" wrote:

> beautiful, works like a charm!
> 
> Next question, how do I change the output to either print preview or ideally 
> word or pdf?
> 
> "Klatuu" wrote:
> 
> > Sorry kim,  it should be
> >     rst.MoveNext
> > -- 
> > Dave Hargis, Microsoft Access MVP
> > 
> > 
> > "kim.in.denver" wrote:
> > 
> > > 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
> > > 
> > > "Klatuu" wrote:
> > > 
> > > > 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
> > > > 
> > > > -- 
> > > > Dave Hargis, Microsoft Access MVP
> > > > 
> > > > 
> > > > "kim.in.denver" wrote:
> > > > 
> > > > > 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?
> > > > > 
> > > > > 

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
"Klatuu" wrote:

> 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
> -- 
> Dave Hargis, Microsoft Access MVP
> 
> 
> "kim.in.denver" wrote:
> 
> > beautiful, works like a charm!
> > 
> > Next question, how do I change the output to either print preview or ideally 
> > word or pdf?
> > 
> > "Klatuu" wrote:
> > 
> > > Sorry kim,  it should be
> > >     rst.MoveNext
> > > -- 
> > > Dave Hargis, Microsoft Access MVP
> > > 
> > > 
> > > "kim.in.denver" wrote:
> > > 
> > > > 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
> > > > 
> > > > "Klatuu" wrote:
> > > > 
> > > > > 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
> > > > > 
> > > > > -- 
> > > > > Dave Hargis, Microsoft Access MVP
> > > > > 
> > > > > 
> > > > > "kim.in.denver" wrote:
> > > > > 
> > > > > > 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?
> > > > > > 
> > > > > > 

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.
-- 
Dave Hargis, Microsoft Access MVP


"kim.in.denver" wrote:

> 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
> "Klatuu" wrote:
> 
> > 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
> > -- 
> > Dave Hargis, Microsoft Access MVP
> > 
> > 
> > "kim.in.denver" wrote:
> > 
> > > beautiful, works like a charm!
> > > 
> > > Next question, how do I change the output to either print preview or ideally 
> > > word or pdf?
> > > 
> > > "Klatuu" wrote:
> > > 
> > > > Sorry kim,  it should be
> > > >     rst.MoveNext
> > > > -- 
> > > > Dave Hargis, Microsoft Access MVP
> > > > 
> > > > 
> > > > "kim.in.denver" wrote:
> > > > 
> > > > > 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
> > > > > 
> > > > > "Klatuu" wrote:
> > > > > 
> > > > > > 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
> > > > > > 
> > > > > > -- 
> > > > > > Dave Hargis, Microsoft Access MVP
> > > > > > 
> > > > > > 
> > > > > > "kim.in.denver" wrote:
> > > > > > 
> > > > > > > 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?
> > > > > > > 
> > > > > > >