Conditional Formatting Problem

[brsawvel]

I have two reports that look similar except that one grabs directly from the table and the other via a query. Each report has a box (box30) that I want the BackColor to change either green, red, or yellow based on the value of one field.

When I entered the following code in the On_Format Event in each report:

<Code>

If fldStatus = "Shipped" Then Box30.BackColor = 4227072 Else: Box30.BackColor = 16777215

If fldStatus = "Awaiting Approval" Then Box30.BackColor = 255

If fldStatus = "Ordered" Then Box30.BackColor = 65535
</Code>

It works for the one coming directly from the table but not from the one coming from the query. Any suggestions?

 

[RuralGuy]

Using a query or table should make no difference. Is the control named fldStatus or the field or both. If both then try naming the control txtfldStatus and see if it makes any difference. Have you considered using a Select Case structure rather than your sequential If's?

 

[brsawvel]

how do I do a select case structure

 

[RuralGuy]

Try:

   Select Case fldStatus

      Case "Shipped"
         Me.Box30.BackColor = 4227072
      Case "Awaiting Approval"
         Me.Box30.BackColor = 255
      Case "Ordered"
         Me.Box30.BackColor = 65535
      Case Else
         Me.Box30.BackColor = 16777215
   End Select

 

[brsawvel]

That didn't work, but I got it to work via your other recommendation.
I changed the box from fldStatus to boxfldStatus (vs. chanigng the txt) and now it works.
Is it that having the Box name and Row name the same causes conflicts?

 

[brsawvel]

Oh... Thanks a lot for your help.

 

[RuralGuy]

That didn't work, but I got it to work via your other recommendation.
I changed the box from fldStatus to boxfldStatus (vs. chanigng the txt) and now it works.
Is it that having the Box name and Row name the same causes conflicts?

I did not expect the Select Case structure to resolve your issue. It is just easier to follow the code. The generally accepted prefix for a TextBox is txt which is why I suggested nameg the TextBox txtfldStatus, and YES Access can somethimes get confused when the names are the same.