Multiple reports with one query.

[dixdo]

I have a query that contains multiple clients and then number of employees per client. What I want to do is create an invoice per client using the one query but can't figure out what to do. Right now it puts all of them on one report but I want seperate reports per client. Help!

 

[dhookom]

You can create a combo box on a form to select the client. Then use the value in the "where" condition of DoCmd.OpenReport. There are tons of code samples on how to use a control on a form to filter a report. You might want to search on my name and "strWhere = "1 = 1 "

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]

 

[dixdo]

Thanks for the reply but I would like to avoid picking by client but rather have them all be created at once but each client on a new report. Does that make sense?

 

[dhookom]

Have you considered just grouping by client and placing each on different pages?

If you truly want separate reports, you can create a recordset of clients and loop through it running a report as you step through. If this isn't familiar to you, come back with table and field names for clients and a report name.

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]

 

[dixdo]

Yes, a recordset is what I want to do. I have done it years ago but couldn't remember what it was called and how to do it. My table is tblPayroll with fields client, invoice amount, etc.. and the report is rptPayroll. Thank you!!

 

[dhookom]

I would use DAO like the following. You might need to set a reference to the MS DAO object library.

Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim strSQL as String
Dim strWhere as String
Set db = currentdb
strSQL = "SELECT DISTINCT Client FROM tblPayroll"

Set rs = db.OpenRecordset(strSQL)
With rs
   .MoveFirst
   Do Until .EOF
      'assuming Client is numeric
      strWhere = "[Client] = " & .Fields("Client")
      'if client is text use the next line
      'strWhere = "[Client] = """ & .Fields("Client") & """"
      Docmd.OpenReport "rptPayroll", , , strWhere
      .MoveNext
   Loop
   .Close
End With
Set rs = Nothing
Set db = Nothing

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]

 

[dixdo]

Thank you! This looks like something I did before so I will work with it. Thanks again.

 

[dixdo]

I got the previous code to work. Thanks!!...one thing I would like to change is that right now it automatically sends the report to the printer. I would like to preview the report and then print it myself. If I add acViewPreview it only brings up the first report then stops. Any thoughts?

 

[dhookom]

I would then just set up a list box with all the clients and select one at a time to preview and print.

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]