WHERE clause for SendObject

[Guest]

I'm just looking for a clever way to pass a WHERE clause to a report that I
am sending via SendObject.

Real-world: I want to e-mail statements. I have already written the
recordset loop that goes through all the customers that have e-mail addresses
and builds a WHERE clause (based on selection criteria on a form). However, I
thought I was just going to be able to pass that along to the report in my
SendObject acSendReport..., I realized that I had made a rather shortsighted
assumption here.

So, other than setting the report's RecordSource as a query and rebulding
the query's SQL on the fly, is there any way to pass either an argument or
WHERE clause to the report?

 

[Allen Browne]

Use a public string variable to hold the filter.
Then use the Open event of the report to apply and reset the filter string.

Steps:
====
1. In a standard module (i.e. one you created from the Modules tab of the
Database Container), declare the public string:
Public gstrReportFilter As String

2. In the code that uses SendObject, assign the filter string:
gstrReportFilter = "ClientID = 99:
DoCmd.SendObject ...

3. In the Open event of the report, apply and reset the filter:
If gstrReportFilter <> vbNullString Then
Me.Filter = gstrReportFilter
Me.FilterOn = True
gstrReportFilter = vbNullstring
End If

 

[Guest]

Thanks, Allen.

That sounds simpler than my solution of setting the report's RecordSource as
a query and re-writing the query's SQL before opening the report.

CurrentDB.QueryDefs("<QueryName>").SQL = "<SQLStatement>"

I think I will rewrite it your way.