Run a report 50 times with different filters to output to different filenames (1 Viewer)

[jewels]

I have a report which needs to be run a number of times with different filters and then saved as a snapshot file with different file names. I've got as far as running it through the loop in vba but I can't print it to different filenames. Please help.
Thanks

 

[Mile-O]

Please post your code so far - stops us having to guess it.

 

[jewels]

Sorry, I suppose this would help.

Here is what I've got so far. Now I can either print it no problem with different criteria or output it to the snap files but then I can't set the criteria. What to I do?

Private Sub Command0_Click()
Dim strSQL As String
Dim strCriteria As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strName As String

Set db = CurrentDb()

strSQL = "SELECT PRACT_ID FROM Practices;"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
Do While Not rs.EOF
strCriteria = "PRACT_ID = " & rs!PRACT_ID
DoCmd.OpenReport "myreport", , , strCriteria
rs.MoveNext
Loop

Set rs = Nothing
Set db = Nothing
End Sub

 

[Mile-O]

What outcome does the following provide?

Private Sub Command0_Click()

    On Error GoTo Err_Command0_Click
    
    Const TableName As String = "Practices"
    Const ReportName As String = "MyReport"

    Dim strCriteria As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strName As String

    Set db = CurrentDb()
    
    Set rs = db.OpenRecordset(TableName, dbOpenForwardOnly)
    Do While Not rs.EOF
        strCriteria = "[PRACT_ID] = " & rs("PRACT_ID")
        DoCmd.OpenReport ReportName, acViewNormal, , strCriteria
        rs.MoveNext
    Loop

Exit_Command0_Click:
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
    
Err_Command0_Click:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_Command0_Click
    
End Sub

 

[jewels]

Result

It prints a report for each ID in my practice table but if I set my printer to PDF, it writes it to the same PDF file each time. If I could get a separate PDF for each report needed for each practice id, then this would be ok. Ideally, I want a separate report for each practice in snapshot format.

 

[Brian1960]

ActiveReport

From my reading I think there is a way to Export the ActiveReport to a snapshot file. You would do this immediateley after the OpenReport bit. BUT I have no idea how to do it.
Sorry, but I'll track this as I could do with knowing!

 

[modest]

Look up Docmd.OutputTo ... this is what I use to save reports so that I can dynamically add them as attachments in an email.

 

[jewels]

DoCmd.OutputTo.. does not provide option to add filter criteria to report. Its not a problem if I create 50 different versions of the report for each filter value that I want in order to create each separate snapshot file.

 

[fpendino]

Here are a couple of other solutions that you could try.

1. If consolidating this report into one is a viable option, you could setup the report to Group by the PRACT_ID field and have it create a new page for each. Search for Sorting and Grouping Reports for more info if needed.

2. You can base the report on a query which you can use a variable as a criteria, allowing to change that variable through code. Here's an example.

''''''''''''''''''''''''''''''''''''''''''''''''''''''
You'll need to create a Module and put the Follwing Code in(Save the Module as whatever)
'Used to assign data to
Public intPractID as Integer 'Assuming the PractID field is an integer

'Only Functions may be used as criteria for query
Public Function PractID() as Integer
'Assigns the variable to the Function
PractID = intPractID
End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''
When creating the Query for the report, put PractID() in the criteria for the Pract_ID field.

Private Sub Command0_Click()
Dim strSQL As String
Dim strCriteria As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strName As String

Set db = CurrentDb()

strSQL = "SELECT PRACT_ID FROM Practices;"

Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

Do While Not rs.EOF

'Assigns the public variable
intPractID = rs!PRACT_ID

'Outputs the report to the C: Drive and uses the intPractID as the file name
DoCmd.OutputTo acOutputReport, "myreport", acFormatSNP, "C:\" & intPractID & ".snp"

rs.MoveNext
Loop

Set rs = Nothing
Set db = Nothing
End Sub

 

[jewels]

Thank you. Using the function in the query works perfectly. You've been a great help. Thanks to everyone who contributed.