Referencing Subreport Control in Main Report Footer

[D Giles]

Access 2003: A subreport control (sum total calculated textbox located in the subreport report footer) does not show total of all records when referenced as a total in the main report footer - only shows one record amount. When I open the subreport alone, it totals the sum of all the records which the query pulls - which is what I want. The referenced textbox in the main report footer only shows one record amount. Why is the total in the main report footer doing this? I solved previous problem of proper referencing syntax which was giving #Name? #Error? errors. I've tried moving my sub-totals to the group footer and kept the grand total in the report footer of the main report to see if it made a difference, but it hasn't. If I make the subreport report footer visible, then when viewing the main report it is not adding all the records either, but repeating each record amount as if the report is a continuous form. I really don't know if this problem is because of syntax?, where the controls are placed in the report sections?, or grouping? Any ideas would be much appreciated.

[mshmyob]

If I understand correctly you do a calculated control in your sub form using the sum and get an answer and when you try to pass it to the main form you are getting a different (wrong) answer in your main form but a correct result in the subform.

Let me know if this is correct. if it is I have a solution.

For example

Access 2003: A subreport control (sum total calculated textbox located in the subreport report footer) does not show total of all records when referenced as a total in the main report footer - only shows one record amount. When I open the subreport alone, it totals the sum of all the records which the query pulls - which is what I want. The referenced textbox in the main report footer only shows one record amount. Why is the total in the main report footer doing this? I solved previous problem of proper referencing syntax which was giving #Name? #Error? errors. I've tried moving my sub-totals to the group footer and kept the grand total in the report footer of the main report to see if it made a difference, but it hasn't. If I make the subreport report footer visible, then when viewing the main report it is not adding all the records either, but repeating each record amount as if the report is a continuous form. I really don't know if this problem is because of syntax?, where the controls are placed in the report sections?, or grouping? Any ideas would be much appreciated.

[D Giles]

Hi thanks. The subreport is in the detail section of the main report. The subreport is pulling two records with amounts R1,500 and R1,000 respectively. If I open the subreport on its own the sum (which is in the report footer of the subreport) adds the two amounts and gets R2,500 correctly. I have referenced a textbox in the main report's group footer to the summed total in the subreport footer =[Leases Test 2 subreport].Report![Subreport Total], but the main report only shows R1,000, and not the R2,500 total. If I set the subreport footer as visible, then in the main report it duplicates each record amount again underneath each record. Does it have anything to do with force pages or sections? I'm so stumped with this, and I have a deadline for tomorrow. To back-track a litte, the main report is a tenant lease schedule, and the subreport is rent escalations which I want to add to add to rent amounts to get total income for month Feb. If I figure this out then I can get on to doing forecasts taking the escalations into account. Thanks for your help.

[dima69]

Hi thanks. The subreport is in the detail section of the main report. The subreport is pulling two records with amounts R1,500 and R1,000 respectively. If I open the subreport on its own the sum (which is in the report footer of the subreport) adds the two amounts and gets R2,500 correctly. I have referenced a textbox in the main report's group footer to the summed total in the subreport footer =[Leases Test 2 subreport].Report![Subreport Total], but the main report only shows R1,000, and not the R2,500 total. If I set the subreport footer as visible, then in the main report it duplicates each record amount again underneath each record. Does it have anything to do with force pages or sections? I'm so stumped with this, and I have a deadline for tomorrow. To back-track a litte, the main report is a tenant lease schedule, and the subreport is rent escalations which I want to add to add to rent amounts to get total income for month Feb. If I figure this out then I can get on to doing forecasts taking the escalations into account. Thanks for your help.

As far as I can remember, the thing you are trying to do (referencing calculated controls on Subreport) is not working with reports, only with forms.
My advice here is to create a separate query calculating the totals you need, and base the main report on that query.

[D Giles]

ps: mshmyob : I couldn't see anything beneath your post "for example.."

[D Giles]

As far as I can remember, the thing you are trying to do (referencing calculated controls on Subreport) is not working with reports, only with forms.
My advice here is to create a separate query calculating the totals you need, and base the main report on that query.

Thanks dima69, in one respect I'm relieved if what you say it true about reports, as I cannot understand why I havent been able to find a solution to this sooner, but in another respect I'm concerned about find an alternative solution.

[dima69]

Thanks dima69, in one respect I'm relieved if what you say it true about reports, as I cannot understand why I havent been able to find a solution to this sooner, but in another respect I'm concerned about find an alternative solution.

Sorry, I think I gave an incorrect answer - got confused with another related problem.
So I think that you CAN use the reference to the subreport calculated total, but the referencing control should be placed in the Detail section (as the subreport itself) of the main report to get the correct result.

[dima69]

And another thing, the subreport should never be empty (i.e., contain no data), otherwise you get an error.

[D Giles]

Sorry, I think I gave an incorrect answer - got confused with another related problem.
So I think that you CAN use the reference to the subreport calculated total, but the referencing control should be placed in the Detail section (as the subreport itself) of the main report to get the correct result.

I placed the referenced control in the Detail section of the main report (same as where the subreport sits), and again it just duplicated the amount under each amount instead of giving one field with one total.

[mshmyob]

The problem is not you it is an Access problem. A subform gets loaded before the main form which is good but the Main form gets loaded so quickly before some calculations in the subform are completed and therefore any controls getting values from the subform appear to get improper results.

Try putting code like the following in your ON CURRENT event of the subform.

Obviously you change the control names and the formula to suit your requirements.

Dim vTotal As Variant
' need to use a recordset to make this work
Set tmpTable = Me.RecordsetClone
On Error Resume Next
tmpTable.MoveFirst
vCount = tmpTable.RecordCount
' get your SUM
   For vCounter = 1 To vCount
        vTotal = vTotal + (tmpTable.Detail_Price)
        On Error Resume Next
        tmpTable.MoveNext  
    Next
' pass the SUM to your main form control
Forms!frmMain.txtTotal.Value = vTotal

[D Giles]

The problem is not you it is an Access problem. A subform gets loaded before the main form which is good but the Main form gets loaded so quickly before some calculations in the subform are completed and therefore any controls getting values from the subform appear to get improper results.

Try putting code like the following in your ON CURRENT event of the subform.

Obviously you change the control names and the formula to suit your requirements.

Dim vTotal As Variant
' need to use a recordset to make this work
Set tmpTable = Me.RecordsetClone
On Error Resume Next
tmpTable.MoveFirst
vCount = tmpTable.RecordCount
' get your SUM
   For vCounter = 1 To vCount
        vTotal = vTotal + (tmpTable.Detail_Price)
        On Error Resume Next
        tmpTable.MoveNext  
    Next
' pass the SUM to your main form control
Forms!frmMain.txtTotal.Value = vTotal

Could not find an On Current event on the subreport, so tried slotting the code into On Open event of the subreport: keep getting compile error on RecordSetClone. Don't have any temp tables, main report and subreport based on two seperate queries. Thanks for help.

[mshmyob]

My mistake I misread SubReport as SubForm. Let me get back to you.

Could not find an On Current event on the subreport, so tried slotting the code into On Open event of the subreport: keep getting compile error on RecordSetClone. Don't have any temp tables, main report and subreport based on two seperate queries. Thanks for help.

[dima69]

I placed the referenced control in the Detail section of the main report (same as where the subreport sits), and again it just duplicated the amount under each amount instead of giving one field with one total.

What do you mean by "under each amount" ? Now I start to suspect that something's wrong with the report/subreport structure. What is the connection between your Main report and it’s Subreport ? What are the underlying tables ?