Access Report Group Header Question

[David Horowitz]

Hi folks.

I need to create a report that has a Group Header that pulls certain data
from the Detail section. It's something like this:

+--Report----------------------------------------
|
+--Group Header----------------------------------
|
| Codes: XYZ, XXX, YYY
|
+--Detail----------------------------------------
| When IsIt Code
| ------- ----- ----
| Row 10/1/05 Y XYZ
| Row 10/2/05 N XXX
| Row 10/1/05 N YYY
+---------------------------------------------

And the RecordSource is something simple like this:
Report.RecordSource="SELECT When, IsIt, Code FROM TheTable"

So I just need this TextBox in the Group Header that's a string
concatenation of the values of the Code column from the records that are in
the Detail section of THIS PAGE. There may be more than one page for each
group.

Hope this makes sense so far. What I don't know how to do is the Codes list
part in the Group Header. Obviously I'm going to concatenate the strings
from the detail records, but how can I pick up the data from the Detail
records? What Event Procedure would I hook into, and how would I actually
reference the data needed?

I guess I can either try to read the detail data from the form, but I think
the Group Header events only have access to the first Detail record, and the
Group Footers only have access to the last. There's no way to get access to
all of them, is there?

What if I concat the strings together in the Detail_Print event, and then
stick that value into a textbox in the GroupHeader? I tried that, but it
seems the group header is already finalized by the time the detail records
are printed, so how could I force the Group Header to redraw AFTER I concat
the values?

And I wouldn't mind opening a Connection to the DB and running my own SQL
query to get the data I want, but how would I know which records were going
to be on this page? I guess in the Group Header, if, as according to the
docs, I can get the first Detail record data from the textboxes, I guess I
could run a query knowing now which is the first record on the page, and
then I could select records appropriately to concat.

Thanks in advance!

David

[PC Datasheet]

Build a recordset from the recordsource of the report then create a public
subroutine (MySub) that loops through the recordset and builds the code
string you want in the Group Header. Put a textbox in the Group Header and
put the following expression in its control source:
=MySub().

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"David Horowitz" <da***@soundsidesoftware.com> wrote in message
news:lX*****************@fe09.lga...

Hi folks.

I need to create a report that has a Group Header that pulls certain data
from the Detail section. It's something like this:

+--Report----------------------------------------
|
+--Group Header----------------------------------
|
| Codes: XYZ, XXX, YYY
|
+--Detail----------------------------------------
| When IsIt Code
| ------- ----- ----
| Row 10/1/05 Y XYZ
| Row 10/2/05 N XXX
| Row 10/1/05 N YYY
+---------------------------------------------

And the RecordSource is something simple like this:
Report.RecordSource="SELECT When, IsIt, Code FROM TheTable"

So I just need this TextBox in the Group Header that's a string
concatenation of the values of the Code column from the records that are
in the Detail section of THIS PAGE. There may be more than one page for
each group.

Hope this makes sense so far. What I don't know how to do is the Codes
list part in the Group Header. Obviously I'm going to concatenate the
strings from the detail records, but how can I pick up the data from the
Detail records? What Event Procedure would I hook into, and how would I
actually reference the data needed?

I guess I can either try to read the detail data from the form, but I
think the Group Header events only have access to the first Detail record,
and the Group Footers only have access to the last. There's no way to get
access to all of them, is there?

What if I concat the strings together in the Detail_Print event, and then
stick that value into a textbox in the GroupHeader? I tried that, but it
seems the group header is already finalized by the time the detail records
are printed, so how could I force the Group Header to redraw AFTER I
concat the values?

And I wouldn't mind opening a Connection to the DB and running my own SQL
query to get the data I want, but how would I know which records were
going to be on this page? I guess in the Group Header, if, as according to
the docs, I can get the first Detail record data from the textboxes, I
guess I could run a query knowing now which is the first record on the
page, and then I could select records appropriately to concat.

Thanks in advance!

David