Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Linking SubReports requiring 'Input Parameters' from thier Master Reports

Posted on 2004-11-29
16
Medium Priority
?
317 Views
Last Modified: 2012-06-27
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'
0
Comment
Question by:Dane_Gardner
13 Comments
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12701716
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
 
LVL 16

Expert Comment

by:GreymanMSC
ID: 12701752
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
 

Author Comment

by:Dane_Gardner
ID: 12702067
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 58

Expert Comment

by:harfang
ID: 12702504

  =========================
  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
 
LVL 58

Expert Comment

by:harfang
ID: 12702520
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
 

Author Comment

by:Dane_Gardner
ID: 12703305
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
 

Author Comment

by:Dane_Gardner
ID: 12717349
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
 
LVL 58

Expert Comment

by:harfang
ID: 12718518
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
 

Author Comment

by:Dane_Gardner
ID: 12721014
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
 
LVL 58

Expert Comment

by:harfang
ID: 12721605
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
 
LVL 58

Expert Comment

by:harfang
ID: 12721627
Correction: in the second "* In a group header", I meant group footer... :(
0
 

Author Comment

by:Dane_Gardner
ID: 12744732
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
 
LVL 58

Accepted Solution

by:
harfang earned 2000 total points
ID: 12748676
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

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

580 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