Referencing subreport field on main report

[Guest]

Hi all,
I know this is simple but I just can't get it.
I have subreport called rptOrdersbyDateSub has a field on its footer having
=Sum([Line Total]) in its data source property and is called subLineTotal...
now this works fine.
Im referring subLineTotal from the main report footer with a text field
called txtGrossTotal like this... =rptOrdersByDateSub.Report.subLineTotal in
the data source property of txtGrossTotal.

The main report is grouped by OrderID and txtGross displays the sum of the
first sorted order rather than displaying the overall total of serveral
OrderID....

I have checked the names of all controls and they are fine...
Can anyone help me out on this...

Thanks in advance.

 

[Duane Hookom]

I don't think you will have success referencing the value of a control on a
subreport from another section of the main report.
I can't really picture your sections and sorting of your main report or the
section of the main report containing the subreport.

 

[Guest]

You might try something like this.

Declare a new variable:
Dim runningtotal As Integer

Set this value to 0 when your report first opens.

In an event procedure for the on format property of your main report,
begin combining the totals from each subreport:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
runningtotal = runningtotal + reportOrdersByDateSub.Report.subLineTotal
End Sub

Lastly, do not assign any value to the control source of txtGrossTotal.
Simply add an event procedure on format for the main report footer,
Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As
Integer)
Me.txtGrossTotal= runningtotal
End Sub

Hope this helps...

 

[Guest]

Hi Duane....
the subreport has a report header and footer plus detail sections...
subLineTotal field is on the footer of the subreport...

the main report has header and footer , page header and page footer, OrderID
hearder and OrderID footer, and the details section...
subreport is embeded on the OrderIDHeader section....

And I got field txtGrossTotal on the footer of the main report wanting to
display the overall total of a given range of OrderID...
Hope I explained that well...

Thanks
--
niuginikiwi
Nelson, New Zealand

I don't think you will have success referencing the value of a control on a
subreport from another section of the main report.
I can't really picture your sections and sorting of your main report or the
section of the main report containing the subreport.

--
Duane Hookom
MS Access MVP

Hi all,
I know this is simple but I just can't get it.
I have subreport called rptOrdersbyDateSub has a field on its footer
having
=Sum([Line Total]) in its data source property and is called
subLineTotal...
now this works fine.
Im referring subLineTotal from the main report footer with a text field
called txtGrossTotal like this... =rptOrdersByDateSub.Report.subLineTotal
in
the data source property of txtGrossTotal.

The main report is grouped by OrderID and txtGross displays the sum of the
first sorted order rather than displaying the overall total of serveral
OrderID....

I have checked the names of all controls and they are fine...
Can anyone help me out on this...

Thanks in advance.

 

[Guest]

Hi JoyAA,
As I'm a lil new to VBA, where do I place the runningTotal variable and set
it to zero... is it on open event of the main report or is it just some
public variable declaration?
Im not reall clear on that.
If you refer to my reply above for Duane, u can see how my report sections
are divided and grouped...

THanks

 

[Duane Hookom]

You can try duplicate your text box in the OrderIDHeader section and set its
Running Sum to Over Group. This will create a running sum of the values in
the section. If the name of this control is txtMyRunningSum, you can add a
text box in a higher level sorting group footer with a control source of:
=txtMyRunningSum

--
Duane Hookom
MS Access MVP

Hi Duane....
the subreport has a report header and footer plus detail sections...
subLineTotal field is on the footer of the subreport...

the main report has header and footer , page header and page footer,
OrderID
hearder and OrderID footer, and the details section...
subreport is embeded on the OrderIDHeader section....

And I got field txtGrossTotal on the footer of the main report wanting to
display the overall total of a given range of OrderID...
Hope I explained that well...

Thanks
--
niuginikiwi
Nelson, New Zealand

I don't think you will have success referencing the value of a control on
a
subreport from another section of the main report.
I can't really picture your sections and sorting of your main report or
the
section of the main report containing the subreport.

--
Duane Hookom
MS Access MVP

