Pass summary value from subreport to main report

[gmurrie99]

I have a report which has too many values to be handled by one query. So I created another report using a different query and inserted it as a subreport. I then total one of those fields and that part works fine. However when I try to set an object in the main report to =Sum(Reports!pg3!price) the result is blank.

 

[dhookom]

Which section of the main report contains the subreport?
Which section of the main report contains the text box?
There probably is no Reports!pg3 since I assume pg3 is the report used as a subreport.

What is the name of the subreport control?
What is the name of the text box in the subreport that you want to reference in your main report?
Are you wishing to sum values from several instances of the subreport?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[gmurrie99]

The main report has the following sections:
Report Header
Page Header
wpno Header
itemno Header
Detail
itemno Footer
wpno Footer
Page Footer
Report Footer

Which section of the main report contains the subreport?
wpno Footer

Which section of the main report contains the text box?
wpno Header

There probably is no Reports!pg3 since I assume pg3 is the report used as a subreport.

pg3 is the name of the subreport, but it exists and I can Sum within it.

What is the name of the subreport control?
pg3

What is the name of the text box in the subreport that you want to reference in your main report?
Text24

Are you wishing to sum values from several instances of the subreport?
Yes. I do this within the subreport. I also have a field in the query (pg3test) which sums price.

 

[dhookom]

I'm not sure you will be able to reference a control in a subreport from a different section on the main report. You could try add a text box in the same section as the subreport control:
Name: txtYourSum
Control Source: =pg3.Report!Text24 (change this)
Then, add a control in your wpno header
Name: txtYourSumInHeader
Control Source: =txtYourSum

Not sure if this will work. You won't be able to sum either of these text boxes on your main report. You may be able to use a running sum or just create totals queries with your values and add them to your report's recordsource.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[gmurrie99]

You're right. That didn't work.But it seems like I ought to be able to write a query that sums the value I need and then stick that value in the text box.

I'm quite new at this, but it seems like such a simple thing to do.

The problem I have is that the report doesn't know which line of data to select. The query lists two fields, wpno and sumofprice. The text box control is in the wpno header of the report. The report is ordered by wpno. I thought that the control would find the appropriate wpno and list that value.

However, each time I run the report, a control comes up asking for a value for MATERIALS_COST, which is the name of the query.

 

[dhookom]

You should add your query to your report's record source and join the wpno fields. Then add the sumofprice to the query grid so you can use it in your report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[gmurrie99]

That's what I thought! I joined the wpno fields in Relationships, but there is no line between between the two tables in the query. What in the world is wrong with me!@

 

[gmurrie99]

Thanks so much for your patient help. I am such a noobie. In the end, what I did was write a query that picks out wpno and totals the price for each. I then call that value from the main recordsource for the report. (Just like you said, I was just doing it wrong.) For text boxes where I need to sum two or more values from other areas, I stuff a nonvisible text box with each value that I need and then sum them in a visible text box.

Thanks again for your help.