Solved

ACCESS 2003 use total in subreport textbox in another subreport textbox

Posted on 2011-09-21
12
291 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

734 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