Passing Parameters from Report to SubReport

Have you considerd linking the main report to your subreports? Especially since they all require the same parameter.  That way all you have to do is pass the parameter to the main report and all the subreports will follow.  You do this with the Master Link Fields and Child Link Fields of each subreport's control properties.  Use your parameter field or the primary key for each subreport so that the Master Link Field = the Chld Link Field on each subreport. Continue to use the same store procs you are using.

I am not sure how to link the parameters by the Master/Child link fields and I am pretty lost when you say to link the main report to the subs...  
The thing is, all five reports are really seperate reports, but need to be displayed on the one page. I guess instead of using stored procedures that require a parameter ( for the quarters), I could hard code it into the SPs and just use five reports (A total, 1st Q, 2nd Q, 3rd Q, and a 4th Q) and depending on which Quarter the user wants output determines which report runs although I don't think that is neccessary since the format is the exact same for each, just the data is different.  Could this be a limitation of ACCESS 2000?

Another Question...  If I declare a public string variable, behind my report, and load it with the parameter needed to run the SP, why does ACCESS error and tell me that the SP does not exist or is mispelled.  for instance:

Option Compare Database
Option Explicit

     Dim Timing As String
     Dim str As String

Private Sub Report_Open(Cancel As Integer)

Timing = "'1st Quarter'" '<--- I could load this into an invisible text box if it would actually find the SP
                                   ' so that Timing = me.txtInvisbleParameter
                         
str = "Report_QuarterBudget " & Timing

Me.RecordSource = str

DoCmd.Maximize

End Sub

OR THIS:

Option Compare Database
Option Explicit

Dim Timing As String
Dim str As String

Private Sub Report_Open(Cancel As Integer)

Timing = "1st Quarter"

str = "Report_QuarterBudget '" & Timing & "'"

Me.RecordSource = str

DoCmd.Maximize

End Sub

In Query Analyzer, I run the SP like this:

Report_QuarterBudget '1st Quarter'

and it runs perfectly, why does Access have such a problem with that?


Thanks again for your help!

>The thing is, all five reports are really seperate reports, but need to be displayed on the one page<

That's one of the great things about subreports.  The best way to link subreports to main reports is to use the 'subreport wizard'.  You don't even need to design the subreports intially, just fix them up when the wizard is done.  Create/open a 'Main' report in design mode.  Now from the design toolbox, select the subform/subreport tool and then drag a rectangle whereever you want to place your first subreport on the main report.  When you let go of the rectangle, the wizard will start up and guide you through the process.  Select the 'Use Existing Tables and Queries' option.  In the next step, select the SP you want to use as the recordsource for the subfrom.  In the next step, select the 'Define My Own' option.  Now in the pulldown lists for the fields, select a field(Report_QuarterBudget) from the main form and a matching field(Report_QuarterBudget) from the subform.  This will link the two forms by way of the fields you just selected.  In the next step, name the subform something that makes sense like 'frm1st_Qtr_subform' and then finish the wizard.  You'll now see the subform displayed within your main form and they are now linked.  Whenever you open the main report with a parameter, the subreport will display the information related to that parameter:

DoCmd.OpenReport "YourMainReportName", acViewPreview, , "Report_QuarterBudget '" & Timing & "'"

BUT.....You could actually place the value of 'Timing' in a text box and pass the text box value instead of a global variable:

DoCmd.OpenReport "YourMainReportName", acViewPreview, , "Report_QuarterBudget '" & me.txtInvisbleParameter & "'"

Use a command button to execute the OpenReport.  Passing a value to a report is by far the best way to do this.  That way you don't need the On Open code to define the recordsource.

I would highly recommend you learn about subforms and subreports.  They are a very valuable tool when developing database applications.  The more you learn about them the more ways you will see to use them.

>Another Question...  If I declare a public string variable, behind my report, and load it with the parameter needed to run the SP, why does ACCESS error and tell me that the SP does not exist or is mispelled. <

I think that is an erroneous error. :-)  Sometimes errors are not very clear.  If you want to declare a public variable, you should do that in a Module.  However you need to use this format:

