Getting shaded details

[Ken]

I have looked at the examples for shading everyother row of a detail but I
would like every third detail to be shaded and have it restart each time a
new group header is displayed.

My report:

Group Header 1 - grouped
Detail - non grouped
Footer - group footer, keep together with first detail.

I would also like to have an index on the left showing a number for each
detail that would start over for each group also.

Is there a way to do this?

Thanks in advance

 

[Allen Browne]

Assuming Access 2000 or later, you can do this without code, using a Running
Sum text box and conditional formatting.

1. Open the report in design view.

2. Add a text box to the Detail section, and give it these properties:
Control Source =1
Running Sum Over Group
Format General Number
Visible No
Name txtCount

3. Add another text box to the Detail section. Make it the whole size of the
Detail section, and behind the others (Send To Back on Format menu.) You may
need to make the other controls transparent.

4. Select this big text box, and choose Conditional Formatting on the Format
menu. Set Condition 1 like this:
Expression ([txtCount] - 1) Mod 3 = 2
and choose the color you want in the bucket.

How it works
=========
The first text box accumulates 1 for every record, so acts as a counter for
the group.

The CF expression subtracts 1 (so it counts 0, 1, 2, 3, ...)
Mod gives the remainer when divided by 3.
The remainder is 2 on every 3rd row.
When this is true, the text box background color changes.

If the detail section Can Grow, this won't work properly, and so you will
need to use some code in its Format event to set its BackColor instead of
relying on the big text box to give you the shading.

 

[Ken]

Hi Allen,

Thanks for the reply.

I am trying to change the background on the detail section.

Also, how do I do the counter?

Ken

Assuming Access 2000 or later, you can do this without code, using a Running
Sum text box and conditional formatting.

1. Open the report in design view.

2. Add a text box to the Detail section, and give it these properties:
Control Source =1
Running Sum Over Group
Format General Number
Visible No
Name txtCount

3. Add another text box to the Detail section. Make it the whole size of the
Detail section, and behind the others (Send To Back on Format menu.) You may
need to make the other controls transparent.

4. Select this big text box, and choose Conditional Formatting on the Format
menu. Set Condition 1 like this:
Expression ([txtCount] - 1) Mod 3 = 2
and choose the color you want in the bucket.

How it works
=========
The first text box accumulates 1 for every record, so acts as a counter for
the group.

The CF expression subtracts 1 (so it counts 0, 1, 2, 3, ...)
Mod gives the remainer when divided by 3.
The remainder is 2 on every 3rd row.
When this is true, the text box background color changes.

If the detail section Can Grow, this won't work properly, and so you will
need to use some code in its Format event to set its BackColor instead of
relying on the big text box to give you the shading.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have looked at the examples for shading everyother row of a detail but I
would like every third detail to be shaded and have it restart each time a
new group header is displayed.

My report:

Group Header 1 - grouped
Detail - non grouped
Footer - group footer, keep together with first detail.

I would also like to have an index on the left showing a number for each
detail that would start over for each group also.

Is there a way to do this?

Thanks in advance

 

[Allen Browne]

Using the text box set up as explained in the previous post, the code to use
in the Format event procedure of the Detail section would be something like
this:

Private Sub Detail_Format(Cancel As Integer, _
FormatCount As Integer)
Dim lngColor As Long
If (Me.txtCount - 1) Mod 3 = 2 Then
lngColor = &HCCCCCC
Else
lngColor = &HFFFFFF
End If
With Me.Section(acDetail)
If .BackColor <> lngColor Then
.BackColor = lngColor
End If
End With
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the reply.

I am trying to change the background on the detail section.

Also, how do I do the counter?

Ken

Assuming Access 2000 or later, you can do this without code, using a
Running
Sum text box and conditional formatting.

1. Open the report in design view.

2. Add a text box to the Detail section, and give it these properties:
Control Source =1
Running Sum Over Group
Format General Number
Visible No
Name txtCount

3. Add another text box to the Detail section. Make it the whole size of
the
Detail section, and behind the others (Send To Back on Format menu.) You
may
need to make the other controls transparent.

4. Select this big text box, and choose Conditional Formatting on the
Format
menu. Set Condition 1 like this:
Expression ([txtCount] - 1) Mod 3 = 2
and choose the color you want in the bucket.

How it works
=========
The first text box accumulates 1 for every record, so acts as a counter
for
the group.

The CF expression subtracts 1 (so it counts 0, 1, 2, 3, ...)
Mod gives the remainer when divided by 3.
The remainder is 2 on every 3rd row.
When this is true, the text box background color changes.

If the detail section Can Grow, this won't work properly, and so you will
need to use some code in its Format event to set its BackColor instead of
relying on the big text box to give you the shading.

I have looked at the examples for shading everyother row of a detail but
I
would like every third detail to be shaded and have it restart each
time a
new group header is displayed.

My report:

Group Header 1 - grouped
Detail - non grouped
Footer - group footer, keep together with first detail.

