Trouble bringing in a calculation from main report to use in subreport (1 Viewer)

PamelaJoy

Registered User.
Local time
Today, 10:44
Joined
Oct 28, 2008
Messages
39
I can bring in the ingredient weight from the main form and add it to the container weight inside the subreport. But I get a #Name? error when I try to use the calculated cost of raw materials from my main form to add to the container cost within my subreport (which brings in the three or four product specific containers).

I'm hoping someone can see where I'm going wrong!

Main Report Name: rptFinishedProductCostWithContainers
Subreport Name: qryFinishedProductContainers
Main Form Control Source (which successfully calculates and adds ingredient costs): =Sum(([RawMaterialPercent]*[Weight])*([TotalPerUnitCost]))
Subreport Control name: Text 22
Subreport Control Source (this is where I am trying to take the calculated total from the main form and add it to the cost of the container (called TotalUnitCost):
=(Reports!rptFinishedProductCostWithContainers!qryFinishedProductContainers.Report(Sum(([RawMaterialPercent]*[Weight])*([TotalPerUnitCost])))+[TotalUnitCost])

Unfortunately, this results in #Name? error!

Help?!
 

DALeffler

Registered Perpetrator
Local time
Today, 09:44
Joined
Dec 5, 2000
Messages
263
What is the following controls' name?

Main Form Control Source (which successfully calculates and adds ingredient costs): =Sum(([RawMaterialPercent]*[Weight])*([TotalPerUnitCost]))
Then your subreports control source would be:

=Reports!rptFinishedProductCostWithContainers!qryFinishedProductContainers.Report!MyMainReportControlName+ [TotalUnitCost]

provided TotalUnitCost is is exposed by the query for the subreport.

(and I have no idea why those spaces are appearing in the text for the above control source assignment statement - make sure the don't get into your report!)

hth,
 
Last edited:

PamelaJoy

Registered User.
Local time
Today, 10:44
Joined
Oct 28, 2008
Messages
39
Still not getting the calculated total from main report to use in subreport

Thank you so much for taking the time to help me! Unfortunately, this did not work either (and the spaces were eliminated!). The control name for the working calculation on the Main Report is 'Text90', and the TotalUnitCost value is available from the query - in fact it is showing up just fine in the column right before the one I'm trying to calculate.

I have three questions about your response (I am a novice, this is my first db attempt, I'm learning everything through the "Access 2002 Bible" and this forum):
  1. I thought I had to restate the entire calculation again when referring to it in another area of the report rather than by its control name?
  2. Also, does the subreport load after the main report, or visa versa? Could my problem be that I have to do this calculation after the subreport has 'found' the correct containers and associated costs, and then bring that figure to the main report, and add to the ingredients total instead of doing the calculation on the subreport?
  3. In the code you provided, can you explain the difference between the first 'Report!' (singular) and later in the string 'Reports!' (plural). Is it because one is the Main and one is a Sub Report?
Thanks!
 

rainman89

I cant find the any key..
Local time
Today, 11:44
Joined
Feb 12, 2007
Messages
3,016
Why cant you do

=parent.text90

as the control source to get the value from the main report?
 

PamelaJoy

Registered User.
Local time
Today, 10:44
Joined
Oct 28, 2008
Messages
39
Thanks, Ray. But alas, no - that didn't work either.
I don't know if this helps pinpoint my problem any better, but I get '#Error' for the calculation when the subreport is viewed from the open Main Report, and '#Name?' when it is viewed on the open subreport.
 

PamelaJoy

Registered User.
Local time
Today, 10:44
Joined
Oct 28, 2008
Messages
39
I hope I haven't eliminated anything you need to see to be able to help . . . but I am so new at this 'stripped down version zip file' thing!

Here's hoping . . . and thank you again.
 

Attachments

  • Copy of db 6-15-09.zip
    88.2 KB · Views: 114

rainman89

I cant find the any key..
Local time
Today, 11:44
Joined
Feb 12, 2007
Messages
3,016
I put
=parent.text90+totalunitcost

in there and got the value....
 

Attachments

  • Copy of db 6-15-09.zip
    68.5 KB · Views: 118

PamelaJoy

Registered User.
Local time
Today, 10:44
Joined
Oct 28, 2008
Messages
39
You solved the calculation problem!

:) Hallelujah!!!!!! I don't think I can say 'thank you' enough!
I really appreciate your sticking with me, especially after I told you that parent.text90+totalunitcost did NOT work for me! NOW (insert an embarrassed face here) I understand/see why it did not - and it still doesn't and never will - I opened the SUBREPORT to see if it worked (dumb girl) but the result of the calculation is as clear as day on the main report.
Thank you very, very much. I think frustration was getting the better of me!
 

Users who are viewing this thread

Top Bottom