Filter Limitations with SendObject SendReport? 1

[MarkCRob]

Used to print a report of all invoices - Now the client wants selected invoices to be send email.

I can DoCmd.OpenReport "Invoices" with a WHERE ID=123 ad print a single invoice

but there doesn't seem to be a WHERE option with the
DoCmd.SendObject acSendReport, "Invoices"

So I figured I would apply a filter to the report

Reports!Invoices.Filter = "ID=123"

... not having much luck. Seems to want the report to be open in order to change the filter...

Basically I want to create a loop that sets a filter, sends a page of the report to outlook. Suggestions?

 

[dhookom]

You might be able to use a small table of IDs and store only the ID or IDs that you want to print. Add this table to your report's record source query and join the ID fields. If you want to print for ID 123, have only a single record in the small table with a value of 123.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Public Sub filterSend()
  Dim rsInv As DAO.Recordset
  Dim fltStr As String
  Dim rpt As Access.Report
  Set rsInv = CurrentDb.OpenRecordset("Select OrderID from qryInvoice")
  DoCmd.OpenReport "rptInvoice", acViewPreview, , , acHidden
  Set rpt = Reports("rptInvoice")
  
  Do While Not rsInv.EOF
    fltStr = "OrderID = " & rsInv.fields("OrderID")
    rpt.Filter = fltStr
    rpt.FilterOn = True
    'put your code here to send object
    rsInv.MoveNext
  Loop
  
End Sub