Linking SubReports requiring 'Input Parameters' from thier Master Reports

I have a master report which uses one User Defined Function as it's source.  It takes the input parameters from another form which initiated it's opening.  Within this report I have a subreport which is also taking one input parameter from the same form, but needs to take another parameter from the master report's first grouping as well.  I'm able to pull a single value from the report (the first one in it's recordset), but am unable to itterate through them as each record is pulled.  For example:

  PO#: 1234                                   <-- Value pulled from outside form
  =========================
  Qty: 25                                        <-- Operations grouped by this value
    Op#: 1   Hours: 1   Cost: $123
    Op#: 2   Hours: 2   Cost: $123
    Op#: 3   Hours: 3   Cost: $123
    -----------------
  Total Cost: $369                           <-- Subreport calls function which needs the PO and the Qty
  Total Hours: 6
  =========================
  Qty: 50
    Op#: 1   Hours: 4   Cost: $321
    Op#: 2   Hours: 5   Cost: $321
    Op#: 3   Hours: 6   Cost: $321
    -----------------
  Total Cost: $369                           <-- When coded as below it only utilizes the first Qty
  Total Hours: 6


Main report name is "EstimateSummary", which uses the "fEstimate_Operations" function as it's source and has an  'Input Parameters' = "EstID = [Forms]![Estimate Log]![ID]"

The supreport is named "EstimateSummarySub", It resides in the 'Qty Footer', uses "fEstimate_Footer" function as it's source and has the 'Input Parameters' = "EstID = [Forms]![Estimate Log]![ID], Qty=[Report]![Qty]".  Where "Report!Qty" is the value on the master report... only problem is that as the Qtys itterate, the footers only use the first Qty available.

Oh and I'm using SQL Server 2000 w/ Access 2003 'ADP'
Dane_GardnerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SjoerdVerweijCommented:
Why on earth would you use a subreport for this? Why not simply put a couple of textboxes in the footer that say =Sum(Cost) and =Sum(Qty)?
0
GreymanMSCCommented:
Use sorting and grouping as SjoerdVerweij suggests.

Additonally, a subreport can, and probably should, be linked to the criteria values in the parent report through the Link Child Field, and Link Master Field properties of the subreport object.  In this case, the setting for both would be "EstID,Qty" and the subreport's recordset does not need to filter by a lookup parameter at all.
0
Dane_GardnerAuthor Commented:
SjoerdVerweij -- Well, obviously it would be quite pointless to merely sum the values from within a UDF, but I was using a (over?) simplified example in order for the reader to understand more easily the issue at hand.  The subreport UDF is in actuallity 5 pages of printed text in 8point Arial Narrow!  Extremely complicated piece of Business logic, which pulls from over eight or nine tables.

GerymanMSC -- Access cannot 'link' a subreport sourced to a UDF which requires parameters based on it's master/child relation; I already tried it, and it throws an error.  Parameters must be passed through the 'Input Parameters' property.  Besides I'd like to do everything server side, instead of building a table with all Qty's for each Qty on the master report (there are quite a bit of them), and then filtering it for the proper Qty.  Doing it this way would be much more efficient.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

harfangCommented:

  =========================
  Qty: 25                                        <-- Name this: txtGroupQuantity
    Op#: 1   Hours: 1   Cost: $123
    Op#: 2   Hours: 2   Cost: $123
    Op#: 3   Hours: 3   Cost: $123
    -----------------
  Total Cost: $369                           <-- SQL: use parameter txtGroupQuantity
  Total Hours: 6                                    Access: use "= Parent.txtGroupQuantity"
                                                           VB: use Me.Parent!txtGroupQuantity
  =========================

It is important to understand name space. When SQL find a non-reserved word, it will search locally (tables, fields), then in the container name space (in this case the parent report), then in the application, then VB, and finally it will ask the user or return #Name?. The problem is that the subform is executed from a detail object, that has no access to the fields. So you need to use control names, displaying the field. This is not the case for forms, where the underlying fields are part of the available names.

Hope this helps!
0
harfangCommented:
I forgot, you can also use control names in the LinkMasterField property (not the LinkChildField, obviously), creating another way to communicate the group Qty to the subform as a simple filter on an appropriate field or calculated expression.

Cheers ;)
0
Dane_GardnerAuthor Commented:
harfang,
I will give that a shot as soon as I get to the office in the morning, though I think I already tried something similar.  Thank you for such a quick professional response.   :)
0
Dane_GardnerAuthor Commented:
harfang, I've tried it (again) and the field is apparently not populated with the proper Qty at the time that the subreport instance is created: it's gleening a NULL value from the textbox, which is even worse than only getting the first value.  Calling the textbox from the Report object gets the same result as calling the field value from the Report.  Any other ideas?
0
harfangCommented:
Sorry, but what exactly did you try? I suggested different syntax for SQL, VB and Access: which one are we talking about? If you used the VB syntax, the important point is the event you have been using.
Could you be a little more specific? Just "it didn't work" is not enough for me to go on...

