We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

report_open of subreport in group footer only executing once.

Mark
Mark asked
on
Medium Priority
494 Views
Last Modified: 2012-08-13
I have a subreport in a group footer of my main report. My subreport report_open function changes the subreport RecordSource. For the first groupfooter output (which happens to be grouped by year), it runs fine, updates the RecordSource and I get the correct totals for that year. However, the subreport report_open() function never appears to be called again and the first set of totals appear for all years (groups). I confirmed that the subreport report_open() function is only being called once by putting a msgbox  call in the report_open() routine. I only ever get one msgbox display regardless of how many years I am subgrouping.

How do I get a VBA report to call the the subreport report_open() function FOR EACH subgroup?
Comment
Watch Question

CERTIFIED EXPERT

Commented:
Hi jmarkfoley,
Please post the code for your subreport open procedure.


Pete
Put your code in the report's Group Header. Msgbox is the best way to see what's going on, so you're on track there.

Author

Commented:
GordonPrince - putting the code (below) in the Group's Header (and taking it out of report_open()) didn't work. Since there was no record source, it just gave me an error on the subreport and didn't print it.

Peter57r: below is the sub report's report_open() code. Pretty simple. Mainly all it does it change the subreport's record source. All that really changes is that I'm modifying the year. I get that from the main report. That part works fine ... once! Note that the msgbox displays only once.

Private Sub Report_Open(Cancel As Integer)
   
    thisYear = Reports![_rptPaDetailTransListing]![year]
MsgBox thisYear
    Me.RecordSource = "select c.contribType, max(t.description) description, " & _
        "c.contribYear, count(*) as transCnt, sum(c.payAmount) amount, " & _
        "case when c.contribType = 1 then count(*)  / 26.0 else sum(c.serviceCredit) end as credit, " & _
        "case when c.contribType = 1 then (count(*) / 26.0) * 365.0 else sum(c.serviceCredit) * 365.0 end as days " & _
        "from tblPaActiveContrib c, tblPaContribType t where c.socialSecurityNo = '123-45-6798' " & _
        "and t.contribType = c.contribType and c.contribYear = " & thisYear & _
        " group by c.contribYear, c.contribType " 'order by contribYear, c.contribType

End Sub
CERTIFIED EXPERT

Commented:
When I first saw the Q I was going to send a 'can't do that' reply straight away, but I decided to read Help first, which suggests it can be done.
However, I am satisfied now that my first thoughts were correct and Help is wrong.
You can only set the subreport recordsource once on the first time it opens; after that you will get an error telling you it can't be done.

I don't understand why you are not getting the msgbox  and  an error message.

As a workround you might look at your linking fields and see if you can use an additional value to provide the variation you need.

Pete


Can you set the record source in the report open event so that it includes everything you'll be needing in the whole report, then have the join between the report and the subreport filter the information? Is the SocialSecurityNo in the main report and would it work as the critiera for the sub report where the subreport's record source includes all the years that you want selected?

I think of working with relational data as working with sets. Then you use a filter and group by to restrict and organize the data on reports. Is there a way to make the record set the whole set of data and go at it that way?

Author

Commented:
peter57r - I imagine that I'm not getting the msgbox and an error on successive times is because the subreport's report_open() is never called again.

GordonPrince - I've changed my subreport/report_open() query to look like this:

    Dim thisSSNO As String
    thisSSNO = Reports![_rptPaDetailTransListing]![SSNO]

    Me.RecordSource = "select c.contribType, max(t.description) description, " & _
        "c.contribYear, count(*) as transCnt, sum(c.payAmount) amount, " & _
        "case when c.contribType = 1 then count(*)  / 26.0 else sum(c.serviceCredit) end as credit, " & _
        "case when c.contribType = 1 then (count(*) / 26.0) * 365.0 else sum(c.serviceCredit) * 365.0 end as days " & _
        "from tblPaActiveContrib c, tblPaContribType t where c.socialSecurityNo = '" & thisSSNO & "' " & _
        "and t.contribType = c.contribType and c.contribYear between 2005 and 2006 " & _
        " group by c.contribYear, c.contribType "

Basically, it's the same as my original, but now I've un-hard-coded the ssno and hard-coded the date range. Yes, this does return all records that I will need. Two questions:

1. Now that I have "all needed records", how do I snag only the rows I want within each group footer? I want to display these totals by year, but of course, all years are in the query so each group footer has the same totals aggregated for all years.

2. I can get the SSNO from the main report, apparently because it is part of the main report's record source. But how can I snag the date range? I've tried putting the string " between 2005 and 2006 " in various places on my main report. I've even tried declaring a Public variable in the main report, but no go. The subreport can't "see" any of my main report objects.

The subreport should be able to refer to the main report's controls using the subreport's Parent. As in
msgbox me.Parent.ControlName

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Sorry to take so long getting back. - pulled off on other things.

I'll give the me.parent.controlname thing a try and let you know. I still need to know how to select in the group footer only the subset of records in the "all records" cursor retrived in the sub-report report_open() function.

Maybe I need to re-think this. Since the subreport's report_open function is only called once, I need another way to do a query for just the year-data I want. Is there a function that is called once each time a group footer is run? Can I attach a new recordsource to my subform in the parent groupfooter2_format() function? (If that's possible, I haven't figured out how).
There are a couple of events that fire once for each group. Try putting your code into the Group Footer's Format or Print event and see how that does. I think what you want to do is set the subreport's recordset there.

Author

Commented:
OK, I'm going to try that. I put the recordsource assignment into the groupFooter2_format() function, but I'm having trouble referencing the subreport's recordsource. I'm trying:

Me("_rptPaDetailTransListingSubform").Report.RecordSource =  "..."

That gives me "The expression you entered refers to an object that is closed or doesn't exist"

I assume my syntax is wrong. Can you advise?
What about Me.SubreportControlName.Report.RecordSource= "..."

Author

Commented:
Sorry to be an idot, but what do you mean by SubreportControlName?

btw, I also tried Me("_rptPaDetailTransListingSubform").RecordSource = "..." and it got the message that "Object does not support this property or method".

Author

Commented:
GordonPrince - I'm giving up on this approach. I've started my question over and reposted http://www.experts-exchange.com/Databases/MS_Access/Q_22074150.html. Go there and see if you can help. Meanwhile, I'll give you the points for hanging in there.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.