Solved

Using VBA export reports to rtf files with criteria

Posted on 2008-10-02
2
1,491 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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

758 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

22 Experts available now in Live!

Get 1:1 Help Now