Use Filter string AND OutputTo together to create multiple reports

[Thall]

Hey Gurus - I've seen a few solutions to this problem, but none of which I can do without a little help. Here's the situation

The following code loops thru a sales report, using the sales rep ID as a filter so that multiple reports are created. This is now generating a PDF report, but I need to change it to RTF documents.

My question is, since I can't create an RTF using open report, is there a way to use the strFilter with the OutputTo method?

Any ideas appreciated!!!

Private Sub cmdCreateBHPDFs_Click()

On Error GoTo Err_cmdCreateBHPDFs_Click

    
    ' Create Variables for Subroutine
    
    Dim strDocName As String    'Report Name
    Dim db         As Database  'Database to retrieve data from
    Dim strSql     As String    'SQL Statement
    Dim rst        As Recordset 'Data Recordset
    Dim strFilter  As String    'Report Filter


    ' Set Values for Variables
    strDocName = "ReportBHRepTotDetailPDF"
    strSql = "SELECT SALESREP_ID FROM qryCurrentReps ORDER BY SALESREP_ID"
    Set db = CurrentDb
    
    ' Open recordset containing list of Sales Reps
    Set rst = db.OpenRecordset(strSql)
    
    ' Test recordset and act accordingly
    If Not rst.EOF Then
    ' A list of Sales Reps Exists, print the reports
    
        'Advance through the Sales Rep list, one at a time
        
        'Go to first record of recordset
        rst.MoveFirst
        
        'Setup loop to loop through Sales Rep List
        Do Until rst.EOF
        
            'Set the report filter property to the SaleRep in the recordset, zero base array
            strFilter = "SALESREP_ID = '" & rst(0) & "'"
                        
            'Print the report using the report name and filter variables
            DoCmd.OpenReport strDocName, , , strFilter
                   
                              
        'Move to next Sales Rep
        rst.MoveNext
        
        'Loop back to top of Do Until
        Loop
    
    Else
    ' No Sales Reps Exist, inform the user and exit subroutine
        MsgBox "No Sales Data for selected Rep", vbOKOnly, "No Reps"
        Exit Sub
    End If

Exit_cmdCreateBHPDFs_Click:
    Exit Sub

Err_cmdCreateBHPDFs_Click:
    MsgBox Err.Description
    Resume Exit_cmdCreateBHPDFs_Click
    
End Sub

[ADezii]

Hey Gurus - I've seen a few solutions to this problem, but none of which I can do without a little help. Here's the situation

The following code loops thru a sales report, using the sales rep ID as a filter so that multiple reports are created. This is now generating a PDF report, but I need to change it to RTF documents.

My question is, since I can't create an RTF using open report, is there a way to use the strFilter with the OutputTo method?

Any ideas appreciated!!!

Private Sub cmdCreateBHPDFs_Click()

On Error GoTo Err_cmdCreateBHPDFs_Click

    
    ' Create Variables for Subroutine
    
    Dim strDocName As String    'Report Name
    Dim db         As Database  'Database to retrieve data from
    Dim strSql     As String    'SQL Statement
    Dim rst        As Recordset 'Data Recordset
    Dim strFilter  As String    'Report Filter


    ' Set Values for Variables
    strDocName = "ReportBHRepTotDetailPDF"
    strSql = "SELECT SALESREP_ID FROM qryCurrentReps ORDER BY SALESREP_ID"
    Set db = CurrentDb
    
    ' Open recordset containing list of Sales Reps
    Set rst = db.OpenRecordset(strSql)
    
    ' Test recordset and act accordingly
    If Not rst.EOF Then
    ' A list of Sales Reps Exists, print the reports
    
        'Advance through the Sales Rep list, one at a time
        
        'Go to first record of recordset
        rst.MoveFirst
        
        'Setup loop to loop through Sales Rep List
        Do Until rst.EOF
        
            'Set the report filter property to the SaleRep in the recordset, zero base array
            strFilter = "SALESREP_ID = '" & rst(0) & "'"
                        
            'Print the report using the report name and filter variables
            DoCmd.OpenReport strDocName, , , strFilter
                   
                              
        'Move to next Sales Rep
        rst.MoveNext
        
        'Loop back to top of Do Until
        Loop
    
    Else
    ' No Sales Reps Exist, inform the user and exit subroutine
        MsgBox "No Sales Data for selected Rep", vbOKOnly, "No Reps"
        Exit Sub
    End If

Exit_cmdCreateBHPDFs_Click:
    Exit Sub

Err_cmdCreateBHPDFs_Click:
    MsgBox Err.Description
    Resume Exit_cmdCreateBHPDFs_Click
    
End Sub

I haven't actually tried all of it but this should work:
1) Open the Base report in Design View/Hidden
DoCmd.OpenRepor t "Report1", acViewDesign, , , acHidden

2) Modify the RecordSource property of the Report to reflect the
appropriate Filter
Reports![Report1].RecordSource = "YourFilter "

3) Close the Report
DoCmd.Close acReport, "Report1", acSaveYes

4) Now you can use the OutputTo Method to generate the Report in RTF
format. I'm not sure about repeating this process within a Loop
DoCmd.OutputTo .......

5) Repeat Filtering process within the Loop

Hope this somehow helps.

[jpcyr]

Slight modifications

This worked for me but I had to slight mods:

- Instead of using RecordSource, I used Filter.
- Report Definition defaults to 'Apply Filter On Load'=No. Once I changed this to Yes, it worked like a charm.