Report Filter Guru, Please Help!

[TheNovice]

Good Day All,

I have a report that I created and am sending it out using an [Event
Procedure].

The problem that I am having is that when I use "Filter" for just one
criteria, it works great!, adding the second critera is more of a Challenge,
but Probably easier and less complicated.

This works great by itself,
"EmailTO = " & Chr(34) & stEmailTo & Chr(34)

but I need a second criteria

WorkOrder = strWorkOrd (they are both intergers, if that helps)

 

[Marshall Barton]

I have a report that I created and am sending it out using an [Event
Procedure].

The problem that I am having is that when I use "Filter" for just one
criteria, it works great!, adding the second critera is more of a Challenge,
but Probably easier and less complicated.

This works great by itself,
"EmailTO = " & Chr(34) & stEmailTo & Chr(34)

but I need a second criteria

WorkOrder = strWorkOrd (they are both intergers, if that helps)

rpt.Filter = "EmailTO = " & Chr(34) & stEmailTo & Chr(34) _
& " And WorkOrder = " & strWorkOrd

 

[TheNovice]

Marshall,

that seems to work, but now I am getting a run-time error '2467': The
Expression you entered refers to an object that is closed or doesnt exist

here is the code in its entirety:

Private Sub Command16_Click()
' This is used to send Repair Work Orders
Me.Dirty = False
DoCmd.SetWarnings No
DoCmd.OpenQuery "qryEmailCorrections"
'Sub createsnapshots()
Dim db As Database
Set db = CurrentDb
Dim rc As Recordset
Dim rpt As Report
Dim strWOR As String
Dim stTOName As String
'Dim stCCName As String
Dim stDate As String
Dim stsubjLine As String
Dim stbody As String
Dim stDocName As String
Dim stWorkOrd As Integer
Dim stWorkArea As String

DoCmd.OpenReport "rptEmailCorrectionsrpt", acViewDesign
Set rpt = Reports![rptEmailCorrectionsrpt]
rpt.Visible = False
DoCmd.SetWarnings No
Set rc = db.OpenRecordset("tblEmailCorrections")
If Not (rc.BOF And rc.EOF) Then
rc.MoveFirst
Do Until rc.EOF
stTOName = rc!EmailTo
'stCCName = rc![Created By]
stWorkArea = rc!Description
stDate = rc!Date
strWOR = rc!WorkOrder
stWorkOrd = rc!WorkOrder
stDocName = "rptEmailCorrectionsrpt"
stsubjLine = "Work Order Number " + strWOR
stbody = "Please review the Work Order Assigned to you"
rpt.Filter = "stWorkArea=" & Chr(34) & Description & Chr(34)
BuildSnapShot2 stTOName, stDate, stDocName, stsubjLine,
stbody, rpt
rc.MoveNext
Loop
End If
DoCmd.Close acReport, "rptEmailCorrectionsrpt", acSaveNo

Exit Sub
'End Sub
'DoCmd.OpenQuery "qryclrwotable"
'DoCmd.OpenQuery "qryClrFormEntry"
'DoCmd.Close acForm, "frmFormReview"
End Sub
---------------------------------------------------------------------------------

Sub BuildSnapShot2(stTOName As String, stDate As String, stDocName As
String, stsubjLine As String, stbody As String, rpt As Report)
'rpt.Filter = "Emailto = " & Chr(34) & stTOName & Chr(34)
DoCmd.SendObject acSendReport, stDocName, "SnapShot Format", stTOName, ,
, stsubjLine, stbody, False

Exit Sub
End Sub

 

[Marshall Barton]

What is that code supposed to be doing???

Regardless of what you had in mind, you can not assign
control values in design view. In fact it's a really,
really bad idea to open any report (or form) in design view
in a running program.

Even if you meant to open the report in preview mode, you
can not "push" values into a report. However, reports can
"pull" values from all kinds of places.

I think you need an entirely new approach to whatever it is
you are trying to do.

 

[krissco]

Even if you meant to open the report in preview mode, you

can not "push" values into a report. However, reports can
"pull" values from all kinds of places.

Maybe it's just semantics, but I disagree. You can load your filter
string with all sorts of information you wish to "push" into a report
and parse it as needed. One way I have used this in the past is by
appending an expression that always evaluates as TRUE to the filter
string. . . Ok. That's confusing. Here is what I mean:

strFilter = "somefieldName = someLegitimateFilter and 'Foo' = 'Foo'"

docmd.openreport blah, blah, blah, blah, strFilter

.. . .

Then in the report:

if me.Filter like "*Foo*" then . . .

Thats what I mean by "pushing" information into a report.


-Kris

 

[Marshall Barton]

Even if you meant to open the report in preview mode, you
can not "push" values into a report. However, reports can
"pull" values from all kinds of places.

Maybe it's just semantics, but I disagree. You can load your filter
string with all sorts of information you wish to "push" into a report
and parse it as needed. One way I have used this in the past is by
appending an expression that always evaluates as TRUE to the filter
string. . . Ok. That's confusing. Here is what I mean:

strFilter = "somefieldName = someLegitimateFilter and 'Foo' = 'Foo'"

docmd.openreport blah, blah, blah, blah, strFilter []
Then in the report:
if me.Filter like "*Foo*" then . . .

Thats what I mean by "pushing" information into a report.

You are probably familiar with most of this, but I thought a
quick review might help others follow this discussion.

I think most folks call that "passing" arguments to a
form/report. The report then takes the passed values and
sets its own properties. With passed arguments, the report
does not care who called it and the caller does not need to
"know" anything about the report. Since A2K, the standard
way to pass an argument string to a report is to use the
OpenReport method's OpenArgs argument.

"Pushing" is when the caller sets the report's properties
directly from VBA code outside the report. If this were
effective, it would alleviate the need for the report to
know who called it, but the calling code would be required
to know a lot about the report.

"Pulling" is when the report retrieves values directly from
the caller and sets its own properties. In this case the
report must know who called it and about how the caller
arranged the values.

A "common/global area" is a publicly available storage space
where the caller sets the values and the report retrieves
them. This technique is kind of a neutralized push and
pull. It avoids the problems of pushing and pulling, but
there are other serious issues with using common storage.

 

[krissco]

You are probably familiar with most of this, but I thought a

quick review might help others follow this discussion.

I think most folks call that "passing" arguments to a
form/report. The report then takes the passed values and
sets its own properties. With passed arguments, the report
does not care who called it and the caller does not need to
"know" anything about the report. Since A2K, the standard
way to pass an argument string to a report is to use the
OpenReport method's OpenArgs argument.

"Pushing" is when the caller sets the report's properties
directly from VBA code outside the report. If this were
effective, it would alleviate the need for the report to
know who called it, but the calling code would be required
to know a lot about the report.

"Pulling" is when the report retrieves values directly from
the caller and sets its own properties. In this case the
report must know who called it and about how the caller
arranged the values.

A "common/global area" is a publicly available storage space
where the caller sets the values and the report retrieves
them. This technique is kind of a neutralized push and
pull. It avoids the problems of pushing and pulling, but
there are other serious issues with using common storage.

Good points all. I suppose it is more of a pass than a push.

One correction - I'm on A2K - no OpenArgs for DoCmd.OpenReport() -
must have been added later.

-Kris

 

[Marshall Barton]

Good points all. I suppose it is more of a pass than a push.

One correction - I'm on A2K - no OpenArgs for DoCmd.OpenReport() -
must have been added later.

Sorry, that should have been A2K2.