Global Timing as String

This will make the variable available anywhere in the app.  I don't think you need a Global variable in this case though.  Let me know if you need some more help.

Hey Dave,

Perhaps its a matter of Access 2000 Standard edition vis a vis Professional?

 have an Access 2000(standard edition) project where I want to set my Report recordsource dynamically in VBA.  I coded according to the following link:

http://support.microsoft.com/?kbid=300693

So you'd think it would work, right? No.  My project cannot find the stored procedure.  If I take away the "Exec" and the parameter part of the statement it works (and then prompts me for the parameter).  See code:

[code]
Private Sub Report_Open(Cancel As Integer)
    Dim strRecordSource As String
    strRecordSource = "Exec spA1 'td962'"
    Me.RecordSource = strRecordSource
End Sub
[/code]

Ok so I tried to see if I could set up a Northwind.ADP like in the link and got the "cannot find" statement also.  This is even though I just cut and paste the same code from the link above.

[code]
Private Sub Report_Open(Cancel As Integer)
    Dim strRecordSource As String
    strRecordSource = "Exec [Sales By Year] '1/1/97','12/31/98'"
    Me.RecordSource = strRecordSource
End Sub
[/code]

So the only thing I can figure is that perhaps this code works with professional 2000 and not with standard.
Any ideas?  What do ya think?

thanks in advance,
SP

Sard0nicpan,
There is no difference between Accesss 2k and 'professional'. Professional is an Office designation.  Office professional includes Access and I believe Publisher.  Standard doesn't.  Passing a value with the OpenReport method is much more versatile even if you want to use individual reports because you can use the same report for all your quarterly reports.  Just pass the value to the report.  Your method will work but it will cause you to hardcode the values in the On Open event of each report.  As long as you have working SPs, passing the value will work just fine.  There is also a parameter for OpenReport called OpenArgs.  You can use OpenArgs to send a new Title to each report so you can use one report and change the title depending on what you are printing. There are a lot more features of OpenReport.  It is the best method for opening reports.

Arji,

I realy appreciate your help, but I can't seem to follow your advice, and here's why....

This is an Access Project (.adp) and I am connected to an SQL Server, so when I tried redesigning the report using the subreport wizard, the only options I get is to use 'Existing Tables or Views', or 'Existing Forms or Reports'.  The problem is that I cannot create a view that requires a parameter to return a specific recordset like a query.  The only option I see to use is a Stored Procedure...  Am I wrong?

For this question, I will use 2 stored procedures (and give the code) so maybe I can clarify the problem a little better than I have in the past.

Here it is:

01.  The main report is a Budget summary of projects my company will do this year.
02.  There are 5 columns that make up the summary.  They are as follows:

              PROJECTTYPE (a category name given to each project)
              BUDGET          (in the db, this denotes a project that is definitely going to be done this year)
              WEDGE           (in the db, this denotes a project that might be done this year)
              RISK               (because a wedge item might NOT be done, we RISK the total estimate for the Wedge item by 50%, so RISK = WEDGE/2)
              TOTAL            (this is a total of BUDGET + RISK)

03.  There are 2 subreports on the main report that are breakdowns of the entire summary
04.  The first subreport is a breakdown of the WEDGE items sorted by PROJECTTYPE, then by LOCATION
05.  The first subreport contains its own sub report that summarizes the Wedge items only by LOCATION and does not take into account the PROJECTTYPE
06.  The secon subreport, on the main form, is a breakdown of all the Budget items by PROJECTTYPE, then LOCATION and also contains its own subreport...

