D
Duane Hookom
What section of your subreport contains TotalSpending? Is it in the
subreport's Report Footer?
Have you ever checked your references in a module and compiled your
application?
--
Duane Hookom
MS Access MVP
--
subreport's Report Footer?
Have you ever checked your references in a module and compiled your
application?
--
Duane Hookom
MS Access MVP
--
BobV said:Ok, here's what happens when I do this. Well, first things first I
guess.....instead of how you've stated it here, I used the following
because it kept telling me I had a syntax error when I format it as you
have it:
=IIF([qrySpending subreport].Report.HasData=True, [qrySpending
subreport].Report![TotalSpending], 0)
....and just so I understood correctly, I put this in the main report in
the detail section just under the subrpt control.
Anyway......when the report runs, the first six have no spending against
the spendplan item, and for those I get $0.00. The next one I do have
spending against, and after those transactions I get the #Error; then for
every item after that, whether I have spending or not, I get the #Error.
And I assume because of that, the reference to that text box in the Report
Footer is also a #Error.
Duane Hookom said:This is normally not very difficult. You need a text box in the same
section as the subreport. The control source of the text box is something
like:
=IIf(subrptcontrol.Report.HasData, subrptcontrol.Report!txtTotalFromSub,
0)
Then, if you want to reference the value of this text box in your main
report footer, create a text box with a control source of:
=NameOfTextBoxInSectionWithSubReport
--
Duane Hookom
MS Access MVP
BobV said:No, it doesn't repeat.....am just trying to bring the total of all
transactions that appear on the subreport into the main report's Report
Footer. Figured that would be a relatively simple thing to do--was I
ever wrong!
Is there only one instance of the subreport in the main report or will
it repeat? If it repeats, are you expecting to total the totals from
the subreports in the footer of the main report?
If I had understood the subreport might not return records, I would
have suggested the HasData solution in my first reply to you.
--
Duane Hookom
MS Access MVP
Yes....under some of the planned spending items I do have actual money
spent, and in some but not all I have transactions. When I do have
transactions, they appear below the particular spendplan item inside a
box/border with the total at the end of those transactions. And in
the report footer of the subreport where I'm totaling all those
individual totals, I get a grand total of $327,927.85. That's what
I'm trying to bring across to the report footer of the main report.
The reason I said that is because I was looking on Microsoft's KB and
saw an article (288251) on this problem that if I entered the
following in the text box on the main report, if there were no records
to report I would get $0.00.
=IIF([qrySpending subreport].Report.HasData=True, [qrySpending
subreport].Report![TotalSpending], 0)
So I tried it, and when I ran the report I got $0.00 for the total
instead of #Error. Doesn't make sense, again because on the subreport
that total is over $327K, but like I said it "appears" it's not seeing
any of that data. Could the spendplan items where no spending is
recorded, and therefore no total appears, $0.00 or otherwise, have
anything to do with it?
Thanks,
Bob
You stated early on that the subreport had data "but I do actually
have data so a amount should be showing up". Can you see subreport
records in your report?
--
Duane Hookom
MS Access MVP
I still get #Error....I'm starting to think it's just not seeing any
data to report, but I don't know why because there's some there, and
I do get the correct total in the subreport "SpendingTotal" text box
when I run the subreport.
Try create a new text box in the same section of the main report as
your subreport.
Name: txtSpendingTotal
Control Source: =[qrySpending subreport].Report!SpendingTotal
Then change the control source of the text box in your main report
footer section to:
=txtSpendingTotal
--
Duane Hookom
MS Access MVP
********************
My first reply to you was "Is the text box that references the
subreport in the same section of the report as the subreport?"
Guess I misunderstood what you meant here. As I said earlier, the
Textbox that holds the grand total in the subreport is in the
Report Footer of the subreport; and the Textbox where I want it to
appear on the main report is in the Report Footer of the main
report; but the subreport control is in the Detail Section.
********************
Try place a text box in the same section of the main report as
the subreport and set its control source to:
=subreportCtlName.Report!Text BoxFromSubreport
I thought that's what I had....again, my Textbox is in the Report
Footer of the main report, is:
=[qrySpending subreport].Report!SpendingTotal
(SpendingTotal being the name of my Textbox in the Report Footer
of the subreport which contains the grand total figure I want to
bring over to Report Footer of the main report)
********************
If you want this total to display in another section of the main
report, add a control to the other section and set its control
source to:
=txtNameOf ControlFromPreviousSection
Do you mean different section from where the text box with the
total is located in the subreport; or different from where the
subreport control is located in the main report.
********************
......sorry.....I know you're getting impatient with me and all of
this; but thanks again for your help.
Bob
--
Duane Hookom
MS Access MVP
Ok, thanks, same thing here then......the name of the subreport
and the name of the control on the main report for that
subreport have the same name. I guess I'm back to square one
then.....sounds like what you're saying is that this really
"should" be working but for some reason is not......compiling
and repairing did not produce anything positive, so I guess I'll
recreate them from scratch and see if that helps.
The only thing I see in what you've said in your last response
is that the subreport control and the "TotalSpent" text box are
not in the same section of the main report. The control is in
the Detail section (because I want the spending figures to
immediately follow the planned spending line item against which
those dollars are being spent), and the TotalSpent textbox is in
the Report Footer (because I want a Grand Total of all spending,
or the total of all the individual totals throughout the
subreport, at the end of the report. I've actually tried putting
them together, though, and although it doesn't give me the
visual presentation I'm looking for, I still get the #Error.
Thanks for all your help on this,
Bob
When you add a subreport to a main report, the subreport
CONTROL has a name. This name may or may not be the name of the
subreport. Just like if you add a label or text box to a
report, the control has a name. Your subreport control name may
be the same as its Source Object property.
If you look at the Catalog report in Northwind.mdb, it has a
subreport control named "CatalogSubreport" and the Source
Object is the "Catalog Subreport".
--
Duane Hookom
MS Access MVP
Well, ok then, from your response maybe I don't know what the
control is after all. I guess this'll probably sound pretty
dumb, then, but I thought it was the name of the text box I
had created in the report footer of the subreport.....i.e.
the text box named "TotalSpent", the result of which I'm
trying to bring across to the main report. Sorry about that.
So, then, what would the control be in this scenario?
Thanks again,
Bob
message Several times you have provided the name of your subreport
and never suggested that you knew there is a difference
between a subreport control name and a subreport name.
If your subreport control has the same name as your subreport
and the subreport control and [TotalSpent] text box are in
the same section of the main report and there are records in
the subreport, then you should not be getting an error.
You may need to compile and repair & compact. If that doesn't
work, try replacing the existing subreport with a subreport
and a single text box that you can try pull a value from.
Otherwise you can start from scratch on the main or
subreport.
--
Duane Hookom
MS Access MVP
Here's all the actual names:
Main Report: rptSpendplan-to-SpendingComparison
SubReport: qrySpending subreport
Control Name in report footer of subreport: SpendingTotal
; which is: =Sum([Total])
TextBox in report footer of main report: TotalSpent ;
which is: =[qrySpending subreport].Report!SpendingTotal
Thanks,
Bob
message What is the name of the subreport control? This might not
be the same as the subreport name.
--
Duane Hookom
MS Access MVP
Subreportname is the name of the subreport.
The control in the subreport is in the report footer; the
text box referencing it in the main report is also in the
report footer.
Thanks,
Bob
message Is subreportname the name of the control or the name of
the subreport? Is the text box that references the
subreport in the same section of the report as the
subreport?
--
Duane Hookom
MS Access MVP
I am trying to create a financial report that compares
planned spending
against actual spending. Actually, I have the report
basically done--using
the main report as the planned and the subreport as the
actual. The planned
budget item appears first, followed by a transaction
report showing spending
against that particular planned line item, which I have
totalling after
each. Now, I just want to show a total at the end of
the report which
reflects the total actual spending, and I can't seem to
get it
accomplished......I keep getting #Error instead of the
figure. If I go into
the subreport, where I've created the total in a text
box which I'm trying
to draw into the main report, it runs fine when I run
the subreport....I get
the actual spending figure. And I'm pretty sure I've
got everything right
(but obviously not) in the text box on the main report:
=[subreportname].[report]![name of text box in
subreport]
Can anyone explain what is wrong and how to get a total
from a subreport
into a main report. I've looked in the KB and see that
if there's no data
you'll get this error, but I do actually have data so a
amount should be
showing up.
Any help greatly appreciated.....thanks,
Bob