Copy Report with Filter

[epifinygirl]

I currently have a report that is filtered with a list of clients from a separate table, however, I need to create a a copy of that report for each client. Below is the code that I currently have:

Public Function OrgIDReports()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sqlStr As String

sqlStr = "SELECT * FROM [Temp Table]"

Set db = CurrentDb
Set rs = db.OpenRecordset(sqlStr)

rs.MoveFirst

Do While Not rs.EOF
  DoCmd.OpenReport "I# Report", acViewPreview, , "[Carrier Report].[I#]=" & rs![I#], acHidden
  DoCmd.RunCommand acCmdSaveAsReport
  rs.MoveNext
Loop

MsgBox ("End of Client Org IDs")

End Function

This code is making copies of the original report without the filter and I'm not sure how I would combine the OpenReport and Copy command in 1 line.

Any ideas??

[Rabbit]

I see no need to create a separate report for each client when all you have to do is open the report and filter it for whichever client you want.

[NeoPa]

I suspect you've overlooked the behaviour of naming files when the report is opened directly to print to file (such as PDF) Rabbit.

I'll go into the details when I get more time to post EpiphanyGirl.

[Rabbit]

I suppose you could rename the report, open it to print to file, and then change the name back.

[epifinygirl]

Could that work while using a table as the reference for the client ID's in the loop?

[NeoPa]

If you rename the report then you must assume the database is used strictly on a one user at a time basis (as you will be corrupting the database so it will be unusable by any others in that process). A solution that doesn't, which is still somewhat limited in that it can only work with databases where you have design change ability (Not MDEs or ACCDEs), is to make a copy of the report object with your required name within the loop of your code; Output the report; then delete the newly created and temporary report object.

Let me how much help you need with that process. I suggest you make a start on it yourself EpiphanyGirl, and let us know where/if you get stuck.

[epifinygirl]

The database would only be used on a "one user at a time" basis.

Maybe I've been staring at this for too long but I'm at a complete loss. So if you could at least point me in the right direction, I can maybe finish it. The cost I have above (in the main question portion of the post) copies the object and loops it with the correct file names, but I can't figure out how I would incorporate the filtering in that loop.

[NeoPa]

EpiphanyGirl:
The database would only be used on a "one user at a time" basis.

While I'm sure that's true, I would nevertheless advize, generally, to avoid designing yourself into a corner. It's likely you will want to re-use what you develop here at a later date and in other circumstances. It may work ok for this one, but hold you up again in future.

As for some code, you can work with :

DoCmd.CopyObject(NewName:=???, _
                 SourceObjectType:=acReport, _
                 SourceObjectName:="[I# Report]")

Where ??? represents the new name you want to assign.

EpiphanyGirl:
The cost (code?) I have above (in the main question portion of the post) copies the object and loops it with the correct file names, but I can't figure out how I would incorporate the filtering in that loop.

It certainly doesn't seem to do that as we can see (unless you've changed it). I see nothing which even indicates what you want to use as the new name.

As far as changing the filtering goes I can't see how to do that. I've found a way that sticks, but seems to be lost as soon as the report is opened. Without the database to test on it's a bit hard for me to proceed with this. I'll show you what I had for report name = X though. In case it helps.

Report_X.Filter = "[I#]=" & rs![I#]
Report_X.FilterOn = True

If, after this, I print the value it certainly shows as expected, but when I next open the report it's gone. Maybe you could test it with opening the report afterwards in acViewNormal mode.