Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ACCESS 2003 use total in subreport textbox in another subreport textbox

Posted on 2011-09-21
12
Medium Priority
?
299 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 1500 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

721 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