Record Count

[HomeALone]

I created a textbox Name txtCount in the detail section of a report. The data Control source is set to "=1" and Run Sum = "Over all" to count the number of records of a grouped records. This runs OK

How do I retrieve the final count value from txtCount to be used in the ReportHeader to show total number of grouped records? This value must be resided in somewhere of the report. Anyone knows its secret place?

 

[PaulBricker]

It's magic spot is in the Report Footer. You will get the final count there. Is there some other place you would rather see it?

Paul

 

[HomeALone]

Yeah, I would like to put in the Report Header along with a couple strings of messages. I want to keep it simple to avoid using a ADO/DAO recordset. There must be a counter somewhere storing this value. Is this another secret Bill Gates keeps from us?

 

[PaulBricker]

Do this. Put a textbox in the Report Footer. Set it's Control Source to = txtCount. Set it's Visible Property to No. Add a textbox to the Report Header. In the Format Event for the Report Footer put this code (change the textbox names where appropriate).

Dim i as Integer
i = Text1 'The textbox in the Report Footer
Text2 = i 'Text2 is the Textbox in the Report Header

That should do it.

Paul

 

[HomeALone]

Hey Paul,

This is good, it works (at least capture the count) but inconsistently to display on the report header. I think it has to do when the event gets triggered but I don't know the sequence. It looks like if I have a long report (more than one page) the event is not triggered until you reach the bottom of the report. Sometimes it shows the count from the last one, very strange . . . Can someone give me a hint on how to debug this? Is this the best place to trigger the event? Thanks!

 

[CosmoKramer]

If you're simply looking for a total count of records in the report, set the control source of your text box in the report header to:

=Count(*)

 

[PaulBricker]

Duh! I can think of at least twice on this forum that I've used that expression. Glad it's Friday. That should do it Cosmo

Paul

 

[HomeALone]

I apology that I first described my situation incorrectly, please let me restate my situation.

My report has a queries contains 2 linked tables. The textbox named txtCount was created in the Field Header, not in the Detail Section as first mentioned. A grouped record may return its related records in the detail section. I am trying to count only the grouped records listed in the Field Header.

The count(*) function will return the total of all records including the ones inside each grouped record.

 

[PaulBricker]

Well I moved stuff around. I put the textbox txtCount in the Field Footer instead of the Field Header. Then I put this expression in the On Format Event for the Report Footer.
Me.ReportHeaderTextBox = Me.txtCount

It gave me the correct amount for my example.

Paul