Hi all,
I know this is simple but I just can't get it.
I have subreport called rptOrdersbyDateSub has a field on its footer
having
=Sum([Line Total]) in its data source property and is called
subLineTotal...
now this works fine.
Im referring subLineTotal from the main report footer with a text field
called txtGrossTotal like this...
=rptOrdersByDateSub.Report.subLineTotal
in
the data source property of txtGrossTotal.

The main report is grouped by OrderID and txtGross displays the sum of
the
first sorted order rather than displaying the overall total of serveral
OrderID....

I have checked the names of all controls and they are fine...
Can anyone help me out on this...

Thanks in advance.

 

[Guest]

I created a report with Orders and OrderDetails.
Here's what the VBA code looks like for my test case.
================================================
Option Compare Database
Dim runningtotal As Integer

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
runningtotal = runningtotal +
Me.OrderDetails_subreport.Report.subLineTotal
End Sub

Private Sub Report_Open(Cancel As Integer)
runningtotal = 0
End Sub

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Me.txtGrossTotal = runningtotal
End Sub

 

[Guest]

niuginikiwi,

This is Way Cool. This is a more efficient way to do it. I just tried it and
it works.
This is what I did.

In the Detail section where I have the Order information, I added an
(invisible) text box named txtMyRunningSum and made the control source as
follows:
=[OrderDetails_subreport].Report!subLineTotal
I set the running sum property to Over All.

Then in the report footer I set the control source of txtGrossTotal to
=[txtMyRunningSum]

Cool. I like it. But my design is a little different than yours because my
orderdetails subreport is embedded in order detail section, not the header
section.
(But I just moved the subreport and it all still works so maybe this'll work
for you too.) Try it and let us know if you get it working.

 

[Guest]

Thanks both Duane and JoyAA
Both of you are great helpers. I had something in the line of what your
suggestions where and you have reaffirmed that nicely.
Thanks again for the invaluable help.
--
niuginikiwi
Nelson, New Zealand

niuginikiwi,

This is Way Cool. This is a more efficient way to do it. I just tried it and
it works.
This is what I did.

In the Detail section where I have the Order information, I added an
(invisible) text box named txtMyRunningSum and made the control source as
follows:
=[OrderDetails_subreport].Report!subLineTotal
I set the running sum property to Over All.

Then in the report footer I set the control source of txtGrossTotal to
=[txtMyRunningSum]

Cool. I like it. But my design is a little different than yours because my
orderdetails subreport is embedded in order detail section, not the header
section.
(But I just moved the subreport and it all still works so maybe this'll work
for you too.) Try it and let us know if you get it working.

You can try duplicate your text box in the OrderIDHeader section and set its
Running Sum to Over Group. This will create a running sum of the values in
the section. If the name of this control is txtMyRunningSum, you can add a
text box in a higher level sorting group footer with a control source of:
=txtMyRunningSum

 

[ironwood9 via AccessMonster.com]

This can be done – here is the syntax:

Put the following in the record source of an unbound text box – I would
suggest that the object name in the subreport be the same as the name of the
object itself.

=IIf(IsNull(Reports!YourMainReportName!YourSubReportName.Report!
YourObjectName),0,Reports!YourMainReportName!YourSubReportName.Report!
YourObjectName)

If the field the text box in the subreport is NET_REVENUE, then it’s a good
idea to make sure the name of the text box in the sub report is also named
NET_REVENUE.

Hi all,
I know this is simple but I just can't get it.
I have subreport called rptOrdersbyDateSub has a field on its footer having
=Sum([Line Total]) in its data source property and is called subLineTotal...
now this works fine.
Im referring subLineTotal from the main report footer with a text field
called txtGrossTotal like this... =rptOrdersByDateSub.Report.subLineTotal in
the data source property of txtGrossTotal.

The main report is grouped by OrderID and txtGross displays the sum of the
first sorted order rather than displaying the overall total of serveral
OrderID....

I have checked the names of all controls and they are fine...
Can anyone help me out on this...

Thanks in advance.