How to refer to main report from a subreport?

[Wim]

I am trying to create an unbound report with several subreports on it, each
with its own record source.
On the main report there are textboxes with dates - txtBegin and txtEnd -
that are filled in when the report is opened from a dialog box.
I need to base certain calculations in the subforms on a number that is
presently displayed in another textbox (txtNumberOfCases) on the main form
with record source:
=(DCount("ID";"nameofquery";"[DateIn] Between [txtBegin] And [txtEnd]"))
Up to here everything works fine.
Now I need to have this "number of cases" available in some of the
subreports and that is where I get lost.
I tried creating a text box in a subform with recordsource:
=Reports![nameofmainreport]![txtNumberOfCases]
but it returns 0.
However, if I put the same text box with the exact same record source on the
main report, it returns the correct number.
Who helps me solving the mystery?

 

[Evi]

Try

=[Parent].[NumberOfCases]

Evi

 

[Wim]

Hi Marsh,

No, the txtNumberOfCases text box was actually in the Detail section of the
main report. But I moved it to the report header and that didn't make any
difference. A text box in the detail section of the main report with
=Reports![nameofmainreport]![txtNumberOfCases]
in its control source gives the correct result while a text box in the
subreport (detail or footer section) with exactly the same control source
returns 0.

As for your second suggestion, I tried it out with a text box in the Detail
Section and with one in the Report Footer, but it keeps giving me "0" as a
result...

I am trying to create an unbound report with several subreports on it, each
with its own record source.
On the main report there are textboxes with dates - txtBegin and txtEnd -
that are filled in when the report is opened from a dialog box.
I need to base certain calculations in the subforms on a number that is
presently displayed in another textbox (txtNumberOfCases) on the main form
with record source:
=(DCount("ID";"nameofquery";"[DateIn] Between [txtBegin] And [txtEnd]"))
Up to here everything works fine.
Now I need to have this "number of cases" available in some of the
subreports and that is where I get lost.
I tried creating a text box in a subform with recordsource:
=Reports![nameofmainreport]![txtNumberOfCases]
but it returns 0.
However, if I put the same text box with the exact same record source on the
main report, it returns the correct number.

What section is the txtNumberOfCases in? It should probably
be in the main report's Report Header section.

If that's not the problem, remove the text box's expression
and try using code in the Format event of the subreport
section that contains the text box:
Me.thetextbox = Parent.txtNumberOfCases

 

[Wim]

Tried that. Is still returns "0".

Try

=[Parent].[NumberOfCases]

Evi

I am trying to create an unbound report with several subreports on it, each
with its own record source.
On the main report there are textboxes with dates - txtBegin and txtEnd -
that are filled in when the report is opened from a dialog box.
I need to base certain calculations in the subforms on a number that is
presently displayed in another textbox (txtNumberOfCases) on the main form
with record source:
=(DCount("ID";"nameofquery";"[DateIn] Between [txtBegin] And [txtEnd]"))
Up to here everything works fine.
Now I need to have this "number of cases" available in some of the
subreports and that is where I get lost.
I tried creating a text box in a subform with recordsource:
=Reports![nameofmainreport]![txtNumberOfCases]
but it returns 0.
However, if I put the same text box with the exact same record source on the
main report, it returns the correct number.
Who helps me solving the mystery?

 

[Wim]

I just noticed something else. The txtNumberOfCases text box, when placed in
the report header, returns the right result only on the first page of the
report. On the second and following pages it returns "0". Does this help in
identifying the problem?

No, the txtNumberOfCases text box was actually in the Detail section of the
main report. But I moved it to the report header and that didn't make any
difference. A text box in the detail section of the main report with
=Reports![nameofmainreport]![txtNumberOfCases]
in its control source gives the correct result while a text box in the
subreport (detail or footer section) with exactly the same control source
returns 0.

As for your second suggestion, I tried it out with a text box in the Detail
Section and with one in the Report Footer, but it keeps giving me "0" as a
result...

I don't know what else to say. That arrangement worked fine
in my tests.

 

[Evi]

Sorry, I see that your text box in the main report is called
txtNumberOfCases so you should use

