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
