Format VB Coding in Reports

[dancole42]

So I've never used VB in a report before, but I'm taking a shot at it.

Just as a test to see if I'm doing it right, I'm trying to set up an
invoice where a particular rectangle will change from white to red
depending if the customer has more than one order on file.

I have my query set up, and it's working just fine.

Of the five dummy invoices I have set up, I have the following values
calculated for CountofOrderID, and would expect the following colors
for the rectangle:

3 - Red
1 - White
2 - Red
2 - Red
3 - Red

However, when I view the reports, what I actually get is this:

3 - Red
1 - Red
2 - Red
2 - Red
3 - Red

The underlying code I have is:

Private Sub Renewal_Indicator(Cancel As Integer)

If [CountOfOrderID] 1 Then
RenewalHide.BackColor = 255
Else
RenewalHide.BackColor = 16777215
End If

End Sub

What am I doing wrong? Thanks!

[Tom van Stiphout]

On 22 Mar 2007 07:06:51 -0700, da*******@gmail.com wrote:

To debug this, I would temporarily add the CountOfOrderID to the
Details section. And I would set a breakpoint at the top of the
procedure to be able to step through.
Perhaps the code should be in the Format event?

Btw, use the built-in constants vbRed and vbWhite.

>So I've never used VB in a report before, but I'm taking a shot at it.

Just as a test to see if I'm doing it right, I'm trying to set up an
invoice where a particular rectangle will change from white to red
depending if the customer has more than one order on file.

I have my query set up, and it's working just fine.

Of the five dummy invoices I have set up, I have the following values
calculated for CountofOrderID, and would expect the following colors
for the rectangle:

3 - Red
1 - White
2 - Red
2 - Red
3 - Red

However, when I view the reports, what I actually get is this:

3 - Red
1 - Red
2 - Red
2 - Red
3 - Red

The underlying code I have is:

Private Sub Renewal_Indicator(Cancel As Integer)

If [CountOfOrderID] 1 Then
RenewalHide.BackColor = 255
Else
RenewalHide.BackColor = 16777215
End If

End Sub

What am I doing wrong? Thanks!

[dancole42]

On Mar 22, 10:42 am, Tom van Stiphout <[e-mail]:

On 22 Mar 2007 07:06:51 -0700, [e-mail] wrote:

To debug this, I would temporarily add the CountOfOrderID to the
Details section. And I would set a breakpoint at the top of the
procedure to be able to step through.
Perhaps the code should be in the Format event?

Btw, use the built-in constants vbRed and vbWhite.

So I've never used VB in a report before, but I'm taking a shot at it.

Just as a test to see if I'm doing it right, I'm trying to set up an
invoice where a particular rectangle will change from white to red
depending if the customer has more than one order on file.

I have my query set up, and it's working just fine.

Of the five dummy invoices I have set up, I have the following values
calculated for CountofOrderID, and would expect the following colors
for the rectangle:

3 - Red
1 - White
2 - Red
2 - Red
3 - Red

However, when I view the reports, what I actually get is this:

3 - Red
1 - Red
2 - Red
2 - Red
3 - Red

The underlying code I have is:

Private Sub Renewal_Indicator(Cancel As Integer)

If [CountOfOrderID] 1 Then
RenewalHide.BackColor = 255
Else
RenewalHide.BackColor = 16777215
End If

End Sub

What am I doing wrong? Thanks!

It does the same thing whether it's in Details or the Group Header.

What's a breakpoint?

[Larry Linson]

<da*******@gmail.comwrote

It does the same thing whether it's in
Details or the Group Header.

Help me understand... your code is a Private Sub named RenewalIndicator...
what is it that you expect to cause this code to execute? At execution
time, the events in a Report that cause code to be executed are the Format
and Print events for a Section -- your code will need to be either called
from, or included in, the Format or Print event for the Report Section where
the Controls reside.

What's a breakpoint?

A "breakpoint" is a debugging aid, set from the toolbar in a code window, on
a line of code -- when execution reaches that line of code, execution stops,
and the VBA code is displayed so you can use the Immediate Window, etc., to
debug your code.

I'd suggest a bit more study on VBA in Access, how it works, what it does,
and how to use it before you launch into writing your own. I believe there
are some recommendations for self-study texts in the FAQ at
http://www.mvps.org/access.

Some people I know have a book that is expected to be published in May of
this year... the lead author is Teresa Hennig, and I believe it is already
listed at amazon.com. John Viescas' Access 2003 Inside/Out includes an
introduction to VBA, but not "in-depth" developer training.

Larry Linson
Microsoft Access MVP