Code for conditional formatting

[Guest]

I have a form in which I have an object I would like to have the background
change color under certain circumstances. However I need five different
colors. The normal way to add contitional formats in Access 2000 is to use
the conditional format option on the format menu. However it only has three
options. Can someone give me some code which will allow me to change the
background color of my object under five different conditions? If so please
also tell me where to enter the code.

 

[Allen Browne]

If this is in form view, you can add code to set the BackColor property of
the control. Use the Current event of the form, and the AfterUpdate event of
the control(s) it depends on. Also use the Undo event of the form to
restore the correct color, depending on the OldValue of the controls.

If this form is in Continuous or Datasheet view, you're stuck.

 

[OhioRichard]

Good Morning, Group

I am NEW to this group, but am OLD (in more ways than one) to Access
developer groups, and though I have not tried this, would not a "CASE"
selection be a 'bit easier to read and follow the Logic??

A blessed Christmas to ALL!

Richard R
"Retired" ?? Teacher/developer

 

[Guest]

Thanks again Wayne for all your help, Buon Natale

 

[Guest]

Hi Wayne,

You seem pretty knowledgable about colors. I wonder if you can solve a
problem for me. I have a report that has many controls. Many of those have
conditional formatting through the Access menu for forecolor or backcolor or
both.

I wanted all the records in "region 2" to have an ivory background, so I
used vba to set the section backcolor to ivory on "region 2", else white,
and then to set all of the controls' back color to ivory or white.
It worked fine.

Then I needed to change one of the default forecolors in the "Style" textbox
that was set in the Access conditional formatting dialog from purple to blue.
After I changed the color, all of the records where the "Style" control was
blue had a black background!

So, I figured, maybe there's some wierd conflict because the forecolor is
set thru the dialog and the backcolor is set thru vba, so I tried to set all
the conditional formatting for that control in vba.

But they are still turning to a black background. Do you know why?

Thanks,
m-

 

[Guest]

here is the code:



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

On Error GoTo HeaderStyle_Format_Err

'''Set Forecolor of Style for flags
If Me.FlagS = "BASIC approaching; " Or Me.FlagS = "EXT approaching; " Or
Me.FlagS = "Large Pairs; " Or (Me.FitStatus = 0 And Me.XfC > 30 And Me.XfC <
45) Then
Me.style.ForeColor = vbRed
Me.style.ForeColor = RGB(253, 182, 29) 'orange
Else
Me.style.ForeColor = 10485760 'DkBlue
End If


'''Style Header Background Color
If Me.[regStyleHdr] = 2 Then
Me.HeaderStyle.BackColor = RGB(247, 249, 221)
Else: Me.HeaderStyle.BackColor = vbWhite
End If

'''Style Header Most Controls Background Color
If Me.[regStyleHdr] = 2 Then
Me.style.BackColor = RGB(247, 249, 221) _
And Me.comment.BackColor = RGB(247, 249, 221) And Me.CommentDate.BackColor =
RGB(247, 249, 221) _
And Me.FlagS.BackColor = RGB(247, 249, 221) And Me.Flag.BackColor = RGB(247,
249, 221)
Else: Me.style.BackColor = vbWhite _
And Me.comment.BackColor = vbWhite And Me.CommentDate.BackColor = vbWhite
And Me.FlagS.BackColor = vbWhite And Me.Flag.BackColor = vbWhite
End If


'''Style Header [Total Pairs this Style] BackColor
Select Case Me.[SumPairsS]
Case Me.[SumPairsS] > 20000 And Me.[FitStatus] = 1 And Me.[XfC] < 46
Me.[SumPairsS].BackColor = vbRed
Case Me.[SumPairsS] > 20000 And Me.[FitStatus] <> 3 And Me.[XfC] < 31
Me.[SumPairsS].BackColor = vbRed
Case Me.[SumPairsS] > 20000 And Me.[FitStatus] = 1 And Me.[FitRejB] > 1
Me.[SumPairsS].BackColor = vbRed
Case Me.[SumPairsS] > 20000 And Me.[FitRejX] > 1 And Me.[FitStatus] <> 3
Me.[SumPairsS].BackColor = vbRed
Case Me.[SumPairsS] > 20000 And Me.[XfD] <> Null And Me.[XfD] > 29
Me.[SumPairsS].BackColor = vbRed
Case Else
Me.[SumPairsS].BackColor = RGB(247, 249, 221) 'Beige
End Select


If Me.[regStyleHdr] = 2 Then
Me.[SumPairsS].BackColor = RGB(247, 249, 221) 'Beige
End If


'''Style Header [Total Pairs this Style] BorderColor
If Me.[SumPairsS] > 20000 And Me.[FitStatus] <> 3 And (45 > Me.[XfC] > 30)
Then
Me.[red_sumPairsS].BackColor = vbRed
Else
If Me.[SumPairsS] > 20000 Then
Me.[red_sumPairsS].BackColor = RGB(253, 182, 29) 'Orange
Else
If Me.[regStyleHdr] = 2 Then
Me.[SumPairsS].BackColor = RGB(247, 249, 221) 'Beige
Else: Me.[SumPairsS].BackColor = vbWhite
End If
End If
End If


If Not IsNull(Me.FlagS) Then
Me.style.ForeColor = vbRed
Else
If (Me.[FitStatus] = 0 And 45 < Me.[XfC] > 30) Or Me.[FlagS] = "BASIC
approaching; " Or Me.[FlagS] = "EXT approaching; " Or Me.[FlagS] = "Large
Pairs; " Or Me.[FlagS] = "X/F Past Due; " Then
Me.[style] = RGB(253, 182, 29) 'orange
Else
Me![style].ForeColor = RGB(90, 45, 187) 'Dk Blue

End If
End If


HeaderStyle_Format_Exit:
Exit Sub

HeaderStyle_Format_Err:
MsgBox Error$
Resume HeaderStyle_Format_Exit

End Sub