> "Calling the textbox from the Report object"
This works reliably. Once the report is created, you can always examine the value and other properties of a text box, but the question is  *how* and perhaps *when* do you examine it during the printing process?

> "calling the field value from the Report"
The only places you can use actual field names is in the control source of controls and in the sorting and grouping window.

> "[both provide] the same result"
That is not true. Again, the question is *when*

Cheers:)
0
Dane_GardnerAuthor Commented:
I don't really know how to be more specific, but here goes:

As I understand it a subreport can only recieve parameters, for a UDF that it uses as it's source, from within the 'Input Parameters' property of that subreport, as I stated in my original post.  Exactly when these values are used seems debateable.  In an OnOpen event for the subreport I can access the value from the parent report easily displaying it in a messagebox, but it does not catch the value when I tried your last recomendation of using "Qty = [Parent]![txtQty]" in the 'Input Parameters' property of the subreport.  This was what I had originally attempted, but moved on to using "Qty = [Report]![Qty]" as it's reading the value as either null or an empty string, as I stated above.

This leads me to believe that it's being called before the subreport is 'opened' and before the parent report feild are populated, but after the 'report' object knows it's values.

Oh and all attempts at setting the properties value for the UDF, through VB during the OnOpen event, throw errors.

Let me know if there is anything I can be more clear on.  And  Thanks!
0
harfangCommented:
OK, this makes sense.

1) queries

Query parameters are just unknown strings, e.g. that cannot be resolved locally. The PARAMETERS section of a query can further type these to specific data types (e.g. dates) to avoid SQL data type mismatch errors.
If the source query on your subreport uses [txtGroupQuantity] in any expression (calculated field, criteria), it cannot be resolved locally. If you open the report as stand-alone report, it will end up asking you (the user) what this means? If run as a subreport, it will find the answer in the main report and will not have to ask you. Hence, controls of the main report are available to the query of the subreport.

2) control values

The values of controls are not pupulated before the report's Open event. The entire printing mechanism is set, the various queries are prepared and checked for errors, but that's it. Fiels are Null, as you experienced.
When printing starts (after the open event), all the queries (detail section, group headers and footers, combo boxes and listboxes) are opened and placed on the first record. Then the formatting starts from top to bottom.
For example:
* In a group header, the values of the detail section will be those of the first record in the group
* In a group header, the values of all included group footers and the detail section will show the last record in the group
* In the detail section, all fields from group headers and footers will be available and contain correct values (the query row of the footers is already set correctly, e.g. for sums).

I think the confusion arose because you tried to use the open event for things that should go into the format event.

Also understand that the format event occurs once for each printing. If you have 100 rows, it will be executed at least 100 times. All formattings at the start of the event are not the default values but those you set in the last format event, so make sure to set them back if you change them.

Once the formatting is completed, there is still the print event, but that not what you need either.

I hope this has resolved the issue. If not, please report back.

Good luck!
0
harfangCommented:
Correction: in the second "* In a group header", I meant group footer... :(
0
Dane_GardnerAuthor Commented:
Hmmm... I only used the Open event to debug.  I was trying to see if it were possible to pull the values from the textboxes at all, under any circumstance.  And in the Open event it worked fine, but I couldn't pass that value from that event into the Input Parameters property because the subreport has already passed that point by the time it gets to the OnOpen event.

What I'm trying to do is use the Input Parameters setting in the Subreport's Properties to directly access the textboxes or the reports grouped data when the subreport is 'created', but from your description above it seems that this is impossible.  If this is correct then how would it be possible to link the subreport to the master report in the desired fasion?

Why on Earth does Microsoft make this such a pain in the butt?!?!

Thanks again!
0
harfangCommented:
It's not that. Mind you, I do not defend Microsoft here, but I would often like Microsoft to leave the complex things complex instead of simplifying them to reduce the number of custommer support calls...

Anyway, the Form_Open of the subreport happens only once: when the subreport is loaded into memory within the main form's subreport control. Note that this event can be canceled. At that point, it is not part of a group and might not even have any data in the fields. The main form should already exist, but again, not necessarily with data in the fields.

It would not make sense to reopen the subreport each time it is printed. There is so much error checking an memory management involved that the subreport feature would be terribly slow. Once opened, its underlying record source is manipulated before each printing (this is not accessible from VB, alas).

Once you understand the mechanism, it is obvious that you were in fact looking for one of the Format events, which happen reliably each time a section is printed. You can use the event from the main report -- the section containing the subreport, or the event from the subreport -- any section.

As for "linking", there are of course the LinkChildField and LinkMasterField properties of the subreport control. The names are perhaps misleading, because both can use *calculated fields* as link fields. Also, the LinkMasterField accepts also *control names*, not just fields. Using those, you can perfectly control how the records of the subreport are filtered for each group of the main report.

Good luck!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.