Solved

ACCESS 2003 use total in subreport textbox in another subreport textbox

Posted on 2011-09-21
12
286 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
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…

760 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now