Counting the number of group headers

I am trying to count the number of group headers in my report as opposed to the total number of records in all the groups.

In the detail section for each group I have a text box (txtBox1) with data source = 1 and running sum set to group overall. In the report footer I have another text box (txtBox2)with its datasource = txtBox1.

When I view the report in Report View, I get the correct result (eg a value of 3 in txtBox2 ie 3 groups with a total of 6 records).

When I view the same report in Print preview mode, I get a wrong value of 1 in txtBox2.

Where am I going wrong?

gorsoft@hotmail.com wrote:
> I am trying to count the number of group headers in my report as
> opposed to the total number of records in all the groups.
>
> In the detail section for each group I have a text box (txtBox1) with
> data source = 1 and running sum set to group overall. In the report
> footer I have another text box (txtBox2)with its datasource =
> txtBox1.
>
> When I view the report in Report View, I get the correct result (eg a
> value of 3 in txtBox2 ie 3 groups with a total of 6 records).
>
> When I view the same report in Print preview mode, I get a wrong
> value of 1 in txtBox2.
>
> Where am I going wrong?

As far as I know nothing. That is how preview works, it isn't guaranteed to
have the correct data just to show some of it. If this isn't the case
somebody stop me now.....

David

First, this seems like a rather strange thing. I mean, when you create a
report, you create the groups. So wouldn't you know the number of group
headers by looking at the report in design mode? I'm not understanding
the purpose or reason for this.

That being said, instead of using text boxes with a running total, why
not use each group's On Format event to add 1 to a global variable?

Then create a global function called (say) GetHeaderCount() which does
nothing but returns the value of the global variable. So if the global
variable is called lngReportHeaders, then the function would be:

Public Function GetHeaderCount() As Long

GetHeaderCount = lngReportHeaders

End Function

Then in your footer text box, just set its control source to:
=GetHeaderCount()

So, that should work. Just remember, in the Report Open event, be sure
to set lngReportHeaders = 0, to reset the variable each time the report
is opened.

HTH.

Neil



On 7/17/2012 10:09 AM, gorsoft@hotmail.com wrote:
> I am trying to count the number of group headers in my report as opposed to the total number of records in all the groups.
>
> In the detail section for each group I have a text box (txtBox1) with data source = 1 and running sum set to group overall. In the report footer I have another text box (txtBox2)with its datasource = txtBox1.
>
> When I view the report in Report View, I get the correct result (eg a value of 3 in txtBox2 ie 3 groups with a total of 6 records).
>
> When I view the same report in Print preview mode, I get a wrong value of 1 in txtBox2.
>
> Where am I going wrong?
>

Hi David,
Thanks for the reply but you can't be right. When I say "Print preview", Imean in effect when I print the report. That has to give the correct result but it does not. It is the other option, "View report" (that's the option with little green binder displayed) which gives me the right result.

On Wednesday, July 18, 2012 8:12:00 AM UTC+1, Neil wrote:
> First, this seems like a rather strange thing. I mean, when you create a
> report, you create the groups. So wouldn't you know the number of group
> headers by looking at the report in design mode? I'm not understanding
> the purpose or reason for this.
>
> That being said, instead of using text boxes with a running total, why
> not use each group's On Format event to add 1 to a global variable?
>
> Then create a global function called (say) GetHeaderCount() which does
> nothing but returns the value of the global variable. So if the global
> variable is called lngReportHeaders, then the function would be:
>
> Public Function GetHeaderCount() As Long
>
> GetHeaderCount = lngReportHeaders
>
> End Function
>
> Then in your footer text box, just set its control source to:
> =GetHeaderCount()
>
> So, that should work. Just remember, in the Report Open event, be sure
> to set lngReportHeaders = 0, to reset the variable each time the report
> is opened.
>
> HTH.
>
> Neil
>
>
>
> On 7/17/2012 10:09 AM, wrote:
> > I am trying to count the number of group headers in my report as opposed to the total number of records in all the groups.
> >
> > In the detail section for each group I have a text box (txtBox1) with data source = 1 and running sum set to group overall. In the report footer I have another text box (txtBox2)with its datasource = txtBox1.
> >
> > When I view the report in Report View, I get the correct result (eg a value of 3 in txtBox2 ie 3 groups with a total of 6 records).
> >
> > When I view the same report in Print preview mode, I get a wrong value of 1 in txtBox2.
> >
> > Where am I going wrong?
> >

Thanks for joining the discussion Neil.

I don't think my request is at all strange - I think you have misunderstoodwhat I was asking. Of course I am not interested in the number of group headers at design stage, but in the number of records that appear as the subject of group headers when the report is output (either in preview mode or in print).

