Sub Report Control

[Tony Williams]

I have a main report "Active Files" with a subreport Active FilesA". The sub
report has a control called ArrestCount with a control source as
=Sum(Abs([Arrest])) where the control "Arrest" is a Yes/No field.
I have a control on my main report called "Total Arrests" where the control
references the control on my sub report and its control source is
=[Reports]![Active Files]![Active FilesA].[Report]![ArrestsCount]
The two reports are linked with the field "ACode"
This works on the first record shown on the report and shows the value of
ArrestCount but for all the other records on the report the value is shown
as #Error
Can anyone help?
Thanks
Tony

 

[Al Campagna]

Tony,

The sub report has a control called **ArrestCount** =[Reports]![Active
Files]![Active FilesA].[Report]![**ArrestsCount**]

**Notice the different names. Probably, you typed the code incorrectly?

Does your subform always show the correct ArrestCount, for all records?

Try making ArrestCount a "bound" calculated field in your subform
query.
In the sub's Query Design grid, in an empty column...
ArrestCount : =Sum(Abs([Arrest]))
Remove the calculation from your subform report's ArrestCount field,
and give it a ControlSource of ArrestCount.

Tony... I'm not sure if this is the cure, (something's a bit odd here)
but, it should be quick to implement and check out.
(aka : Can't hurt to try)
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

I have a main report "Active Files" with a subreport Active FilesA". The
sub report has a control called ArrestCount with a control source as
=Sum(Abs([Arrest])) where the control "Arrest" is a Yes/No field.
I have a control on my main report called "Total Arrests" where the
control references the control on my sub report and its control source is
=[Reports]![Active Files]![Active FilesA].[Report]![ArrestsCount]
The two reports are linked with the field "ACode"
This works on the first record shown on the report and shows the value of
ArrestCount but for all the other records on the report the value is shown
as #Error
Can anyone help?
Thanks
Tony

 

[Marshall Barton]

I have a main report "Active Files" with a subreport Active FilesA". The sub
report has a control called ArrestCount with a control source as
=Sum(Abs([Arrest])) where the control "Arrest" is a Yes/No field.
I have a control on my main report called "Total Arrests" where the control
references the control on my sub report and its control source is
=[Reports]![Active Files]![Active FilesA].[Report]![ArrestsCount]
The two reports are linked with the field "ACode"
This works on the first record shown on the report and shows the value of
ArrestCount but for all the other records on the report the value is shown
as #Error

One cause of #Error is when the subreport has no records (so
there is nothing to reference. If that's the cause of your
problem, you can avoid it by using this reference instead:

=IIf([Active FilesA].Report.HasData, [Active
FilesA].Report!ArrestsCount, 0)

 

[Tony Williams]

Thanks Al
Sorry typo in the message it should be AresstCount. I did as you suggest and
made the control a bound control. When I run the sub report on its own it
gives me the correct data. When it's run as part of the main report it shows
the correct data for only some of the records, for the others I still get
#Error. That's odd? those where it shows as #Error should have data and not
always "0's"
Any ideas?
Thanks
Tony

Tony,

The sub report has a control called **ArrestCount** =[Reports]![Active
Files]![Active FilesA].[Report]![**ArrestsCount**]

**Notice the different names. Probably, you typed the code
incorrectly?

Does your subform always show the correct ArrestCount, for all
records?

Try making ArrestCount a "bound" calculated field in your subform
query.
In the sub's Query Design grid, in an empty column...
ArrestCount : =Sum(Abs([Arrest]))
Remove the calculation from your subform report's ArrestCount field,
and give it a ControlSource of ArrestCount.

Tony... I'm not sure if this is the cure, (something's a bit odd
here) but, it should be quick to implement and check out.
(aka : Can't hurt to try)
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

I have a main report "Active Files" with a subreport Active FilesA". The
sub report has a control called ArrestCount with a control source as
=Sum(Abs([Arrest])) where the control "Arrest" is a Yes/No field.
I have a control on my main report called "Total Arrests" where the
control references the control on my sub report and its control source is
=[Reports]![Active Files]![Active FilesA].[Report]![ArrestsCount]
The two reports are linked with the field "ACode"
This works on the first record shown on the report and shows the value of
ArrestCount but for all the other records on the report the value is
shown as #Error
Can anyone help?
Thanks
Tony

 

[Tony Williams]

Hi again Marsh. Tried that and got 0's where there should have been data and
#Error where there wasn't ?????
Any ideas?
Thanks
Tony

I have a main report "Active Files" with a subreport Active FilesA". The
sub
report has a control called ArrestCount with a control source as
=Sum(Abs([Arrest])) where the control "Arrest" is a Yes/No field.
I have a control on my main report called "Total Arrests" where the
control
references the control on my sub report and its control source is
=[Reports]![Active Files]![Active FilesA].[Report]![ArrestsCount]
The two reports are linked with the field "ACode"
This works on the first record shown on the report and shows the value of
ArrestCount but for all the other records on the report the value is shown
as #Error

One cause of #Error is when the subreport has no records (so
there is nothing to reference. If that's the cause of your
problem, you can avoid it by using this reference instead:

=IIf([Active FilesA].Report.HasData, [Active
FilesA].Report!ArrestsCount, 0)

 

[Marshall Barton]

Double check your main form expression to make sure you
translated the line wrapped expression I posted correctly.

Make everything in the subreport visible so you can see all
of the data and the result of the sum before the main report
gets involved. I suspect that there is something wrong in
the subreport calculation (e.g. the yes/no field is really a
Text type field or Acode is a text box instead of a record
source field.
--
Marsh
MVP [MS Access]

Tried that and got 0's where there should have been data and
#Error where there wasn't ?????

I have a main report "Active Files" with a subreport Active FilesA". The
sub
report has a control called ArrestCount with a control source as
=Sum(Abs([Arrest])) where the control "Arrest" is a Yes/No field.
I have a control on my main report called "Total Arrests" where the
control
references the control on my sub report and its control source is
=[Reports]![Active Files]![Active FilesA].[Report]![ArrestsCount]
The two reports are linked with the field "ACode"
This works on the first record shown on the report and shows the value of
ArrestCount but for all the other records on the report the value is shown
as #Error

One cause of #Error is when the subreport has no records (so
there is nothing to reference. If that's the cause of your
problem, you can avoid it by using this reference instead:

=IIf([Active FilesA].Report.HasData, [Active
FilesA].Report!ArrestsCount, 0)

 

[Tony Williams]

I've just run the reports independently and there's something wrong. There
are records on my subreport that don't appear on my main report and should
do. I'll check the field types as well. I think it's back to square one and
check all the relationships and queries from the ground up.
Thanks for you help so far.
Tony

Double check your main form expression to make sure you
translated the line wrapped expression I posted correctly.

Make everything in the subreport visible so you can see all
of the data and the result of the sum before the main report
gets involved. I suspect that there is something wrong in
the subreport calculation (e.g. the yes/no field is really a
Text type field or Acode is a text box instead of a record
source field.
--
Marsh
MVP [MS Access]

Tried that and got 0's where there should have been data and
#Error where there wasn't ?????

Tony Williams wrote:

I have a main report "Active Files" with a subreport Active FilesA". The
sub
report has a control called ArrestCount with a control source as
=Sum(Abs([Arrest])) where the control "Arrest" is a Yes/No field.
I have a control on my main report called "Total Arrests" where the
control
references the control on my sub report and its control source is
=[Reports]![Active Files]![Active FilesA].[Report]![ArrestsCount]
The two reports are linked with the field "ACode"
This works on the first record shown on the report and shows the value
of
ArrestCount but for all the other records on the report the value is
shown
as #Error


One cause of #Error is when the subreport has no records (so
there is nothing to reference. If that's the cause of your
problem, you can avoid it by using this reference instead:

=IIf([Active FilesA].Report.HasData, [Active
FilesA].Report!ArrestsCount, 0)

 

[Tony Williams]

Thanks to Marsh and Al they pointed me in the right direction. My problem
was with the subreports pulling data for records where there was a Null
value and so I amended the query to get rid of null value records and it
works fine now. Phew!
Thanks again
Tony

 

[Marshall Barton]

Don't forget to double check the Link Master/Child Fields
properties too.
--
Marsh
MVP [MS Access]

I've just run the reports independently and there's something wrong. There
are records on my subreport that don't appear on my main report and should
do. I'll check the field types as well. I think it's back to square one and
check all the relationships and queries from the ground up.
Thanks for you help so far.
Tony

Double check your main form expression to make sure you
translated the line wrapped expression I posted correctly.

Make everything in the subreport visible so you can see all
of the data and the result of the sum before the main report
gets involved. I suspect that there is something wrong in
the subreport calculation (e.g. the yes/no field is really a
Text type field or Acode is a text box instead of a record
source field.

Tried that and got 0's where there should have been data and
#Error where there wasn't ?????

Tony Williams wrote:

I have a main report "Active Files" with a subreport Active FilesA". The
sub
report has a control called ArrestCount with a control source as
=Sum(Abs([Arrest])) where the control "Arrest" is a Yes/No field.
I have a control on my main report called "Total Arrests" where the
control
references the control on my sub report and its control source is
=[Reports]![Active Files]![Active FilesA].[Report]![ArrestsCount]
The two reports are linked with the field "ACode"
This works on the first record shown on the report and shows the value
of
ArrestCount but for all the other records on the report the value is
shown
as #Error


One cause of #Error is when the subreport has no records (so
there is nothing to reference. If that's the cause of your
problem, you can avoid it by using this reference instead:

=IIf([Active FilesA].Report.HasData, [Active
FilesA].Report!ArrestsCount, 0)