Solved

ACCESS 2003 use total in subreport textbox in another subreport textbox

Posted on 2011-09-21
12
287 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
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…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

932 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

22 Experts available now in Live!

Get 1:1 Help Now