<div>
Its been a while since I've done this, but the last time I did this, I think I did something like: <pre><code class="code-10 nolinks" id="code-20-43067622-1">docmd.openreport "reportname", acviewpreview
Dim rpt as report
set rpt = reports("reportname")
dim rs as dao.recordset
set rs = currentdb.openrecordset("SELECT distinct InvoiceNum, Client, ClientEmail FROM qry_rptName")
while not rs.eof
&nbsp; &nbsp; rpt.filter = "InvoiceNum = " &amp; rs!InvoiceNum
&nbsp; &nbsp; rpt.filterOn = true
&nbsp; &nbsp; rpt.Caption = Format(Date, "yyyy-mm-dd") &amp; "_Invoice #" &amp; rs!InvoiceNum &amp; "_" &amp; rs!Client
 strFilename = strDefaultPath &amp; "\" &amp; rpt.caption &amp; ".rtf"
&nbsp; &nbsp; docmd.outputto acOutputReport, rpt.name, acformatrtf, strfilename
&nbsp; &nbsp; rs.movenext
Wend
</code></pre> 
</div>

<div>
ok. &nbsp;So in your example is qry_rptName the record source for reportname?
</div>

<div>
in your case,since your InvoiceNumbers are not numeric, this: <pre><code class="code-1 nolinks" id="code-20-43067753-1">rpt.filter = "InvoiceNum = " &amp; rs!InvoiceNum
</code></pre>might need to be changed to: <pre><code class="code-1 nolinks" id="code-20-43067753-2">rpt.filter = "InvoiceNum = " &amp; chr$(34) &amp; rs!InvoiceNum &amp; chr$(34)
</code></pre> 
</div>

<div>
I have also seen this done by opening the recordset first, then looping through the recordset and opening the report with a WHERE argument for each invoice, followed by the Output method, but I found this slower than opening the report once with all of the invoices and then filtering for each invoice. HTH Dale
</div>

<div>
OK, partial success. &nbsp;My trial data set has 23 records, and this produces 23 RTFs in the correct folder, with correct name. However, all 23 RTFs have all 23 records. &nbsp;It is not filtering on INVNUM. &nbsp;Does the report require INVNUM field to be in a particular place? 
 
Private Sub Command0_Click() 
DoCmd.OpenReport &quot;Report1&quot;, acViewPreview 
Dim rpt As Report 
Set rpt = Reports(&quot;Report1&quot;) 
 
Dim rs As DAO.Recordset 
Dim strfilename As String 
 
Set rs = CurrentDb.OpenRecordset(&quot;SELECT distinct BillingName, INVNUM, Customer from rpt_q1&quot;) 
While Not rs.EOF 
&nbsp; &nbsp; rpt.Filter = &quot;INVNUM = &quot;&nbsp;&amp; rs!INVNUM 
&nbsp; &nbsp; rpt.FilterOn = True 
&nbsp; &nbsp; rpt.Caption = rs!Customer &amp;&nbsp;&quot;-&quot; &amp;&nbsp;rs!BillingName &amp;&nbsp;&quot;-&quot; &amp;&nbsp;rs!INVNUM 
&nbsp; &nbsp; strfilename = &quot;C:\LocalDB\Rpt\&quot; &amp;&nbsp;rpt.Caption &amp;&nbsp;&quot;.rtf&quot; 
&nbsp; &nbsp; DoCmd.OutputTo acOutputReport, rpt.Name, acFormatRTF, strfilename 
&nbsp; &nbsp; rs.MoveNext 
Wend 
 
End Sub
</div>

<div>
tried this too 
&nbsp; &nbsp; rpt.Filter = &quot;INVNUM=&quot; &amp;&nbsp;Chr$(34) &amp;&nbsp;rs!INVNUM &amp;&nbsp;Chr$(34)
</div>

<div>
OK. &nbsp;It works if I put an interrupt between turning the filter on and outputting the report: 
 