=[Parent].[txtNumberOfCases]

in your subform's text box (type the word Parent, not the name of your main
report)

(though you probably realised that already)

What surprises me is that you are getting a 0 rather than an error message.
Are there, by any chance, any Nulls in the data that txtNumberOfCases is
calculating?

Just to test,
Try the exercise again putting a new textbox in the main report Header.
Type

="Hi"

into it

Using Properties, on the Other tab, name it txtHi

In your subreport's Detail section

add a text box and into it type

=[Parent].[txtHi]

does that give the expected result?

Evi

Tried that. Is still returns "0".

Try

=[Parent].[NumberOfCases]

Evi

I am trying to create an unbound report with several subreports on it, each
with its own record source.
On the main report there are textboxes with dates - txtBegin and txtEnd -
that are filled in when the report is opened from a dialog box.
I need to base certain calculations in the subforms on a number that is
presently displayed in another textbox (txtNumberOfCases) on the main form
with record source:
=(DCount("ID";"nameofquery";"[DateIn] Between [txtBegin] And [txtEnd]"))
Up to here everything works fine.
Now I need to have this "number of cases" available in some of the
subreports and that is where I get lost.
I tried creating a text box in a subform with recordsource:
=Reports![nameofmainreport]![txtNumberOfCases]
but it returns 0.
However, if I put the same text box with the exact same record source

on
the

main report, it returns the correct number.
Who helps me solving the mystery?

 

[Wim]

You're right, Marsh, sorry for being so imprecise.
What I should have said is: when putting the text box in the page header, it
gives the right result on the first page, but "0" from the second page onward.

On the main report - detail section - I have two text boxes, called txtBegin
and txtEnd, with control sources:
=Nz(Forms!forRepDat3!txtRepIn;#12-01-2007#)
and
=Nz(Forms!forRepDat3!txtRepOut;Date())

The form forRepDat3 is a dialog box, used to determine the beginning and end
dates for some of the subreports and to open the report in Print Preview. I
want these dates printed in the beginning of the report, that's what I
created the text boxes for in the first place.

In the main report's header there is a textbox, txtNumberOfCases which was
based on a query. But the problem is not in the query. To rule that out, I
tried a DCount directly on a table. The control source of txtNumberofCases
now reads:
=(DCount("ID";"tabEntrada";"[Rec_Data] Between [txtBegin] And [txtEnd]"))
ID is the primary key in table tabEntrada, Rec_Data is a date field.

Up to this point everything is working as it should: the txtNumberOfCases
gives the right result.

In the detail section of the main report there is a subreport, which is also
working fine, except for the following. In its report footer I have an
unbound text box, called NumCases. In the Format event of the subreport's
footer I have the following code:

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Me.NumCases = Parent.txtNumberOfCases
End Sub

This textbox, NumCases, always returns 0, instead of the value
txtNumberOfCases shows in the report header.

I need the "number of cases" for certain calculations in the subreport, but
I can't obtain it directly from the Record Source of the subreport.

PS. The semicolon is defined as list separator in my Windows regional
settings.

That doesn't make sense to me. The report header section
only appears at the beginning of a report.

In reviewing what you posted earlier, I don't think the
DCount expression you posted for txtNumberOfCases's will
work, but you said it does work (sometimes?) so something
here is murky. Let's review what you have in more precise
terms (please use Copy/Paste to post VBA code, text box
expressions and queries in SQL view). Be sure to use exact
names of your form controls, table fields and
queries/tables.
--
Marsh
MVP [MS Access]

I just noticed something else. The txtNumberOfCases text box, when placed in
the report header, returns the right result only on the first page of the
report. On the second and following pages it returns "0". Does this help in
identifying the problem?

Wim wrote:
No, the txtNumberOfCases text box was actually in the Detail section of the
main report. But I moved it to the report header and that didn't make any
difference. A text box in the detail section of the main report with
=Reports![nameofmainreport]![txtNumberOfCases]
in its control source gives the correct result while a text box in the
subreport (detail or footer section) with exactly the same control source
returns 0.

As for your second suggestion, I tried it out with a text box in the Detail
Section and with one in the Report Footer, but it keeps giving me "0" as a
result...


I don't know what else to say. That arrangement worked fine
in my tests.

 

[Wim]

Evi, thanks for trying to help me!

As for your first point, yes I had realised that.
Second, no, because what is counted is a primary key field, which is created
automatically (not Autonumber, but programmatically).
Third. I tried your suggestion and it works fine. With the text box in the
detail or in the footer section of the subreport, I get "Hi" as a result!

Sorry, I see that your text box in the main report is called
txtNumberOfCases so you should use

=[Parent].[txtNumberOfCases]

in your subform's text box (type the word Parent, not the name of your main
report)

