SubReport Filter Problem 1

[bcw]

I'm having a problem filtering a subreport. The subreport is not connected to the main report (by that I mean the link child and parent fields are empty). If I set the Filter property of the subreport in the properties box the filter runs fine. The problem is I need to filter at runtime so I'm trying to apply the filter in VBA. I've tried to reference the Filter and FilterOn properties several ways with no luck. I'm not sure if my syntax is off or if the Filter property is not available for a subreport. Here is what I've tried so far.

1. This works if the report is opened by itself but give an error of "The setting you selected isn't valid for this property" and debugs to the sub's open event below.
subreports open event
me.filteron = true
me.filter = "Date > #11/15/01#"
end

2. I've tried to reference the sub filter from the open event of the main report with similiar errors using the syntax,
me.(subreportname).Form.FilterOn = True

Any help will be greatly appreciated!!! Thanks

 

[JerryDennison]

You cannot change the subreport's filter property at runtime(by this I mean when the report is run). You'll need to open the subreport, change it's filter property, close and save the subreport, then run the main report that includes the subreport. This can be done in code and happens sufficiently fast enough that it is {barely} noticeable.

 

[bcw]

Thanks Jerry...I was starting to think that if couldn't be done at runtime.

Everything worked great. It is pretty fast (barely see the screen flash when it changes the subreport).

Thanks, you saved me a lot of headache.

Brian

 

[JerryDennison]

You're welcome.

 

[JohnHarkins]

Could you share the code you use to set the filter, etc.?
Thanks, John Harkins

 

[JerryDennison]

Any property can be set using the following syntax:

ObjectName.Property = {Expression}

Simply insure that you use the full path to the Object. For example, if the property is a Form property and the form is open you can use Me. Or you can use the name of the form, FormName.PropertyName. "Advice is a dangerous gift, even from the wise to the wise, for all course may run ill." J.R.R. Tolkien

 

[stable]

Hi there,
I've used the suggested solution for my subreport. I can set the filterOn property & set up the filter ok (I've checked this using debug print), but unfortunately I can't seem to save these changes in the subreport (therefore when I open the main report the subreport is not valid). Below is the code for the close & save:

DoCmd.Close acReport, strSubReport, acSaveYes

where strSubReport is the name of the report object. The report closes ok.
I have previewed the subreport before it is closed and the filter works so there is no problem there.

Any help will be much appreciated!!

 

[Elbats]

Hi there,

The acSaveYes in
DoCmd.Close acReport, strSubReport, acSaveYes

does not appear to save the subreport.

I've now separated this to a save then close & it works fine.

Cheers

 

[ptiernan]

Hi I have the same problem, the

DoCmd.Close acReport, strSubReport, acSaveYes

dose not appear to save. I have tried to seperate the save and close out as suggested but no joy.

DoCmd.Save acReport, strSubReport
DoCmd.Close acReport, strSubReport, acSaveYes

 

[stable]

Hi ptiernan,

I open the report in an event procedure for a command button on a form:

' Open, save and close filtered subreport. This is necessary to set the filter in the
' Sub report, as it is not possible to set the sub report filter during run time.
DoCmd.OpenReport strSubReport, acPreview, , strFilterSub
DoCmd.Save
DoCmd.Close

I think once the report is opened it has the focus and therefore the save & close does not require the report name.

Cheers