Alternate backcolor in report

[Georges Heinesch]

Hi.

I have a problem with alternating background colors in a report. The
code below shows, that the first line should always be white (default
global boolean value is false). However it depends on the number of
records upon which the report is based. If the number of records is odd,
the first line is grey; if it's even, the first line is white (how it
should be).

I included so much debug.print lines to nail down the source of the
problem, but I didn't find one single hint why Access behaves like that.
All seems properly programmed and executed, but the lines show the wrong
background color when the number of records is odd.

Any (!) ideas ... !

TIA

---- cut here ----
Option Compare Database
Option Explicit
Dim boolColor As Boolean

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Call ChangeColor
End Sub

Private Sub ChangeColor()
Const conColorWhite = 16777215
Const conColorGrey = 14737632

If boolColor Then
Me.Section(0).BackColor = conColorGrey
Else
Me.Section(0).BackColor = conColorWhite
End If
boolColor = Not boolColor
End Sub
---- cut here ----

--
Georges

[Allen Browne]

One issue with your code is that the Format event of the Detail section can
be called multiple times for one row where pages break or things are growing
or shrinking or being kept together or ... Checking FormatCount might be a
partial solution, or you might be able to use the Print event since you are
not changing the size of anything.

Assuming Access 2000 or later, it would be much more efficient to do this
with conditional formatting.

1. Place a text box in the detail section, and set these properties:
Name txtCount
Control Source =1
Running Sum Over All
Visible No

2. Place another text box in the detail section. Send To Back so it sits
behind the others. Select this textbox, and choose Conditional Formatting on
the Format menu. Set the first condition to this Expression:
[txtCount] Mod 2 = 1
and choose the grey color in the bucket.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Georges Heinesch" <ne**@geohei.lu> wrote in message
news:40********@news.vo.lu...

Hi.

I have a problem with alternating background colors in a report. The
code below shows, that the first line should always be white (default
global boolean value is false). However it depends on the number of
records upon which the report is based. If the number of records is odd,
the first line is grey; if it's even, the first line is white (how it
should be).

I included so much debug.print lines to nail down the source of the
problem, but I didn't find one single hint why Access behaves like that.
All seems properly programmed and executed, but the lines show the wrong
background color when the number of records is odd.

Any (!) ideas ... !

TIA

---- cut here ----
Option Compare Database
Option Explicit
Dim boolColor As Boolean

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Call ChangeColor
End Sub

Private Sub ChangeColor()
Const conColorWhite = 16777215
Const conColorGrey = 14737632

If boolColor Then
Me.Section(0).BackColor = conColorGrey
Else
Me.Section(0).BackColor = conColorWhite
End If
boolColor = Not boolColor
End Sub
---- cut here ----

--
Georges

[Steve Jorgensen]

When formatting reports, you should never rely on a sequence of event calls.
For one thing, what if you're previewing and going backward through the pages?
Some events will actually be firing in backward order in that case.

Now, the reason your code doesn't seem to be doing anything at all, not even
working strangely because of the issue I previously stated is that the Format
event actually fires at least twice for each section, possibly more if it
tries to format it on one page, figures it won't fit, then tries again on the
next page. The FormatCount parameter tells you how many times the event has
fired, so you can check for an even numbered value to perform the operation.

Now, to get away from code dependent upon a firing sequence, you need a way to
identify the actual row number you're on. You can do this by creating a
hidden counter control on the report. You do that by making a hidden control,
setting its control source to =1 (yes, the equal sign is part of what you
type), then you set the Running Sum property of the control to Over Report.
now, you have a running sum of the constant value 1 over the report which
amounts to a running count which amounts to a row number.

Assuming that control is called txtRowCounter, in your code, you can say If
Me!txtRowCounter mod 2 = 1 Then <odd-row-action> Else <even-row-action>.

On Fri, 06 Feb 2004 10:47:13 +0100, Georges Heinesch <ne**@geohei.lu> wrote:

Hi.

I have a problem with alternating background colors in a report. The
code below shows, that the first line should always be white (default
global boolean value is false). However it depends on the number of
records upon which the report is based. If the number of records is odd,
the first line is grey; if it's even, the first line is white (how it
should be).

I included so much debug.print lines to nail down the source of the
problem, but I didn't find one single hint why Access behaves like that.
All seems properly programmed and executed, but the lines show the wrong
background color when the number of records is odd.

Any (!) ideas ... !

TIA

---- cut here ----
Option Compare Database
Option Explicit
Dim boolColor As Boolean

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Call ChangeColor
End Sub

Private Sub ChangeColor()
Const conColorWhite = 16777215
Const conColorGrey = 14737632

If boolColor Then
Me.Section(0).BackColor = conColorGrey
Else
Me.Section(0).BackColor = conColorWhite
End If
boolColor = Not boolColor
End Sub
---- cut here ----

[Georges Heinesch]

Allen Browne wrote:

One issue with your code is that the Format event of the Detail section can
be called multiple times for one row where pages break or things are growing
or shrinking or being kept together or ... Checking FormatCount might be a
partial solution, or you might be able to use the Print event since you are
not changing the size of anything.
I included some debug.print line to check FormatCount, but it always
showed 1.
Assuming Access 2000 or later, it would be much more efficient to do this
with conditional formatting.

1. Place a text box in the detail section, and set these properties:
Name txtCount
Control Source =1
Running Sum Over All
Visible No
That is done anyway already since I use a control to count the rows.
2. Place another text box in the detail section. Send To Back so it sits
behind the others. Select this textbox, and choose Conditional Formatting on
the Format menu. Set the first condition to this Expression:
[txtCount] Mod 2 = 1
and choose the grey color in the bucket.

What about omiiting our suggested textbox, and including the code into
Detail_Format? This avoids creating this text box control.

Any objections?

---- cut here ----
Option Compare Database
Option Explicit

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Const conColorWhite = 16777215
Const conColorGrey = 14737632

' change background color
If Me!txtRowCounter Mod 2 = 0 Then
Me.Section(0).BackColor = conColorGrey
Else
Me.Section(0).BackColor = conColorWhite
End If

End Sub
---- cut here ----

This works perfectly now!
Thanks a lot so far!

TIA

--
Georges

[Georges Heinesch]

Steve Jorgensen wrote:

When formatting reports, you should never rely on a sequence of event calls.
For one thing, what if you're previewing and going backward through the pages?
I have only sone records at the moment (test data). These don't fit a
complete page yet.
Now, the reason your code doesn't seem to be doing anything at all, not even
working strangely because of the issue I previously stated is that the Format
event actually fires at least twice for each section, possibly more if it
tries to format it on one page, figures it won't fit, then tries again on the
next page. The FormatCount parameter tells you how many times the event has
fired, so you can check for an even numbered value to perform the operation.
I tested the FormatCount variable with a debug.print line, but it always
showed 1 for some reason?!
Now, to get away from code dependent upon a firing sequence, you need a way to
identify the actual row number you're on. You can do this by creating a
hidden counter control on the report. You do that by making a hidden control,
setting its control source to =1 (yes, the equal sign is part of what you
type), then you set the Running Sum property of the control to Over Report.
now, you have a running sum of the constant value 1 over the report which
amounts to a running count which amounts to a row number.
Done!
Assuming that control is called txtRowCounter, in your code, you can say If
Me!txtRowCounter mod 2 = 1 Then <odd-row-action> Else <even-row-action>.

This code should be within the Detail_Format part of the report, correct?

TIA

--
Georges

[Steve Jorgensen]

On Fri, 06 Feb 2004 16:25:20 +0100, Georges Heinesch <ne**@geohei.lu> wrote:

Steve Jorgensen wrote:

When formatting reports, you should never rely on a sequence of event calls.
For one thing, what if you're previewing and going backward through the pages?
I have only sone records at the moment (test data). These don't fit a
complete page yet.

Now, the reason your code doesn't seem to be doing anything at all, not even
working strangely because of the issue I previously stated is that the Format
event actually fires at least twice for each section, possibly more if it
tries to format it on one page, figures it won't fit, then tries again on the
next page. The FormatCount parameter tells you how many times the event has
fired, so you can check for an even numbered value to perform the operation.

I tested the FormatCount variable with a debug.print line, but it always
showed 1 for some reason?!

Perhaps, my information is out of date. The statement was true for Access 97
and earlier.

Now, to get away from code dependent upon a firing sequence, you need a way to
identify the actual row number you're on. You can do this by creating a
hidden counter control on the report. You do that by making a hidden control,
setting its control source to =1 (yes, the equal sign is part of what you
type), then you set the Running Sum property of the control to Over Report.
now, you have a running sum of the constant value 1 over the report which
amounts to a running count which amounts to a row number.

Done!

Assuming that control is called txtRowCounter, in your code, you can say If
Me!txtRowCounter mod 2 = 1 Then <odd-row-action> Else <even-row-action>.

This code should be within the Detail_Format part of the report, correct?

Yes. In fact, given the uncertain firing order, the only place you should
usually set any dynamic formatting is in an event handler for the section that
will be affected.

[Allen Browne]

The Running Sum is a simple, reliable count.

The Detail_Format event can occur multiple times, and you may need to handle
the Retreat event as well.

Further, VBA code will execute more slowly than conditional formatting.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Georges Heinesch" <ne**@geohei.lu> replied in message
news:40********@news.vo.lu...

What about omiiting our suggested textbox, and including the code into
Detail_Format? This avoids creating this text box control.

Any objections?

[Georges Heinesch]

Steve Jorgensen wrote:

Assuming that control is called txtRowCounter, in your code, you can say If
Me!txtRowCounter mod 2 = 1 Then <odd-row-action> Else <even-row-action>.

This code should be within the Detail_Format part of the report, correct?

Yes. In fact, given the uncertain firing order, the only place you should
usually set any dynamic formatting is in an event handler for the section that
will be affected.

Thanks a lot.
Works perfectly now!

--
Georges