Line up grouping

A

aeronaut

I have report that groups records by Canister and Cane. The report
shows the Canister in one row, the Cane in the next and the
following rows contain the grouped records. It looks like this:

Canister Cane Male FDate
1
1
Doe, John 5/25/2005
Tough, Guy 1/15/2007
2
Strong, Ken 5/25/2000
Hefty, Max 6/11/2001
2
1
Rough, Mark 11/5/1999
Smith, Sam 10/22/2002

I would like to have the Canister and the Cane to be in the same
row as the first grouped record, but to show the Canister and Cane
only for the for the first record in the group like below:

Canister Cane Male FDate
1 1 Doe, John 5/25/2005
Tough, Guy 1/15/2007
2 Strong, Ken 5/25/2000
Hefty, Max 6/11/2001
2 1 Rough, Mark 11/5/1999
Smith, Sam 10/22/2002

I thank you for your help.
Andras
 
R

Rob Parker

Hi Andras,

You can do this by moving the textbox controls containing the Canister and
Cane data from the group sections where they are currently located into the
detail section. Set the Hide Duplicates property for these controls to Yes.
You will need to keep the sorting that you have set, but you can set the
Show Group Header property to No so the group headers don't show (or simply
resize them to zero height).

HTH,

Rob
 
M

Marshall Barton

aeronaut said:
I have report that groups records by Canister and Cane. The report
shows the Canister in one row, the Cane in the next and the
following rows contain the grouped records. It looks like this:

Canister Cane Male FDate
1
1
Doe, John 5/25/2005
Tough, Guy 1/15/2007
2
Strong, Ken 5/25/2000
Hefty, Max 6/11/2001
2
1
Rough, Mark 11/5/1999
Smith, Sam 10/22/2002

I would like to have the Canister and the Cane to be in the same
row as the first grouped record, but to show the Canister and Cane
only for the for the first record in the group like below:

Canister Cane Male FDate
1 1 Doe, John 5/25/2005
Tough, Guy 1/15/2007
2 Strong, Ken 5/25/2000
Hefty, Max 6/11/2001
2 1 Rough, Mark 11/5/1999
Smith, Sam 10/22/2002


If the two header sections and the detail section are the
same height (and CanGrow/CanShrink are No), then just add:

Me.MoveLayout = False

to the Format or Print event procedure of the header
sections.
 
A

aeronaut

Marsh,
This also works perfectly. However, I do not understatnd what

Me.MoveLayout = False

command does.

Andras
 
M

Marshall Barton

aeronaut said:
This also works perfectly. However, I do not understatnd

Me.MoveLayout = False


VBA Help explains it.

It simply tells Access to print the section without moving
the current position on the page. I.e. the next section
will print on top of this section.

The CanGrow/CanShrink restriction can be severe in reports
where it's needed.

OTOH, HideDuplicates is unaware of grouping so a new group
might hide a value that just happens to be the last value in
the previous group.
 
A

aeronaut

I thank for your explanation.
Andras

Marshall Barton said:
VBA Help explains it.

It simply tells Access to print the section without moving
the current position on the page. I.e. the next section
will print on top of this section.

The CanGrow/CanShrink restriction can be severe in reports
where it's needed.

OTOH, HideDuplicates is unaware of grouping so a new group
might hide a value that just happens to be the last value in
the previous group.
 
A

aeronaut

Now I have another problem. I wanted like to have thet group header
info appear on the first detail line when I move to a new page. To
do this I set the Group Header's Repeat Section property to Yes.
However, this caused Access to freeze up.
 
M

Marshall Barton

aeronaut said:
Now I have another problem. I wanted like to have thet group header
info appear on the first detail line when I move to a new page. To
do this I set the Group Header's Repeat Section property to Yes.
However, this caused Access to freeze up.


RepeatSection is hypersensitive to fooling with the header's
printing. Off the top of my head, I don't see an easy way
to get that effect.
 
A

aeronaut

Marsh,
I thank you for all your help. In the mean time I have found an easy way to
get get the grouping headings lined up with the the first row of the details
section and also to repeat the grouping headings on the top of every new
page. I discovered that the report wizzard gives all that when I choose the
the "Block" layout. I do not like the boxes (solid outline) and other format
features of the block lay out, but that is easy to change.
Andras
 
M

Marshall Barton

Not sure what you are referring to, but it's good to hear
that you got it working. I've been trying to work out
something using an approach that is undoubtably more complex
than whatever it is you are using. (probably something
utilizing the page header).
 
M

Marshall Barton

Marshall said:
Not sure what you are referring to, but it's good to hear
that you got it working. I've been trying to work out
something using an approach that is undoubtably more complex
than whatever it is you are using. (probably something
utilizing the page header).


Well, I think I've come up with something that avoids all(?)
the restrictions. It's kind of tricky, but I like it
because once you grasp the concept, it's fundamentally
simple.

For the sake of historical record, even if you are no longer
interested, here it is.

Put all the bound text boxes in the detail section. Then
create a group level for each field you want to hide and
make all these group headers invisible.

Add a text box (named txtGrp1) to the **second** level group
header. Set its control source expression to =1 and
RunningSum property to Over Group.

Add a text box (named txtGrp2) to the third level group
header with the same properties as txtGrp1. Repeat for
however many (up to 10 levels) fields you want to hide
duplicates.

Also add a similar, hidden text box (named txtDtl) to the
detail section.

Finally add some code to the detail section's Format event.
The code uses the running sum values to determine when a
field value changes (which can not be done reliably using
VBA variables and code).

Me.txtFieldA.Visible = (Me.txtGrp1 = 1 And txtDtl = 1) _
Or Me.Top <= 0.5 * 1440
Me.txtFieldB.Visible = (Me.txtGrp2 = 1 And txtDtl = 1) _
Or Me.Top <= 0.5 * 1440
. . .

Where txtFieldA is the top level grouping text box,
txtFieldB is the second level text box and so on.

The .5 is the reports top margin setting in inches and the
1440 is the standard twips per inch conversion factor. This
is how you detect when the detail is the first one on a
page.

You can even use the Line method in the detail section's
Print event to draw same height borders around the text
boxes in situations where one or more of the text boxes can
grow:

With Me.txtFieldA
Me.Line (.Left, .Top)-Step(.Width, Me.Height), , B
End With
. . .
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top