Solved

Using VBA export reports to rtf files with criteria

Posted on 2008-10-02
2
1,508 Views
Last Modified: 2013-11-27
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

0
Comment
Question by:eshurak
2 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 22626870
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
 
LVL 3

Author Comment

by:eshurak
ID: 22627034
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

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now