Total amount in form footer

  • Thread starter Thread starter Joan
  • Start date Start date
J

Joan

Hi,
I have a subform with a form footer that I want to display the count of
records on the subform. Is there a way to do this on the form itself
without using the query the form is based on? The record source of the
subform changes depending upon what data mode the parent form is opened in.

I can make a new query from the record source of the subform to return the
totals or count but it won't let me refer to it in the sub-form footer.
There are dog and cat records displayed on the subform and in the footer I'd
like to display the total number of dogs and the total number of cats. Each
record in the subform has a field called [SpeciesCode] with either a "D" for
dog or a "C" for cat. How can I write the control source for TotalDogs so
that it displays the count of the D's in the [SpeciesCode] column of the
subreport?

Joan
 
Joan,

A couple of ways to do this. Here is one...

On your subform put a textbox for each species that you need
and mark them visible = False. This will hide them and you
can make them small enough that they won't get in the way.
Name them something like, CountDogs, CountCats, etc... For
each put in code in this fashion for the ControlSource...

For CountDogs...
=If(Me!SpeciesCode="D",1,0)

This will fill the box in with a one if that record is a
dog. If it is a "D" give me a one, else give me a zero.

Now in your subform footer's TotalDogs ControlSource put
in...

=Sum([CountDogs])

....and this will add those hidden controls up.

In a report this is much easier as you can group by Dogs,
Cats, etc... and just use a count() in the group footer. No
grouping in your subform.

Gary Miller
 
Thanks so much, Gary!

I followed your instructions and it works great! I guess I will have to
learn to be a bit more imaginative.

Thanks again,
Joan



Gary Miller said:
Joan,

A couple of ways to do this. Here is one...

On your subform put a textbox for each species that you need
and mark them visible = False. This will hide them and you
can make them small enough that they won't get in the way.
Name them something like, CountDogs, CountCats, etc... For
each put in code in this fashion for the ControlSource...

For CountDogs...
=If(Me!SpeciesCode="D",1,0)

This will fill the box in with a one if that record is a
dog. If it is a "D" give me a one, else give me a zero.

Now in your subform footer's TotalDogs ControlSource put
in...

=Sum([CountDogs])

...and this will add those hidden controls up.

In a report this is much easier as you can group by Dogs,
Cats, etc... and just use a count() in the group footer. No
grouping in your subform.

Gary Miller
Joan said:
Hi,
I have a subform with a form footer that I want to display the count of
records on the subform. Is there a way to do this on the form itself
without using the query the form is based on? The record source of the
subform changes depending upon what data mode the parent form is opened in.

I can make a new query from the record source of the subform to return the
totals or count but it won't let me refer to it in the sub-form footer.
There are dog and cat records displayed on the subform and in the footer I'd
like to display the total number of dogs and the total number of cats. Each
record in the subform has a field called [SpeciesCode] with either a "D" for
dog or a "C" for cat. How can I write the control source for TotalDogs so
that it displays the count of the D's in the [SpeciesCode] column of the
subreport?

Joan
Click to expand...
Click to expand...
 
Back
Top