bulletboy
asked on
Subreport Open Event - Modifying Report Filter
Experts,
I have several reports that have a common query as their recordsource. On each report I filter the results programmatically using an expression similar to
Me.Filter = "[scheduledin] <= #" & dNextSunday & "#"
in the report's Open event.
These reports are ALSO subreports on an unbound parent report separated by page break controls for the purpose of printing all reports at once to the printer with stapling and hold punching.
My problem is that the parent report seems to be triggering the subreport open events. This causes an error because Me.Filter doesn't make sense to the parent since it's unbound.
How do I filter at runtime (because it's calculated based on the date) the subreports and have it also work when the subreport is being run alone?
Is there a way to know if it's running as a subreport that can be used in a conditional expression?
Thank you.
I have several reports that have a common query as their recordsource. On each report I filter the results programmatically using an expression similar to
Me.Filter = "[scheduledin] <= #" & dNextSunday & "#"
in the report's Open event.
These reports are ALSO subreports on an unbound parent report separated by page break controls for the purpose of printing all reports at once to the printer with stapling and hold punching.
My problem is that the parent report seems to be triggering the subreport open events. This causes an error because Me.Filter doesn't make sense to the parent since it's unbound.
How do I filter at runtime (because it's calculated based on the date) the subreports and have it also work when the subreport is being run alone?
Is there a way to know if it's running as a subreport that can be used in a conditional expression?
Thank you.
You can also try to reference the report by its full name when setting the filter, such as:
Reports![report 1].Filter = "[scheduledin] <= #" & dNextSunday & "#"
Reports![report 1].FilterOn = True
The above code should work if the report is opened either as a subreport or as a main report. Please note that if the report name does not have any spaces in it, you can use:
Reports("report1") or Reports!report1 as the reference.
Joe
Reports![report 1].Filter = "[scheduledin] <= #" & dNextSunday & "#"
Reports![report 1].FilterOn = True
The above code should work if the report is opened either as a subreport or as a main report. Please note that if the report name does not have any spaces in it, you can use:
Reports("report1") or Reports!report1 as the reference.
Joe
ASKER
The error is:
"Run-time error '2101':
The setting you entered isn't valid for this property.
To see the valid settings for this property, search the Help index for the name of the property."
When I click the "Debug" button the highlighted expression is
Me.Filter = "[scheduledin] <= #" & dNextSunday & "#"
When I run the report by itself it works beautifully!! I only get this report when I run the unbound parent report with this as a subreport. This error occurs with the two subreports that have this similar line of code and each of them run perfectly alone.
I tried referencing the report as josocal suggested. No joy.
I appreciate the comments and suggestions.
Brent
"Run-time error '2101':
The setting you entered isn't valid for this property.
To see the valid settings for this property, search the Help index for the name of the property."
When I click the "Debug" button the highlighted expression is
Me.Filter = "[scheduledin] <= #" & dNextSunday & "#"
When I run the report by itself it works beautifully!! I only get this report when I run the unbound parent report with this as a subreport. This error occurs with the two subreports that have this similar line of code and each of them run perfectly alone.
I tried referencing the report as josocal suggested. No joy.
I appreciate the comments and suggestions.
Brent
It could very well be that you can't change the filter on a subreport. I haven't tried, because I found an easier solution.
Replace the filter with a built-in criteria in each report's query:
scheduledin <= Date()-WeekDay(Date())+8
This should solve your problem, assuming dNextSunday holds the date of the next Sunday.
Good luck!
(°v°)
Replace the filter with a built-in criteria in each report's query:
scheduledin <= Date()-WeekDay(Date())+8
This should solve your problem, assuming dNextSunday holds the date of the next Sunday.
Good luck!
(°v°)
If the expression for dNextSunday is more comples, create a function like this:
Function NextSunday()
NextSunday = dNextSunday
End Function
The "hard coded" criteria would then be:
scheduledin <= NextSunday()
Cheers!
(°v°)
Function NextSunday()
NextSunday = dNextSunday
End Function
The "hard coded" criteria would then be:
scheduledin <= NextSunday()
Cheers!
(°v°)
ASKER
Thank you harfang.
The problem is that all of the reports use the same query. I could use a different query for each report but then I'd have a bunch of very similar queries. Maybe I'll just use SQL in the RecordSource property of each report instead of using a stored query. But then if I want to make a change I have to change all of them. I'd like to be able to set the filter instead.
Any ideas? If it can't be done without a complex and inellegant workaround, why not?
Thanks.
The problem is that all of the reports use the same query. I could use a different query for each report but then I'd have a bunch of very similar queries. Maybe I'll just use SQL in the RecordSource property of each report instead of using a stored query. But then if I want to make a change I have to change all of them. I'd like to be able to set the filter instead.
Any ideas? If it can't be done without a complex and inellegant workaround, why not?
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
No, I think you've got the point.
I like your idea about making a "standard" query. It's not as nice as actually setting a filter to essentially use one query with each report having its own WHERE clause but it's okay.
I'll do it tomorrow and let you know how it works (it's gonna work).
Thanks.
By the way, is the (ºvº) a guy with glasses or a chick's ass with those little dimples?
I like your idea about making a "standard" query. It's not as nice as actually setting a filter to essentially use one query with each report having its own WHERE clause but it's okay.
I'll do it tomorrow and let you know how it works (it's gonna work).
Thanks.
By the way, is the (ºvº) a guy with glasses or a chick's ass with those little dimples?
> It's not as nice as actually setting a filter to essentially use one query with each report having its own WHERE clause but it's okay.
I think it is. Where is the difference?
1) RecordSource: SELECT * FROM qYourOneQuery WHERE <criteria>
2) RecordSource: qYourOneQuery + Me.Filter = "<criteria>"
I wasn't saying "create a new standard query", I was using qselStandardQuery to refer to your existing "one query" to rule them all ;)
Anyway, see how it goes...
Good luck!
(°v°)
I think it is. Where is the difference?
1) RecordSource: SELECT * FROM qYourOneQuery WHERE <criteria>
2) RecordSource: qYourOneQuery + Me.Filter = "<criteria>"
I wasn't saying "create a new standard query", I was using qselStandardQuery to refer to your existing "one query" to rule them all ;)
Anyway, see how it goes...
Good luck!
(°v°)
ASKER
Well...since you put it that way...
I think you're right. Your solution is cleaner than what I was trying to do.
Thank you so much.
I think you're right. Your solution is cleaner than what I was trying to do.
Thank you so much.
Thank you, and good luck with your reports!
(°v°)
(°v°)
ASKER
It worked (we knew it would). Thank you again.
I'm glad for you. It's always good to skip these VB adjustments if they are not really needed :)
Success with your project!
(°v°)
Success with your project!
(°v°)
When code is run from a report, "Me" refers to the actual report, not the parent. So this is not the problem. What is the error message?
> Is there a way to know if it's running as a subreport
Use:
If AmISubreport(Me) Then
' do things
With this in a global module:
Function AmISubreport(prpt As Report) As Boolean
' tests the report's Parent property to see if it's run as a subreport.
Dim varDummy
On Error Resume Next
Set varDummy = prpt.Parent
If Err Then Err.Clear Else AmISubreport = True
End Function
Hope this helps,
(°v°)