Solved

ACCESS 2003 use total in subreport textbox in another subreport textbox

Posted on 2011-09-21
12
289 Views
Last Modified: 2012-05-12
I have a main report with two subreports.  I use the textbox value from the main report in the subreport calculation sucessfully.  However,  when I pull the main report into another Main report making the main report now a subreport the calculation in the subreport within the new subreport fails.  #error is result.   Help.  
0
Comment
Question by:eyes59
  • 6
  • 4
  • 2
12 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 36577656
Can you post a sample to illustrate this? (mask any sensitive data, please)
0
 

Author Comment

by:eyes59
ID: 36577705
MainReport textbox controlname  = txtPendedCount located =Reports!RPTAnnuityPostIssueDashboard_NIGO_SupPg5!txtPendedCount

From the Main Report the subreport  Source Object =Report RPTAnnuityPostIssueDashboard_NIGO_1
I created a textbox on the subreport above as text251  with the control source =Reports!RPTAnnuityPostIssueDashboard_NIGO_SupPg5!txtPendedCount

SubReport calculation =[text231]/[text251]  this calculation works

When I take the main Report  Reports!RPTAnnuityPostIssueDashboard_NIGO_SupPg5 and make it a subreport on another Main report the result of the calculation =#error
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36577956
This is probably because all of the referencing changes when you start using main reports as sub reports.

Why not just make two of these reports?
Use one as the main form (as you have working already)
Then use the other as a subform, then simply use the expression builder to get the referencing right for the textbox....
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:eyes59
ID: 36578027
I have to do this way as the Main Report that becomes a subreport becomes one of 5 supplemental (subreports) within one big Main Report.  The supplement reports can be run individually OR within a Master Main Report.   Do you know how the referencing changes when I move the first Main report to another Master Main report making it a sub report?   THANKS
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36578355
Then only use the term "Main Report" for the actual "Main" (top) level Report.
Otherwise the terminology becomes confusing.

I don't design my reports in this way because of this issue.

I'll let mbizup take it from here...


JeffCoachman
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36579376
<I'll let mbizup take it from here...>

I honestly am having a very difficult time visualizing this one without without the sample I requested earlier, and will have to leave it to others.

0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 36581341
eyes59,

This is a typical Controlsource of a textbox in a subreport referencing a value in the main report:
=[Reports]![rptCustomers]![CustRatio] * [Freight]
-^--Main Report Control Reference--^   ^------^------Subreport control reference

Here is what the same controlsource reference needs to be if the main report is now being used as a subreport
=Reports![rptBuildings]![rptCustomers].Report![CustRatio] * [Freight]
-^---Main Report---^  ^----New Subreport control ---^   ---^------Sub/Subreport control

So here you see that the ControlSource references must change in order for you to perform the same calculation.
Again, this is not commonly done, most developers simply make another copy report and try not to "Re-use" the same report in order to simply avoid this issue.

To do what you are asking, you would have to come up with some sort of "Detection" logic that would detect if the current report was now being used as a sub report, then change the referencing dynamically via VBA....
Too much work IMHO, and again, ...not to sound like a broken record, ...but this is not a common issue here because most developers don't' do this.

<I have to do this way as the Main Report that becomes a subreport becomes one of 5 supplemental (subreports) within one big Main Report. >

What is your experience with MS Access Report design?
I see no reason why you "Have" to do it that way, ...especially since it is not working for you...

Why is it impossible to make an identical copy of the Report and use that as the "sub report"?
I don't really see an issue with doing it this way, especially since it will work fine...

But as always, I am open to other points of view or alternatives form the other Experts...


JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36581589
FWIW,

For clarity, here is an example of my approach.



EEQ-Access-27321226ReferencingCo.mdb
0
 

Author Closing Comment

by:eyes59
ID: 36582998
It would have gotten an A except for the bit about my ACCESS experience.  The purpose of this site is to ask questions not be insulted.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36583348

<It would have gotten an A except for the bit about my ACCESS experience.>
What insult?
Inquiring about a user experience is fairy standard in all forums like this one.
It help us tailor our responses to be commensurate with the asker's skill level.

There are literally hundreds of posts here where an asker will state: "I Have to use a recordset loop to..."
Only because their experience in Access had not yet expanded to the point of them knowing that an Action query could accomplish the same thing in less time, with less system resources.

Or a asker will state that they: "Have to save a the customer name in the Orders table so it shows up in the report".
Only to find out that only the CustomerID need be stored in the Order table, and the name can be "Looked up", then displayed in the report.

Do a search here of:  "What is your experience"
..and you will see that it is fairly common for both Experts and members to pose this question of each other, ...and no one feels insulted...

In fact the asker's Skill level was actually required at one point on this site to even ask a question.

<The purpose of this site is to ask questions not be insulted.>
Would a person looking to insult you post such a detailed explanation... and then take the time to create a sample file...?
;-)

JeffCoachman

0
 

Author Comment

by:eyes59
ID: 36584289
I apologize for my comment the way I was asked about report design experience.   Frustrated by the issue
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36584491
No problem.

I have frustrating days as well...

;-)

Jeff
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question