Allen:
Very many thanks for your reply.
In my previous post, I didn't get into the reasons why I'd constructed the
report the way I had because it was complicated to explain and I wanted to
keep things simple. You've made the reasonable assumption that I may be
accumulating totals and have given some better alternatives.
In fact, the database I'm working on (for a friend) is a database of his CD,
vinyl records and audio tapes collection. He wants a report that will print
clear play lists for his car. Most of his collection is on CD and often the
jewel case slips are not clear. He wants an Access report that will print
clear jewel case slips. This means squeezing the data into a 120mm x 120mm
square of paper. It's this requirement that gave rise to the problem.
The database has three significant one-to-many relationships: (1) between
the Albums table and the Tracks table; (2) between the Tracks table and the
TrackArtists table; and (3) between the Artists table and the TrackArtists
table. (The TrackArtists table only stores the foreign keys for the
Track_ID and the Artist_ID.) Therefore, each album has one database record
in the Albums table, its many tracks are entered in the Tracks table and
each track's artist(s) are entered in the TrackArtists table.
The query on which the report is based contains the Albums, Tracks,
TrackArtists, and Artists tables. The query returns mutiple records - for
each album, track and artist combination.
The report's Group Header contains the name of the Album (Album_Title).
Under that, I need to list the tracks and artists. If I write a traditional
grouped report, I'd have a second Group Header for the track and under that
I'd have a Detail Section for the Artist(s). But not only does this look
clumsy, it takes up too much space vertically down the page.
To overcome this, I have one Group Header for the Album_Title and I'm using
the Detail_Format event to accumulate track and artist data as a
concatenated string, so I can print on one line:
Track_No, Track_Title (Artist1, Artist2... ArtistN), Track_Time
The conditions I've implemented in the Detail_Format event are:
1. (a) If the Track_ID has changed since the last time the Detail_Format
event fired, then it's time to print the string that's been constructed for
the previous Track_ID. (b) If the Track_ID has not changed, then the
Detail_Format event continues to build the string by concatenating the next
artist and forces a move to the next record without printing or moving
layout. The Detail_Format event needs to do this because it doesn't know
until it fires again (when it hits the next record) which course of action
to take, ie (a) or (b), ie the string cannot be finalised and printed until
the Detail_Format event hits the next record for a new Track_ID.
2. If the Album_ID changes, then again it's time to print the string. The
Track_ID would also change at this stage so some of my code may be
superfluous. For the timebeing, my code evaluates changes in Track_ID and
Album_ID.
As the report iterates through its recordset, sooner or later it hits a
record containing a new Album_ID. The GroupHeader0_Format event fires and
lays out the album title. This happens before the Detail_Format event has
fired, ie before it's had time to evaluate the above-mentioned criteria.
This results in the Album_Title printing before the string. As the string
contains data for the previous Album, the string should be printed as part
of the previous group.
I've wondered whether the Detail_Format event could set a flag so that the
following GroupHeader0_Format event could be cancelled or retreated and the
string printed as part of the previous group. But how then do I make the
GroupHeader0 print the next album title? My textbooks are a bit hazy on the
Retreat event. It seems, if I set the GroupHeader0 to keep with first
detail, then Access will fire the GroupHeader0's Retreat event but I can't
see how to use it in my solution.
I've not given it much thought but I've also wondered if an alternative
approach might be to include an invisible GroupHeader1 for the track and
possibly use it to accumulate data or set and evaluate conditions.
Could it be that the Access reports engine lacks a Me.PreviousRecord
property as a way of getting back?
This problem is intriguing. There must be many instances when developers
want to control report formatting in this way, ie when data is split across
a one-to-many relationship and when a standard grouped report won't do. As I
can't be the first person who's come across this, my guess is that there may
be a ready solution within Access, but I can't think of it. Your comments,
and those of other developers, would be welcome.
(Incidentally, on a point that isn't relevant to my current problem but I
pick up as you mentioned it. I thought that the technique of using "Page n
of m" would sort out many formatting issues because Access would be forced
to format the report twice: first to get the last page number "m" and again
to put the last page number on each page. I thought that using "Page n of m"
was one of the tricks for sorting out some formatting issues. It's not my
main problem for now, but I'd be interested to hear your comments.)
Regards
Geoff
Allen Browne said:
Geoff, it would be much easier to add a Group Footer section, and use its
Format event to put the total into the report. You can look up the first
value from the next group and add that in as well if you really need to do
that.
We don't know the reasons why you are preparing the report this way, but
it might be better to use criteria in the report's query to exclude the
records you don't want printed rather than set PrintSection to No. That
might mean calculating the total in some other way rather than
accumulating it in the events of the Detail section.
Reasons for suggesting that:
a) Accumulating totals across more than one page is inaccurate. If you
print/preview only some pages of the report (e.g. just page 5), Access may
not fire the events for the intervening pages, so the total is inaccurate.
b) For the same reason the layout is inaccurate as well, e.g. if you just
print page 5, Access may not calculate the suppressed/repeated records
correctly, so you actually get different records printed on page 5 than
you would if you printed the whole report.
c) Suppressing/repeating sections across multiple pages confuses Access
itself. For example, if you have a text box with Control Source of:
=[Page] & " of " & [Pages]
you may get:
Page 6 of 4
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Geoff said:
Access 2002:
In the Detail_Format event procedure, I'm using:
Me.NextRecord
Me.MoveLayout
Me.PrintSection
Everything's working fine except one thing.
The report uses Grouping and Sorting (there's one Group Header).
I do not want to set Me.PrintSection = TRUE until the current record
meets certain conditions. As a consequence, the Format event contains the
following three code lines until the conditions are true:
Me.NextRecord = True
Me.MoveLayout = False
Me.PrintSection = False
One of the conditions is: When the current record begins a new group, I
want to print the data I've been accumulating for the Detail section but
I want to print it as part of the previous group. At the moment, Access
prints the new Group header and the data relating to the previous
record(s) prints under the new group heading.
Is it possible to force a retreat event, undo the printing of the Group
header and print the previous record(s) as part of the detail of the
previous group?
TIA
Geoff
