Solved

report_open of subreport in group footer only executing once.

Posted on 2006-11-03
13
457 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?
0
Comment
Question by:jmarkfoley
  • 6
  • 5
  • 2
13 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 17872424
Hi jmarkfoley,
Please post the code for your subreport open procedure.


Pete
0
 
LVL 4

Expert Comment

by:GordonPrince
ID: 17872793
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.
0
 
LVL 1

Author Comment

by:jmarkfoley
ID: 17879139
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
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 77

Expert Comment

by:peter57r
ID: 17879890
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


0
 
LVL 4

Expert Comment

by:GordonPrince
ID: 17880128
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?
0
 
LVL 1

Author Comment

by:jmarkfoley
ID: 17904263
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.

0
 
LVL 4

Accepted Solution

by:
GordonPrince earned 500 total points
ID: 17905299
The subreport should be able to refer to the main report's controls using the subreport's Parent. As in
msgbox me.Parent.ControlName
0
 
LVL 1

Author Comment

by:jmarkfoley
ID: 18021477
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).
0
 
LVL 4

Expert Comment

by:GordonPrince
ID: 18021632
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.
0
 
LVL 1

Author Comment

by:jmarkfoley
ID: 18021945
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?
0
 
LVL 4

Expert Comment

by:GordonPrince
ID: 18022011
What about Me.SubreportControlName.Report.RecordSource= "..."
0
 
LVL 1

Author Comment

by:jmarkfoley
ID: 18022079
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".
0
 
LVL 1

Author Comment

by:jmarkfoley
ID: 18024364
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.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

790 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