(though you probably realised that already)

What surprises me is that you are getting a 0 rather than an error message.
Are there, by any chance, any Nulls in the data that txtNumberOfCases is
calculating?

Just to test,
Try the exercise again putting a new textbox in the main report Header.
Type

="Hi"

into it

Using Properties, on the Other tab, name it txtHi

In your subreport's Detail section

add a text box and into it type

=[Parent].[txtHi]

does that give the expected result?

Evi

Tried that. Is still returns "0".

Try

=[Parent].[NumberOfCases]

Evi
I am trying to create an unbound report with several subreports on it,
each
with its own record source.
On the main report there are textboxes with dates - txtBegin and txtEnd -
that are filled in when the report is opened from a dialog box.
I need to base certain calculations in the subforms on a number that is
presently displayed in another textbox (txtNumberOfCases) on the main form
with record source:
=(DCount("ID";"nameofquery";"[DateIn] Between [txtBegin] And [txtEnd]"))
Up to here everything works fine.
Now I need to have this "number of cases" available in some of the
subreports and that is where I get lost.
I tried creating a text box in a subform with recordsource:
=Reports![nameofmainreport]![txtNumberOfCases]
but it returns 0.
However, if I put the same text box with the exact same record source on
the
main report, it returns the correct number.
Who helps me solving the mystery?

 

[Wim]

Yes, the form closes itself after opening the report.
And you are right: if I allow it to remain open, the problem is solved!
But that implies I have to change it to a non-pop form.
So, does that mean I cannot use pop-up dialog boxes in this case?

As for the second part of your reply, yes,
"[Rec_Data] Between [txtBegin] And [txtEnd]"
works fine. My first try was
"[Rec_Data] Between " & [txtBegin] & " And " & [txtEnd]
but that returned 0.
"[Rec_Data] Between " & Me.txtBegin & " And " & Me.txtEnd
is changed by Access into
"[Rec_Data] Between " & [Me].[txtBegin] & " And " & [Me].[txtEnd]
and returns #Name?
I did not use the Format function. Do you think I should?

Thanks for your help!

Does the form remain open until after the report is closed?
I think(?) your problem might be explainable if the form
closes itself after opening the report.

