RE: Passing Looking Up Filters from Form to Report

Tech-Archive recommends: Fix windows errors by optimizing your registry

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
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"Beth G" wrote:

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
.

Relevant Pages

  • Re: OpenReport from form Problem
    ... Okay I tried to filter by openreport. ... Private Sub cmdAllCountiesSelectedIssues_Click ... > the report itself. ... > OpenReport action to change the report's RecordSource. ...
    (microsoft.public.access.formscoding)
  • RE: 1 Report, Multiple Queries
    ... On Error GoTo Err_cmdOpenProjectReport_Click ... Dim stDocName As String ... the valid filter entries for strMachineType. ... I have a report named ...
    (microsoft.public.access.reports)
  • Re: Setting Reports RecordSource
    ... Add a command button on your form with code like: ... Clicking the button produces no filter. ... The command button is in the header of the report. ... I can't change the recordsource while the report is open. ...
    (microsoft.public.access.reports)
  • Re: Filtering an ADP Report - Access 2000
    ... There are in Access 2000 very few ways to filter a report based on a sproc ... > - Keeping my recordsource as a stored procedure, I tried a server filter. ...
    (microsoft.public.access.reports)
  • RE: Continuous form preview button
    ... The 3rd argument is for using a query to filter the report. ... Private Sub Job_Details_BTn_Click ... Dim stDocName As String ...
    (microsoft.public.access.forms)