Create Multiple RTF Reports using strFilter

[thall29323]

Hello to all -

I have a sales report that uses a strFilter (SalesRep ID) to produce individual reports in PDF. I need to change this to produce RTF reports and save the file using the strFilter as part of the name. My problem is, the openReport does not allow to save as file and the OutputTo does not allow Filters. How do I go about this?

Any and all suggestions appreciated!!!!!

Here's the current code:

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

 

[brettatexplace]

I have been able to open the report with the filter and, if still open then E-mail it like below, without the report regenerating without the filter... sort of like this...

strTitleLine = [lngID] & " Is my person TILTE"
' in the on_open of the rptGOData, set this as the title
stFilter = "[lngId]='" & Me.lngId
stDocName = "rptGOData"
DoCmd.OpenReport stDocName, acPreview, , stFilter
DoCmd.Minimize
DoCmd.SendObject acReport, stDocName, "SnapshotFormat(*.snp)", , , "", strTitleLine, , True, ""
DoCmd.Close acReport, stDocName
' go to next

but this is a snapshot output, but should work the same