I would also like to have an index on the left showing a number for
each
detail that would start over for each group also.

Is there a way to do this?

Thanks in advance

 

[Ken]

Hi Allen,

I copied the code and put a text box in the detail section.

When I run the report none of the detail rows are highlighted. When I
changed the running sum to over all tehn every third detail was highlighted.

This is close but I am trying to restart the count so that every third
detail row is highlighted.

Thanks,

K Kazinski

Using the text box set up as explained in the previous post, the code to use
in the Format event procedure of the Detail section would be something like
this:

Private Sub Detail_Format(Cancel As Integer, _
FormatCount As Integer)
Dim lngColor As Long
If (Me.txtCount - 1) Mod 3 = 2 Then
lngColor = &HCCCCCC
Else
lngColor = &HFFFFFF
End If
With Me.Section(acDetail)
If .BackColor <> lngColor Then
.BackColor = lngColor
End If
End With
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the reply.

I am trying to change the background on the detail section.

Also, how do I do the counter?

Ken

Assuming Access 2000 or later, you can do this without code, using a
Running
Sum text box and conditional formatting.

1. Open the report in design view.

2. Add a text box to the Detail section, and give it these properties:
Control Source =1
Running Sum Over Group
Format General Number
Visible No
Name txtCount

3. Add another text box to the Detail section. Make it the whole size of
the
Detail section, and behind the others (Send To Back on Format menu.) You
may
need to make the other controls transparent.

4. Select this big text box, and choose Conditional Formatting on the
Format
menu. Set Condition 1 like this:
Expression ([txtCount] - 1) Mod 3 = 2
and choose the color you want in the bucket.

How it works
=========
The first text box accumulates 1 for every record, so acts as a counter
for
the group.

The CF expression subtracts 1 (so it counts 0, 1, 2, 3, ...)
Mod gives the remainer when divided by 3.
The remainder is 2 on every 3rd row.
When this is true, the text box background color changes.

If the detail section Can Grow, this won't work properly, and so you will
need to use some code in its Format event to set its BackColor instead of
relying on the big text box to give you the shading.

I have looked at the examples for shading everyother row of a detail but
I
would like every third detail to be shaded and have it restart each
time a
new group header is displayed.

My report:

Group Header 1 - grouped
Detail - non grouped
Footer - group footer, keep together with first detail.

I would also like to have an index on the left showing a number for
each
detail that would start over for each group also.

Is there a way to do this?

Thanks in advance

 

[Allen Browne]

Sounds like you have another grouping that is affecting this grouping, so
the Over Group running sum never gets going.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

I copied the code and put a text box in the detail section.

When I run the report none of the detail rows are highlighted. When I
changed the running sum to over all tehn every third detail was
highlighted.

This is close but I am trying to restart the count so that every third
detail row is highlighted.

Thanks,

K Kazinski

Using the text box set up as explained in the previous post, the code to
use
in the Format event procedure of the Detail section would be something
like
this:

Private Sub Detail_Format(Cancel As Integer, _
FormatCount As Integer)
Dim lngColor As Long
If (Me.txtCount - 1) Mod 3 = 2 Then
lngColor = &HCCCCCC
Else
lngColor = &HFFFFFF
End If
With Me.Section(acDetail)
If .BackColor <> lngColor Then
.BackColor = lngColor
End If
End With
End Sub

Thanks for the reply.

I am trying to change the background on the detail section.

Also, how do I do the counter?

Ken


:

Assuming Access 2000 or later, you can do this without code, using a
Running
Sum text box and conditional formatting.

1. Open the report in design view.

2. Add a text box to the Detail section, and give it these properties:
Control Source =1
Running Sum Over Group
Format General Number
Visible No
Name txtCount

3. Add another text box to the Detail section. Make it the whole size
of
the
Detail section, and behind the others (Send To Back on Format menu.)
You
may
need to make the other controls transparent.

4. Select this big text box, and choose Conditional Formatting on the
Format
menu. Set Condition 1 like this:
Expression ([txtCount] - 1) Mod 3 = 2
and choose the color you want in the bucket.

How it works
=========
The first text box accumulates 1 for every record, so acts as a
counter
for
the group.

The CF expression subtracts 1 (so it counts 0, 1, 2, 3, ...)
Mod gives the remainer when divided by 3.
The remainder is 2 on every 3rd row.
When this is true, the text box background color changes.

If the detail section Can Grow, this won't work properly, and so you
will
need to use some code in its Format event to set its BackColor instead
of
relying on the big text box to give you the shading.

I have looked at the examples for shading everyother row of a detail
but
I
would like every third detail to be shaded and have it restart each
time a
new group header is displayed.

My report:

Group Header 1 - grouped
Detail - non grouped
Footer - group footer, keep together with first detail.

I would also like to have an index on the left showing a number for
each
detail that would start over for each group also.

Is there a way to do this?

Thanks in advance