Access Filter Report with Field Name

[epifinygirl]

I currently have a report that summarizes my data for each client from the table (each page summarizes the information for each client). I have VBA code all set that can filter each client and print it to PDF (using ScanSoft), however, my issue is that it prints/saves it using the same name as the original file. In the end, I will need a separate file or report for each client. I am looking for a better and more efficient way of doing so rather than manually creating a new report for each client using the filter option and saving it with a new name.

Currently, my code is as follows:

DoCmd.OpenReport ("I# Report"), acViewPreview, , "[Carrier Report].[I#]=10936"
 

In this example, it filters 1 client but when it saves it to PDF, it's saving it as "I# Report".

Any thoughts??

[patjones]

How many clients are we talking about? It almost seems more laborious to manage a different file for each client as opposed to having just one file where it's one client to each page.

If this is what you really need to do though, the approach that I would take is to populate a recordset with each client ID, and then setup a loop that goes through that recordset and runs the OpenReport action for each client. How does this sound to you?

Pat

[epifinygirl]

My main problem is saving it with the client's ID in the the name. The OpenReport would be fine with just opening the report and filtering it on a specific client.

[NeoPa]

We have a problem with your posted code. See When Posting (VBA or SQL) Code.

That said, we can probably guess what it really looks like. I would suggest something like (where txtClient is the control on the form that contains the client number) :

Call DoCmd.OpenReport(ReportName:="I#" & Me.txtClient & " Report", _
                      View:=acViewPreview, _
                      WhereCondition:="[Carrier Report].[I#]=" & Me.txtClient)