Private Sub Command0_Click() 
DoCmd.OpenReport &quot;Report1&quot;, acViewPreview 
&nbsp; &nbsp; Dim rpt As Report 
&nbsp; &nbsp; Set rpt = Reports(&quot;Report1&quot;) 
&nbsp; &nbsp; 
&nbsp; &nbsp; Dim rs As DAO.Recordset 
&nbsp; &nbsp; Dim strfilename As String 
&nbsp; &nbsp; 
&nbsp; &nbsp; Set rs = CurrentDb.OpenRecordset(&quot;SELECT distinct INVNUM, BillingName, Customer from rpt_q1&quot;) 
&nbsp; &nbsp; 
&nbsp; &nbsp; While Not rs.EOF 
&nbsp; &nbsp; 
&nbsp; &nbsp; rpt.Filter = &quot;INVNUM=&quot; &amp;&nbsp;Chr$(34) &amp;&nbsp;rs!INVNUM &amp;&nbsp;Chr$(34) 
&nbsp; &nbsp; rpt.FilterOn = True 
&nbsp; &nbsp; 
 &nbsp; &nbsp;MsgBox (&quot;Create Invoice: &quot;&nbsp;&amp; rs!INVNUM) 
&nbsp; &nbsp; &nbsp; 
&nbsp; &nbsp; rpt.Caption = rs!Customer &amp;&nbsp;&quot;-&quot; &amp;&nbsp;rs!BillingName &amp;&nbsp;&quot;-&quot; &amp;&nbsp;rs!INVNUM 
&nbsp; &nbsp; strfilename = &quot;C:\LocalDB\Rpt\&quot; &amp;&nbsp;rpt.Caption &amp;&nbsp;&quot;.rtf&quot; 
&nbsp; &nbsp; DoCmd.OutputTo acOutputReport, rpt.Name, acFormatRTF, strfilename 
&nbsp; &nbsp; rs.MoveNext 
Wend 
 
End Sub
</div>

hgj1357

<div>
If it works with the message box, you might just be able to insert a pause of a second or two instead of the msgbox. 
</div>

<div>
Hello HG1357, 
 
You might find this code useful. 
The code spins through data that contains order numbers and invoice numbers. The order numbers points to invoice numbers. The invoice report is triggered but remains hidden and outputs to a PDF file. I do not want a printed copy. After each invoice is outputted a routine is called which emails a recipient (email address in my input file). The PDF is sent as an attachment. 
Of course this same concept can be used to email any kind of output file. 
 
Dave 
 
sub SendInvoices &nbsp; 
&nbsp; 
&nbsp; &nbsp;Set db = CurrentDb 
&nbsp; &nbsp;rem dataset having the list of order numbers to print 
&nbsp; &nbsp;Set Myquery = db.QueryDefs(&quot;qryorders) 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 
&nbsp; &nbsp;Set SV = Myquery.OpenRecordset(DB_OPEN_DYNASET) 
&nbsp; &nbsp; &nbsp; Do 
&nbsp; &nbsp; &nbsp; &nbsp; If SV.EOF Then 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Exit Do 
&nbsp; &nbsp; &nbsp; &nbsp; End If 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 
&nbsp; &nbsp; &nbsp; &nbsp; rem order number used to find invoice number 
&nbsp; &nbsp; &nbsp; &nbsp; w1$ = &quot;OrderNumber=&quot; &amp;&nbsp;&quot;'&quot; &amp;&nbsp;SV!OrderNumber &amp;&nbsp;&quot;'&quot; 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
&nbsp; &nbsp; &nbsp; &nbsp; rem invoice print 
&nbsp; &nbsp; &nbsp; &nbsp; DoCmd.OpenReport &quot;rptOrderEntryMaster_Invoice&quot;, acViewPreview, , w1$, acHidden 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
&nbsp; &nbsp; &nbsp; &nbsp; rem output invoice to a file - using PDF 
&nbsp; &nbsp; &nbsp; &nbsp; DoCmd.OutputTo acOutputReport, &quot;rptorderentrymaster_Invoice&quot;, acFormatPDF, &quot;C:\output_folder\&quot; &amp;&nbsp;&amp; LTrim$(RTrim$(SV!InvoiceNumber)) &amp;&nbsp;&quot;.PDF&quot; 
&nbsp; &nbsp; &nbsp; &nbsp; DoCmd.Close acReport, &quot;rptorderentrymaster_Invoice&quot; 
&nbsp; &nbsp; &nbsp; &nbsp; 
&nbsp; &nbsp; &nbsp; &nbsp; rem call routine to send file as attachment &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
&nbsp; &nbsp; &nbsp; &nbsp; Call Send_CDO_Mail_Invoice_By_Outlook(Email$, SV!PurchaseOrderNumber, SV!InvoiceNumber) 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 
MoveNext: 
&nbsp; &nbsp; &nbsp; &nbsp; rem delete output file 
&nbsp; &nbsp; &nbsp; &nbsp; Kill &quot;C:\output_folder\&quot; &amp;&nbsp;&amp; LTrim$(RTrim$(SV!InvoiceNumber)) &amp;&nbsp;&quot;.PDF&quot; 
Bypass: 
&nbsp; &nbsp; &nbsp; &nbsp; SV.MoveNext 
&nbsp; &nbsp; &nbsp; Loop 
&nbsp; &nbsp; &nbsp; SV.Close 
&nbsp; &nbsp; &nbsp; Set SV = Nothing 
&nbsp; &nbsp; &nbsp; Sending.Visible = False 
&nbsp; &nbsp; &nbsp; MsgBox (&quot;Number of Invoices Emailed: &quot;&nbsp;&amp; EmailCount) 
End Sub 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
Sub Send_CDO_Mail_Invoice_By_Outlook(Email$, PurchaseOrderNumber$, Invoice$) 
&nbsp; &nbsp; &nbsp; Dim olkApp As Outlook.Application, olkNS As Outlook.NameSpace, olkFolder As Outlook.MAPIFolder, olkMessage As Outlook.MailItem, strSubject As String, strBody As String 
&nbsp; &nbsp; &nbsp; On Error GoTo BadEmail 
&nbsp; &nbsp; &nbsp; Attachment1 = &quot;C:\access2002.db\&quot; &amp;&nbsp;&quot;METROV&quot; &amp;&nbsp;LTrim$(RTrim$(Invoice$)) &amp;&nbsp;&quot;.PDF&quot; 
&nbsp; &nbsp; &nbsp; ErrorSwitch = 0 
&nbsp; &nbsp; &nbsp; Set objApp = CreateObject(&quot;Outlook.Application&quot;) 
&nbsp; &nbsp; &nbsp; Dim outobj, mailobj 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; 
TryAgain: 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Set outobj = CreateObject(&quot;Outlook.Application&quot;) 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Set mailobj = outobj.CreateItem(0) 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 
 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; With mailobj 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .Sender = &quot;invoicing@metrovac.com&quot; 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .To = Email$ 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .Subject = &quot;Metropolitan Vacuum Cleaner Company - Invoice &quot;&nbsp;&amp; Invoice$ 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;GoSub Get_Text 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .Body = message$ 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .Attachments.Add Attachment1 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .Send 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; End With 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Set outobj = Nothing 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Set mailobj = Nothing 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Exit Sub 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
