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.

bulletboyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

harfangCommented:
Hi,

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°)
0
josocalCommented:
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
0
bulletboyAuthor Commented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

harfangCommented:
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°)
0
harfangCommented:
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°)
0
bulletboyAuthor Commented:
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.
0
harfangCommented:
All reports use the same query, but different filters? the dNextSunday was just one example? If not, just change your base query...

Anyway, you could still store the query as string in each report's RecordSource, or perhaps use a simple query based on your "standard" one (again, not saved as query, simply written into the RecordSource property):

    SELECT * from qselStandard WHERE scheduledin <= Date()-WeekDay(Date())+8

For the one using NextSunday, and similar simple queries for the other reports. This is quite the same as applying a filter on the Report_Open event, and probably better. That way, you can still change qselStandard to affect all reports.

Or maybe I'm missing the point.
(°v°)

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bulletboyAuthor Commented:
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?  
0
harfangCommented:
> 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°)
0
bulletboyAuthor Commented:
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.  
0
harfangCommented:
Thank you, and good luck with your reports!
(°v°)
0
bulletboyAuthor Commented:
It worked (we knew it would).  Thank you again.
0
harfangCommented:
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°)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.