report_open of subreport in group footer only executing once.

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.

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

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?

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.

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.

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= "..."

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".

GordonPrince - I'm giving up on this approach. I've started my question over and reposted https://www.experts-exchange.com/questions/22074150/Need-to-display-query-results-in-report-group-footer.html. Go there and see if you can help. Meanwhile, I'll give you the points for hanging in there.