I also don't see how that DCount can work. IME, I would
expect that the third argument would have to be either:
"[Rec_Data] Between Reports![name of report].txtBegin
And Reports![name of report].txtEnd"
or
"[Rec_Data] Between " & Format(Me.txtBegin, "\#yyyy-m-d\#)
& " And " & Format(Me.txtEnd, "\#yyyy-m-d\#)
but maybe I'm learning something new here???
--
Marsh
MVP [MS Access]

You're right, Marsh, sorry for being so imprecise.
What I should have said is: when putting the text box in the page header, it
gives the right result on the first page, but "0" from the second page onward.

On the main report - detail section - I have two text boxes, called txtBegin
and txtEnd, with control sources:
=Nz(Forms!forRepDat3!txtRepIn;#12-01-2007#)
and
=Nz(Forms!forRepDat3!txtRepOut;Date())

The form forRepDat3 is a dialog box, used to determine the beginning and end
dates for some of the subreports and to open the report in Print Preview. I
want these dates printed in the beginning of the report, that's what I
created the text boxes for in the first place.

In the main report's header there is a textbox, txtNumberOfCases which was
based on a query. But the problem is not in the query. To rule that out, I
tried a DCount directly on a table. The control source of txtNumberofCases
now reads:
=(DCount("ID";"tabEntrada";"[Rec_Data] Between [txtBegin] And [txtEnd]"))
ID is the primary key in table tabEntrada, Rec_Data is a date field.

Up to this point everything is working as it should: the txtNumberOfCases
gives the right result.

In the detail section of the main report there is a subreport, which is also
working fine, except for the following. In its report footer I have an
unbound text box, called NumCases. In the Format event of the subreport's
footer I have the following code:

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Me.NumCases = Parent.txtNumberOfCases
End Sub

This textbox, NumCases, always returns 0, instead of the value
txtNumberOfCases shows in the report header.

I need the "number of cases" for certain calculations in the subreport, but
I can't obtain it directly from the Record Source of the subreport.

PS. The semicolon is defined as list separator in my Windows regional
settings.

That doesn't make sense to me. The report header section
only appears at the beginning of a report.

In reviewing what you posted earlier, I don't think the
DCount expression you posted for txtNumberOfCases's will
work, but you said it does work (sometimes?) so something
here is murky. Let's review what you have in more precise
terms (please use Copy/Paste to post VBA code, text box
expressions and queries in SQL view). Be sure to use exact
names of your form controls, table fields and
queries/tables.
--
Marsh
MVP [MS Access]


Wim wrote:
I just noticed something else. The txtNumberOfCases text box, when placed in
the report header, returns the right result only on the first page of the
report. On the second and following pages it returns "0". Does this help in
identifying the problem?

:

Wim wrote:
No, the txtNumberOfCases text box was actually in the Detail section of the
main report. But I moved it to the report header and that didn't make any
difference. A text box in the detail section of the main report with
=Reports![nameofmainreport]![txtNumberOfCases]
in its control source gives the correct result while a text box in the
subreport (detail or footer section) with exactly the same control source
returns 0.

As for your second suggestion, I tried it out with a text box in the Detail
Section and with one in the Report Footer, but it keeps giving me "0" as a
result...


I don't know what else to say. That arrangement worked fine
in my tests.

 

[John Spencer]

I think you should be able to change the visible property of the form to false
in the code that opens the report or in the Open Event of the report. In the
close event of the report, you can choose to close the form or to make the
form visible again.

And you must enclose the txtBegin and txtEnd in # delimiters if they are being
concatenated into the string. Marshall's method of using the format function
is an excellent way of doing this.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Yes, the form closes itself after opening the report.
And you are right: if I allow it to remain open, the problem is solved!
But that implies I have to change it to a non-pop form.
So, does that mean I cannot use pop-up dialog boxes in this case?

As for the second part of your reply, yes,
"[Rec_Data] Between [txtBegin] And [txtEnd]"
works fine. My first try was
"[Rec_Data] Between " & [txtBegin] & " And " & [txtEnd]
but that returned 0.
"[Rec_Data] Between " & Me.txtBegin & " And " & Me.txtEnd
is changed by Access into
"[Rec_Data] Between " & [Me].[txtBegin] & " And " & [Me].[txtEnd]
and returns #Name?
I did not use the Format function. Do you think I should?

Thanks for your help!

Does the form remain open until after the report is closed?
I think(?) your problem might be explainable if the form
closes itself after opening the report.

I also don't see how that DCount can work. IME, I would
expect that the third argument would have to be either:
"[Rec_Data] Between Reports![name of report].txtBegin
And Reports![name of report].txtEnd"
or
"[Rec_Data] Between " & Format(Me.txtBegin, "\#yyyy-m-d\#)
& " And " & Format(Me.txtEnd, "\#yyyy-m-d\#)
but maybe I'm learning something new here???
--
Marsh
MVP [MS Access]

You're right, Marsh, sorry for being so imprecise.
What I should have said is: when putting the text box in the page header, it
gives the right result on the first page, but "0" from the second page onward.

On the main report - detail section - I have two text boxes, called txtBegin
and txtEnd, with control sources:
=Nz(Forms!forRepDat3!txtRepIn;#12-01-2007#)
and
=Nz(Forms!forRepDat3!txtRepOut;Date())

The form forRepDat3 is a dialog box, used to determine the beginning and end
dates for some of the subreports and to open the report in Print Preview. I
want these dates printed in the beginning of the report, that's what I
created the text boxes for in the first place.

In the main report's header there is a textbox, txtNumberOfCases which was
based on a query. But the problem is not in the query. To rule that out, I
tried a DCount directly on a table. The control source of txtNumberofCases
now reads:
=(DCount("ID";"tabEntrada";"[Rec_Data] Between [txtBegin] And [txtEnd]"))
ID is the primary key in table tabEntrada, Rec_Data is a date field.

Up to this point everything is working as it should: the txtNumberOfCases
gives the right result.

In the detail section of the main report there is a subreport, which is also
working fine, except for the following. In its report footer I have an
unbound text box, called NumCases. In the Format event of the subreport's
footer I have the following code:

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Me.NumCases = Parent.txtNumberOfCases
End Sub

This textbox, NumCases, always returns 0, instead of the value
txtNumberOfCases shows in the report header.

I need the "number of cases" for certain calculations in the subreport, but
I can't obtain it directly from the Record Source of the subreport.

PS. The semicolon is defined as list separator in my Windows regional
settings.

:

That doesn't make sense to me. The report header section
only appears at the beginning of a report.

In reviewing what you posted earlier, I don't think the
DCount expression you posted for txtNumberOfCases's will
work, but you said it does work (sometimes?) so something
here is murky. Let's review what you have in more precise
terms (please use Copy/Paste to post VBA code, text box
expressions and queries in SQL view). Be sure to use exact
names of your form controls, table fields and
queries/tables.
--
Marsh
MVP [MS Access]


Wim wrote:
I just noticed something else. The txtNumberOfCases text box, when placed in
the report header, returns the right result only on the first page of the
report. On the second and following pages it returns "0". Does this help in
identifying the problem?

:

Wim wrote:
No, the txtNumberOfCases text box was actually in the Detail section of the
main report. But I moved it to the report header and that didn't make any
difference. A text box in the detail section of the main report with
=Reports![nameofmainreport]![txtNumberOfCases]
in its control source gives the correct result while a text box in the
subreport (detail or footer section) with exactly the same control source
returns 0.

As for your second suggestion, I tried it out with a text box in the Detail
Section and with one in the Report Footer, but it keeps giving me "0" as a
result...

I don't know what else to say. That arrangement worked fine
in my tests.

 

[Wim]

Thanks John and Marsh, for this suggestion. Simple, but effective!
Thanks for all your help. I learned a lot.

What John said about making the form invisible!

Well, I have never used a simple control name reference in a
domain aggregate function like that so I am surprised that
it works. Now I am wondering how Access handles extending
the name space to include local control names?? I'll need
some time to experiment with this in various contexts.
--
Marsh
MVP [MS Access]

Yes, the form closes itself after opening the report.
And you are right: if I allow it to remain open, the problem is solved!
But that implies I have to change it to a non-pop form.
So, does that mean I cannot use pop-up dialog boxes in this case?

As for the second part of your reply, yes,
"[Rec_Data] Between [txtBegin] And [txtEnd]"
works fine. My first try was
"[Rec_Data] Between " & [txtBegin] & " And " & [txtEnd]
but that returned 0.
"[Rec_Data] Between " & Me.txtBegin & " And " & Me.txtEnd
is changed by Access into
"[Rec_Data] Between " & [Me].[txtBegin] & " And " & [Me].[txtEnd]
and returns #Name?
I did not use the Format function. Do you think I should?

Does the form remain open until after the report is closed?
I think(?) your problem might be explainable if the form
closes itself after opening the report.

I also don't see how that DCount can work. IME, I would
expect that the third argument would have to be either:
"[Rec_Data] Between Reports![name of report].txtBegin
And Reports![name of report].txtEnd"
or
"[Rec_Data] Between " & Format(Me.txtBegin, "\#yyyy-m-d\#)
& " And " & Format(Me.txtEnd, "\#yyyy-m-d\#)
but maybe I'm learning something new here???