07.  The main reports name is TOTALSBYPROJECTTYPEQ.
08   The Q at the end is my way of saying this is going to be the report that we can use to filter out the totals by QUARTER
09.  The Stored Procedure that I use for it's record set is REPORT_QUARTERBUDGET and the code is as follows:

     ALTER  PROC Report_QuarterBudget (@Timing varchar(25))
     AS

     SELECT A.ProjectType, A.Budget,
         Wedge = CASE WHEN A.wedge IS NULL THEN 0 ELSE A.wedge END,
         Risk = CASE WHEN A.risk IS NULL THEN 0 ELSE A.risk END,
        Total = CASE WHEN SUM(A.Budget + A.Risk) IS NULL THEN SUM(A.Budget + 0) ELSE SUM(A.Budget + A.Risk) END
     FROM

     (

     SELECT B.ProjectType, B.[Net] AS Budget, W.[Net] AS Wedge, W.Risk
     FROM

      (
      SELECT ProjectType, SUM(CurrentNet) AS [Net], BudgetType
      FROM ProjectView
      WHERE BudgetType = 'Budget' and Timing = @Timing
      GROUP BY ProjectType, BudgetType
      ) B

     LEFT OUTER JOIN

      (
      SELECT ProjectType, SUM(CurrentNet) AS [Net], SUM((CurrentNet)/2) AS [Risk], BudgetType
      FROM ProjectView
      WHERE BudgetType = 'Wedge' and Timing = @Timing
      GROUP BY ProjectType, BudgetType
      ) W

     ON  B.ProjectType = W.ProjectType

     GROUP BY B.ProjectType, B.[Net], W.Risk, W.[Net]
     ) A

     GROUP BY A.ProjectType, A.Budget, A.Wedge, A.Risk

10.  As you can see, the SP requires the @Timing variable which could be '1st Quarter', '2nd Quarter', '3rd Quarter' or '4th Quarter'
11.  The stored procedure for the first subreport also requires the @Timing variable, as it is a breakdown of the summary as a whole.  the code is as follows:

     ALTER PROC Report_QLocationWedge (@Timing varchar(25))
     AS

     SELECT Location, CurrentNet AS Unrisked, SUM(CurrentNet / 2) AS RISKed
     FROM ProjectView
     GROUP BY ProjectID, Project, Location, CurrentNet, BudgetType, Timing
     HAVING (BudgetType = 'Wedge') and Timing = @Timing

12.  As you can see, it is a small portion of the whole, but adds in the LOCATION part

There really isn't a field I can link together, that I can see, so if I could pass the @Timing Variable from the main report to the sub report without the user typing it in several times, that would be great.  Right now I have a form (frmMenuReports) that has a list box (lstReports) that looks to a table (Reports) and lists the Report names as I want them displayed.  When the user opens frmMenuReports, They can click the option from lstReports that displays "Budget Summary by Quarter".  Once clicked, it opens an option frame (fmeQuarterOption) where the user will choose which Quarter they want the report to print.  When the user chooses an option, it sets the string variable (TIMING) to '1st Quarter', '2nd Quarter', '3rd Quarter' or '4th Quarter'.  On the bottom of frmMenuReports, there is a command button (cmdRunReport) that opens the report chosen, and if it is this particualr report, it opens and passes the variable TIMING as the parameter needed for the stored procedure, Report_QuarterBudget, as well as the parameter for the other stored procedures for all the subforms.  This way the user never has to acutally type anything out and everything is dynamic....  

Thanks Again!

Arji,

You are correct, the second subform's Stored Procedure should be:

     ALTER PROC Report_QLocationWedge (@Timing varchar(25))
     AS

     SELECT ProjectType, Location, CurrentNet AS Unrisked, SUM(CurrentNet / 2) AS RISKed
     FROM ProjectView
     GROUP BY ProjectType, ProjectID, Project, Location, CurrentNet, BudgetType, Timing
     HAVING (BudgetType = 'Wedge') and Timing = @Timing


I was in the middle of testing siome consolidation of stored procedures and pasted the wrong code...  

Ok, I think I see where you're coming from.  There is a field in my table called TIMING...  But, if I remove the @Timing variable from the Stored Procedures, how will I return the recordset of all items for only '1st Quarter', '2nd Quarter', '3rd Quarter' or '4th Quarter'?

That's what I'm doing here, right?:    DoCmd.OpenReport "TOTALSBYPROJECTTYPEQ", acViewPreview, , "Timing'" & me.txtInvisbleParameter & "'"
     (just as an aside, did you mean   "Timing = '" & me.txtInvisibleParameter & "'" ?)

I'll give that a try...

Dave