Passing Looking Up Filters from Form to Report

[Guest]

I have a form which lists studies. People can filter the form based on
details about the study, such as the study type. Currently users filter via
the right click method.

I would like to be able to have a report that shows just the filtered records.

The code that I tried which didn't work:

Private Sub print_detail_Click()
On Error GoTo Err_print_detail_Click
DoCmd.Minimize
Dim stDocName As String
stDocName = "RptStudyInformationDetail"
'Debug.Print Me.Filter
'Debug.Print Me.RecordSource
DoCmd.openreport stDocName, acViewPreview, , Me.Filter, , Me.RecordSource
Reports!RptStudyInformationDetail.FilterOn = True


Exit_print_detail_Click:
Exit Sub

Err_print_detail_Click:
MsgBox Err.Description
Resume Exit_print_detail_Click

End Sub

The Debug Window read the following for Me.Recordsource:
SELECT TblMainStudyInformation.* FROM TblMainStudyInformation INNER JOIN
[TblChoicesNames] ON TblMainStudyInformation.[Study Director] =
[TblChoicesNames].NameCode WHERE ((([last name] & ',' & [first initial] &
[middle initial])='Smith,JM'));

And the following for Me.Filter:
((([Lookup_Test System].[Test System]="SYSTEMTYPE1"))) AND
((Lookup_Sponsor.Initials="RWW"))

Anybody have any idea how to make this work?

Thanks,
Beth

 

[Guest]

Hi Beth,

I see two problems:

The first is that the syntax is wrong. The statement as shown in Help is:

DoCmd.OpenReport reportname[, view][, filtername][, wherecondition]


You have too many arguments and in the wrong order. Since you want to use a
filter, it should look like:


DoCmd.openreport stDocName, acViewPreview, Me.Filter


You cannot use the recordsource like you tried. You can set the report
record source to a SQL string or a saved query by using

Me.RecordSource = ......

Or you can set the report record source in the report design view.



The second thing is that the tables/queries and fields in the filter are not
in the recordsource. You can filter only on fields that are in the
reocrdsource.


HTH