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.
12 7999
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.
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.
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.
ps: mshmyob : I couldn't see anything beneath your post "for example.."
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.
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.
And another thing, the subreport should never be empty (i.e., contain no data), otherwise you get an error.
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.
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
-
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.
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.
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 ?
Sign in to post your reply or Sign up for a free account.
Similar topics
by: last Name |
last post by:
Hello all,
I'm using Access 2000. I have a subreport control which loads a
report in the detail section of a main report. I need to keep a count
of each item price in the subreport and place it...
|
by: DFS |
last post by:
Main report contains one large graph in the detail band (no detail records).
Subreport snakes just fine (5 columns) when opening by itself. When I add
it to the main report (in the report...
|
by: Marie |
last post by:
How do you display a total from a subreport on the main report. If it makes
a difference, the total is a total of a calculated field (Qty * Price). Does
the total on the subreport go in the...
|
by: Steve Jorgensen |
last post by:
I tried to fix a problem for a client today in which report sections and even
individual text controls in some of their reports are being split across page
boundaries. Of course, I was thinking...
|
by: sara |
last post by:
I am STUCK!
I have an Access2000 report; main report and subreport. The sub report
is 2 columns, with a footer controlled by a value in a field.
The Report Footer should have SUM of values in...
|
by: shaqattack1992-newsgroups |
last post by:
I know this is kind of a weird question, but is there anyway to give a
subreport control of a main report?
I posted my situation earlier about having drawings print out after a
group. I have a...
|
by: Beowulf |
last post by:
I'm having some difficulty and I can't for the life of me figure
out why. I have a main report, rptMain, that contains a report
header (displays the AppTitle), 3 group headers (one is a
header...
|
by: MLH |
last post by:
I have a report and on it, a subreport control
Main Report Name: rptInvoiceMain
Sub Report Name: rptInvoiceSubReport
SubReport Control Name: rptInvoiceSubReportCtl
The...
|
by: Jana |
last post by:
Using Access 97.
Background:
I have a main report called rptTrustHeader with a subreport
rptTrustDetails in the Details section of the main report. The main
report is grouped by MasterClientID. ...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |