Cangrow with Hidden Duplicates

[Bill B]

I have a report that's based on two tables, Tasks and Assignments, where
each task can have many assignments. I want to be able to show the
assignment data in each record but I only want to show the task data in the
first record for each Task. So I set the Task fields to hide dupilcates and
off I go. The problem is that one of the task fields is a Name field that
can easily exceed the with of the text box I've given it. So, wanting to
wrap the text, I set the Name text box and detail section to CanGrow. But
the problem is that a) this causes a gap between the first and second record
(ideally, the Name text should just fill down into the area for the second
and third Assignment records) and b) this appears to be unreliable in that
the Name field wraps sometimes and not others, words or parts of words are
duplicated in the wrapped text, and sometimes the Name does not print at
all.

Is there some way to accomplish this? I thought about subreports but I need
totals in the main report and the subreports would have to be sorted (I have
written code that sets up sort levels in the main report at runtime by
changing summary level data sources).

Does this make sense?

Bill B

 

[Marshall Barton]

I have a report that's based on two tables, Tasks and Assignments, where
each task can have many assignments. I want to be able to show the
assignment data in each record but I only want to show the task data in the
first record for each Task. So I set the Task fields to hide dupilcates and
off I go. The problem is that one of the task fields is a Name field that
can easily exceed the with of the text box I've given it. So, wanting to
wrap the text, I set the Name text box and detail section to CanGrow. But
the problem is that a) this causes a gap between the first and second record
(ideally, the Name text should just fill down into the area for the second
and third Assignment records) and b) this appears to be unreliable in that
the Name field wraps sometimes and not others, words or parts of words are
duplicated in the wrapped text, and sometimes the Name does not print at
all.

Is there some way to accomplish this? I thought about subreports but I need
totals in the main report and the subreports would have to be sorted (I have
written code that sets up sort levels in the main report at runtime by
changing summary level data sources).

I don't know how to integrate this need with your "changing
summary level data sources". but you can get what I think is
the desired effect by grouping on the task field and putting
the task info in the group header. Make sure the group
header has nothing in the same "column" area as the detail
info and vice versa. Then add a line of code to the task
group header's Format event:
Me.MoveLayout = False

 

[Bill B]

Thanks for that Marsh. Now when I run the report, everything is fine as long
as the detail area is big enough to fit the header data. Even though I have
Cangrow set to true on both the control and the section, if the header data
runs to two lines and the detail is only a single line the header part gets
chopped. Is there any way around that?

Bill B.

 

[Marshall Barton]

Thanks for that Marsh. Now when I run the report, everything is fine as long
as the detail area is big enough to fit the header data. Even though I have
Cangrow set to true on both the control and the section, if the header data
runs to two lines and the detail is only a single line the header part gets
chopped. Is there any way around that?

If you're using A2002 or later, you can change the details
section's Height on the fly. With a hidden text box to keep
track of the group header's Height, it might then be
possible to figure out how much to increase the height of
the last detail.

I am going to be away today and tomorrow so, if you need
help figuring that out, it could be a couple of days before
I can find the time to work through the specifics.

 

[Marshall Barton]

Well, I finally got some time to work out the details for
putting the group header beside the details. This worked in
my quicky tests even when there are not enough detail
records to reach the bottom of the header section. I have
not tested this with a CanGrow detail section nor with all
combinations of KeepTogether, etc.

The group header can grow, but the group's KeepTogether
property probably needs to be set to Whole Group

These untility text boxes were added:
Section Name ControlSource RunningSum
header txtDtlCnt =Count(*)
header txtHdrBottom
detail txtLine =1 Over Group

The event procedures that I used are:

Private Sub Detail_Format( . . .
If Me.txtLine = Me.txtDtlCnt Then
If Me.Top + Me.Height < Me.txtHdrBottom Then
Me.Section(0).Height = Me.txtHdrBottom - Me.Top
End If
End If
End Sub

Private Sub GroupHeader0_Format( . . .
Me.MoveLayout = False
Me.Section(0).Height = 0
End Sub

Private Sub GroupHeader0_Print( . . .
Me.txtHdrBottom = Me.Top + Me.Height
End Sub

 

[Bill B]

Marsh,

Thanks for your efforts. In your absence I came up with the following. I had
tried your initial idea and was having problems when the size of the header
field was larger than the size of the detail area. I wound up writing code
to delay the writing of the footer section until the size of the detail area
+ the size of the footer was bigger than the height of the detail area. The
only thing I'm not sure about is the use of the FormatCount parameter in the
Detail_Format event handler. Here's the code (no special text boxes or
counters needed - I think):

Option Explicit
Private detailHeight, headHeight

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If FormatCount = 1 Then
' increase height of detail area for each row
detailHeight = detailHeight + 285
End If
End Sub

Private Sub GroupFooter0_Print(Cancel As Integer, PrintCount As Integer)
If headHeight > detailHeight Then ' can't print yet - not far enough
down page
MoveLayout = True
NextRecord = False
PrintSection = False
headHeight = headHeight - 290 ' subtract a row plus wiggle room from
remaining detail height
End If
End Sub

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
MoveLayout = False
detailHeight = 0 ' init detail section height accumulator
End Sub

Private Sub GroupHeader0_Print(Cancel As Integer, PrintCount As Integer)
' figure out the height of the header. the Name field is 230 high and
the height of a detail row is 285
' rounding takes care of oddball row mismatches
headHeight = Round(txtActivityName.Height / 230 + 0.5) * 285
End Sub

Private Sub Report_Open(Cancel As Integer)
detailHeight = 0
End Sub

Regards,

Bill B

Well, I finally got some time to work out the details for
putting the group header beside the details. This worked in
my quicky tests even when there are not enough detail
records to reach the bottom of the header section. I have
not tested this with a CanGrow detail section nor with all
combinations of KeepTogether, etc.

The group header can grow, but the group's KeepTogether
property probably needs to be set to Whole Group

These untility text boxes were added:
Section Name ControlSource RunningSum
header txtDtlCnt =Count(*)
header txtHdrBottom
detail txtLine =1 Over Group

The event procedures that I used are:

Private Sub Detail_Format( . . .
If Me.txtLine = Me.txtDtlCnt Then
If Me.Top + Me.Height < Me.txtHdrBottom Then
Me.Section(0).Height = Me.txtHdrBottom - Me.Top
End If
End If
End Sub

Private Sub GroupHeader0_Format( . . .
Me.MoveLayout = False
Me.Section(0).Height = 0
End Sub

Private Sub GroupHeader0_Print( . . .
Me.txtHdrBottom = Me.Top + Me.Height
End Sub
--
Marsh
MVP [MS Access]

Thanks for that Marsh. Now when I run the report, everything is fine as
long
as the detail area is big enough to fit the header data. Even though I
have
Cangrow set to true on both the control and the section, if the header
data
runs to two lines and the detail is only a single line the header part
gets
chopped. Is there any way around that?

 

[Marshall Barton]

Your approach is more likely to be off. The problem is that
the FormatCount can be 1 several times as the section is
(re)formatted, depending on all kinds of factors (e.g.
section KeepTogether, group KeepTogether, Pages, printing a
preview, etc). You just plain can not use code to
accumulate a value across multiple records/details.

OTOH, you don't need to do that. Even for a CanGrow
section, you can get the section's position on the page and
it's final Height by using Me.Top and Me.Height in the
section's Print event.

Using a blank footer with NextRecord = False will work (only
way to do this in A2K and earlier), but it is not capable of
being precise and you will usually get a blank area before
the start of the next group. This is why I suggested
adjusting the Height (new feature in A2002) of the last
detail.