Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Subreport Open Event - Modifying Report Filter

Posted on 2006-04-11
13
Medium Priority
?
351 Views
Last Modified: 2012-05-05
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.

0
Comment
Question by:bulletboy
  • 7
  • 5
13 Comments
 
LVL 58

Expert Comment

by:harfang
ID: 16432027
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
 
LVL 1

Expert Comment

by:josocal
ID: 16432135
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
 

Author Comment

by:bulletboy
ID: 16432470
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 58

Expert Comment

by:harfang
ID: 16432505
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
 
LVL 58

Expert Comment

by:harfang
ID: 16432512
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
 

Author Comment

by:bulletboy
ID: 16432693
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
 
LVL 58

Accepted Solution

by:
harfang earned 2000 total points
ID: 16432763
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
 

Author Comment

by:bulletboy
ID: 16432867
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
 
LVL 58

Expert Comment

by:harfang
ID: 16432931
> 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
 

Author Comment

by:bulletboy
ID: 16433228
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
 
LVL 58

Expert Comment

by:harfang
ID: 16433709
Thank you, and good luck with your reports!
(°v°)
0
 

Author Comment

by:bulletboy
ID: 16437540
It worked (we knew it would).  Thank you again.
0
 
LVL 58

Expert Comment

by:harfang
ID: 16439628
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question