Solved

report_open of subreport in group footer only executing once.

Posted on 2006-11-03
13
432 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
 
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now