For example, let's say I have a report which shows for each piece of classical music, the number of recordings of that piece. The group header will display the name of the piece and the detail will list all of the recordingsof that piece. I want to know how many classical pieces are in the report, not the total number of recordings.

Anyway, to your proposed solution. Unfortunately, it gives me incorrect results which don't seem to relate to anything. However, when I place the function in the OnPrint event (rather than the OnFormat event that you suggested), it does work and I do get the right result...but only when I actually print the report, not in print preview.

That is not ideal but at least I can get the result, so unless anyone has any other suggestions, I will go for that. Thanks.

On 7/18/2012 7:38 AM, Gina wrote:
> On Wednesday, July 18, 2012 8:12:00 AM UTC+1, Neil wrote:
>> First, this seems like a rather strange thing. I mean, when you create a
>> report, you create the groups. So wouldn't you know the number of group
>> headers by looking at the report in design mode? I'm not understanding
>> the purpose or reason for this.
>>
>> That being said, instead of using text boxes with a running total, why
>> not use each group's On Format event to add 1 to a global variable?
>>
>> Then create a global function called (say) GetHeaderCount() which does
>> nothing but returns the value of the global variable. So if the global
>> variable is called lngReportHeaders, then the function would be:
>>
>> Public Function GetHeaderCount() As Long
>>
>> GetHeaderCount = lngReportHeaders
>>
>> End Function
>>
>> Then in your footer text box, just set its control source to:
>> =GetHeaderCount()
>>
>> So, that should work. Just remember, in the Report Open event, be sure
>> to set lngReportHeaders = 0, to reset the variable each time the report
>> is opened.
>>
>> HTH.
>>
>> Neil
>>
>>
>>
>> On 7/17/2012 10:09 AM, wrote:
>> > I am trying to count the number of group headers in my report as opposed to the total number of records in all the groups.
>> >
>> > In the detail section for each group I have a text box (txtBox1) with data source = 1 and running sum set to group overall. In the report footer I have another text box (txtBox2)with its datasource = txtBox1.
>> >
>> > When I view the report in Report View, I get the correct result (eg a value of 3 in txtBox2 ie 3 groups with a total of 6 records).
>> >
>> > When I view the same report in Print preview mode, I get a wrong value of 1 in txtBox2.
>> >
>> > Where am I going wrong?
>> >
>
> Thanks for joining the discussion Neil.
>
> I don't think my request is at all strange - I think you have misunderstood what I was asking. Of course I am not interested in the number of group headers at design stage, but in the number of records that appear as the subject of group headers when the report is output (either in preview mode or in print).
>
> For example, let's say I have a report which shows for each piece of classical music, the number of recordings of that piece. The group header will display the name of the piece and the detail will list all of the recordings of that piece. I want to know how many classical pieces are in the report, not the total number of recordings.
>
> Anyway, to your proposed solution. Unfortunately, it gives me incorrect results which don't seem to relate to anything. However, when I place the function in the OnPrint event (rather than the OnFormat event that you suggested), it does work and I do get the right result...but only when I actually print the report, not in print preview.
>
> That is not ideal but at least I can get the result, so unless anyone has any other suggestions, I will go for that. Thanks.
>

I just tested it out, and it works fine using the OnFormat event, as I
originally said. But it will only give the correct results after the
last Format event. Thus, I put the text box with the call to the
function that returns the global variable value in the report footer,
which only exists at the end of the report. In that place, the value is
correct.

You could, of course, put it in the page footer. But the value would
only be correct on the last page. Better just to put it in the report
footer.

Neil

Thanks Neil, got it working now. I notice it only works if I place the GetHeaderCount text box in the report footer. It would be nice if it could also put the total for each group in its group footer but I guess I should be happy with what I've got.<grn>

On Thu, 19 Jul 2012 04:40:14 -0700 (PDT) gorsoft@hotmail.com wrote:

> Thanks Neil, got it working now. I notice it only works if I place
> the GetHeaderCount text box in the report footer. It would be nice
> if it could also put the total for each group in its group footer but
> I guess I should be happy with what I've got.<grn>

You also might consider the group header's Retreat event. Sometimes a
report section is formatted twice, for instance if Access notices that
the section will not fit on the page and therefore has to be printed
on the following page. In that case, there will be two Format events
with one Retreat event between them. Without subtracting 1 at Retreat,
your result will be too big by 1.

Greetings from Austria, Georg

On 7/19/2012 6:40 AM, gorsoft@hotmail.com wrote:
> Thanks Neil, got it working now. I notice it only works if I place the GetHeaderCount text box in the report footer. It would be nice if it could also put the total for each group in its group footer but I guess I should be happy with what I've got.<grn>
>
Well, you can do a separate total for each group, and still put all the
totals in the report footer (each in their own text box). The main thing
is that the totals are calculated separately, but there's no reason they
can't be in the report footer.