By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,106 Members | 2,679 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,106 IT Pros & Developers. It's quick & easy.

Referencing Subreport Control in Main Report Footer

D Giles
P: 11
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.
Feb 14 '08 #1
Share this Question
Share on Google+
12 Replies


mshmyob
Expert 100+
P: 903
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.
Feb 14 '08 #2

D Giles
P: 11
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.
Feb 14 '08 #3

dima69
Expert 100+
P: 181
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.
Feb 14 '08 #4

D Giles
P: 11
ps: mshmyob : I couldn't see anything beneath your post "for example.."
Feb 14 '08 #5

D Giles
P: 11
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.
Feb 14 '08 #6

dima69
Expert 100+
P: 181
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.
Feb 14 '08 #7

dima69
Expert 100+
P: 181
And another thing, the subreport should never be empty (i.e., contain no data), otherwise you get an error.
Feb 14 '08 #8

D Giles
P: 11
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.
Feb 14 '08 #9

mshmyob
Expert 100+
P: 903
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.

Expand|Select|Wrap|Line Numbers
  1. Dim vTotal As Variant
  2. ' need to use a recordset to make this work
  3. Set tmpTable = Me.RecordsetClone
  4. On Error Resume Next
  5. tmpTable.MoveFirst
  6. vCount = tmpTable.RecordCount
  7. ' get your SUM
  8.    For vCounter = 1 To vCount
  9.         vTotal = vTotal + (tmpTable.Detail_Price)
  10.         On Error Resume Next
  11.         tmpTable.MoveNext  
  12.     Next
  13. ' pass the SUM to your main form control
  14. Forms!frmMain.txtTotal.Value = vTotal
  15.  
Feb 14 '08 #10

D Giles
P: 11
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.

Expand|Select|Wrap|Line Numbers
  1. Dim vTotal As Variant
  2. ' need to use a recordset to make this work
  3. Set tmpTable = Me.RecordsetClone
  4. On Error Resume Next
  5. tmpTable.MoveFirst
  6. vCount = tmpTable.RecordCount
  7. ' get your SUM
  8.    For vCounter = 1 To vCount
  9.         vTotal = vTotal + (tmpTable.Detail_Price)
  10.         On Error Resume Next
  11.         tmpTable.MoveNext  
  12.     Next
  13. ' pass the SUM to your main form control
  14. Forms!frmMain.txtTotal.Value = vTotal
  15.  
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.
Feb 14 '08 #11

mshmyob
Expert 100+
P: 903
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.
Feb 14 '08 #12

dima69
Expert 100+
P: 181
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 ?
Feb 15 '08 #13

Post your reply

Sign in to post your reply or Sign up for a free account.