Filter sub report on open

[DaveyEdgar]

Greetings all!

I have a main report with a sub report. The sub report is linked to the main report. The sub report contains a "violations" control/field. I'd like that sub report to only display records that contain 'callout' in the "violations" control/field.

The underlying query for the subreport underlies other forms, so I can't change the query itself. I really don't want to create yet another query, so I'm hoping there is an easy way to filter that subreport.

I tried this in the main report...

Private Sub Report_Open(Cancel As Integer)
Reports![rptrona]![rptRONAsubdis].Form.RecordSource = "Select * from [qrydiscipline] WHERE [violation] = 'callout' "
End Sub

...but I get an error saying I used an invalid reference or something.

Is there an easier way?

 

[DaveyEdgar]

Oh I forgot to mention. I need to remove that filter when the report closes.

 

[VicRauch]

If the query that is currently in the RecordSource for the subreport can not be changed because it is used in other places, is this subreport used in other places? If not, then just use SQL for the RecordSource.

But, if you have to stay with this situation just as you presented it, then create the query you want to use, like the SQL you show in your question, then switch to SQL view, copy the SQL code, and then paste that into your code you've shown above.

When you close the report (and subreport), if you don't say to "Save" the objects, you will not have to restore the pre-existing contents of RecordSource.

 

[DaveyEdgar]

Ah but of coarse! Thanks that did it. I must have been braincramping today that never occured to me.

Thanks again for the help!