Using VBA export reports to rtf files with criteria

Hello,

I'm trying to export a report to an various rtf files depending on criteria.  The code currently loops through a record set and will export the file but how do I pass it crieria to limit the record set.
With CurrentDb.OpenRecordset("Select Distinct Department from new;", dbOpenForwardOnly)
    While Not .EOF      
        DoCmd.OutputTo acOutputReport, "testreport", acFormatRTF, "C:\exports\" & !Department
   
        .MoveNext
    Wend
.Close
End With

Open in new window

LVL 3
eshurakAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
peter57rConnect With a Mentor Commented:
You can't easily.
OutputTo doesn't use filters or Where clauses.

You willhave to find some way of setting the report's recordsource based on the department.
Maybe setting the report's query sql to a value that includes the dapartment criteria.


strsql = " Select * from table where dpartment = '" &  rs!department & "'"
currentdb.querydefs("reportqueryname").sql = strsql
docmd.outputto ......
0
 
eshurakAuthor Commented:
Yeah, I just wrote the code below using a similer concept.  It works, but it's ugly.
Dim sql As String
Dim qd As QueryDef
 
Set qd = CurrentDb.QueryDefs("ReportData")
 
With CurrentDb.OpenRecordset("Select Distinct Department from new;", dbOpenForwardOnly)
    While Not .EOF
    
    sql = "SELECT * FROM new WHERE (((new.Department)='" & !Department & "'));"
    qd.sql = sql
    
    
    DoCmd.OutputTo acOutputReport, "testreport", acFormatRTF, "C:\rtf\" & !Department & ".rtf"
    
        .MoveNext
    Wend
.Close
End With

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.