I currently have 1 report that is summarized by client (1 client per page) and I am looking for correct VBA code to filter and copy the report and save with a new name. In the end, I will need a separate report for each client (It has to be done this way because when we print the file to PDF using Scansoft, it uses the original report name and we need the file name to incorporate the client number as well).
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)
Do While Not rs.EOF
DoCmd.OpenReport "I# Report", acViewPreview, , "[Carrier Report].[I#]=" & rs![I#], acHidden
MsgBox ("End of Client Org IDs")
My issue is that it's saving the copies of the reports with the correct client numbers from the list through the loop, however, it is ignoring the filter option and just resaving the entire report with a different name.
Any thoughts as to how I can copy the report using the filter???