BadEmail: 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Select Case ErrorSwitch 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Case 0 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ErrorSwitch = 1 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Email$ = InputBox(&quot;Fix Email Address: &quot;&nbsp;&amp; &quot;&nbsp;For Invoice &quot;&nbsp;&amp; Invoice$ &amp;&nbsp;&quot; &quot;, , Email$) 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;GoTo TryAgain 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Case Else 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;MsgBox (&quot; Can't Transmit Hold For Manual Processing:&quot; &amp;&nbsp;Invoice$) 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;End Select 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Exit Sub 
 
End Sub
</div>

<div>
I think Dale Fye's solution will work, but I have to try a few other things before I can confirm. &nbsp;Looks good for now.
</div>

<div>
<div class="content wysiwyg-content">
I have table of invoice numbers like this: 
 
BillingInvNum 
19-563-00-3 
19-570-00-2 
19-575-00-5 
20-511-00-1 
... 
I have a report that can filter on any of these invoice numbers and print out said invoice. &nbsp;I'd like to be able to Output to an RTF file for each invoice number. &nbsp;ie.19-563-00-3.rtf; 19-570-00-2.rtf etc. 
 
So essentially I need to cycle through the entire (approx 56) invoice numbers and for each one use 
 
DoCmd.OutputTo acOutputReport, &quot;Invoice Complete duplicates&quot;, acFormatRTF, &quot;XXX.rtf&quot; 
 
where XXX is the relevant invoice number from the table. 
 
Thanks
</div>
</div>

I have table of invoice numbers like this:

BillingInvNum
19-563-00-3
19-570-00-2
19-575-00-5
20-511-00-1
...
I have a report that can filter on any of these invoice numbers and print out said invoice.  I'd like to be able to Output to an RTF file for each invoice number.  ie.19-563-00-3.rtf; 19-570-00-2.rtf etc.

So essentially I need to cycle through the entire (approx 56) invoice numbers and for each one use

DoCmd.OutputTo acOutputReport, "Invoice Complete duplicates", acFormatRTF, "XXX.rtf"

where XXX is the relevant invoice number from the table.

Thanks

MS Access acoutput reports based on table data

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.