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.

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.

--
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.

"ron b" <(E-Mail Removed)> wrote in message
news:7FB42BA5-E57F-485C-8AF5-(E-Mail Removed)...
>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.

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

Wayne-I-M wrote:
> Hi again, I just read your code and it should be cut down to this - as you
> have duplicated some inserts.
>
> Private Sub Status_AfterUpdate()
> If Me!Status = "GO" Or "NOGO" Or "Postponed" Or "GO/NOGO" Or "GATE 3
> GO/NOGO" Then
> Me!Status.ForeColor = vbWhite
> Me!Status.BackColor = vbBlue
> End If
> If Me!Status = "Y" Or "K" Then
> Me!Status.ForeColor = vbBlack
> Me!Status.BackColor = vbYellow
> End If
> If Me!Status = "G" Or "Status Readout" Or "Plan Readout" Then
> Me!Status.ForeColor = vbBlack
> Me!Status.BackColor = vbGreen
> End If
> If Me!Status = "R" Then
> Me!Status.ForeColor = vbBlack
> Me!Status.BackColor = vbRed
> End If
> End Sub
>
>
> I find the less code the is the less can go wrong with the typing -
> sometimes ??
>
> Hope this helps
>
> --
> Buon Natale, Happy Chritmas.
>
> Wayne
> Manchester, England.
> Scusate,ma il mio Inglese fa schiffo :-)
> Percio se non ci siamo capiti, mi mandate un
> messagio e provero di spiegarmi meglio.
>
>
>
> "Wayne-I-M" wrote:
>
> > I am sorry - I thought you would know about end if's. My fualt for no
> > explaining
> > If your form is set to Black and white you dont need the "else" so I have
> > taken it off
> >
> > One other thing - are you "sure" your users will know what all the colours
> > mean. You may better with a text box with some words in to explain. Of
> > course you know your system so it may be ok.
> >
> > Use this
> >
> >
> > Private Sub Status_AfterUpdate()
> > If Me!Status = "GO" Then
> > Me!Status.ForeColor = vbWhite
> > Me!Status.BackColor = vbBlue
> > End If
> > If Me!Status = "NOGO" Then
> > Me!Status.ForeColor = vbWhite
> > Me!Status.BackColor = vbBlue
> > End If
> > If Me!Status = "Postponed" Then
> > Me!Status.ForeColor = vbWhite
> > Me!Status.BackColor = vbBlue
> > End If
> > If Me!Status = "GO/NOGO" Then
> > Me!Status.ForeColor = vbWhite
> > Me!Status.BackColor = vbBlue
> > End If
> > If Me!Status = "GATE 3 GO/NOGO" Then
> > Me!Status.ForeColor = vbWhite
> > Me!Status.BackColor = vbBlue
> > End If
> > If Me!Status = "Y" Then
> > Me!Status.ForeColor = vbBlack
> > Me!Status.BackColor = vbYellow
> > End If
> > If Me!Status = "K" Then
> > Me!Status.ForeColor = vbWhite
> > Me!Status.BackColor = vbBlack
> > End If
> > If Me!Status = "G" Then
> > Me!Status.ForeColor = vbBlack
> > Me!Status.BackColor = vbGreen
> > End If
> > If Me!Status = "Status Readout" Then
> > Me!Status.ForeColor = vbBlack
> > Me!Status.BackColor = vbGreen
> > End If
> > If Me!Status = "Plan Readout" Then
> > Me!Status.ForeColor = vbBlack
> > Me!Status.BackColor = vbGreen
> > End If
> > If Me!Status = "R" Then
> > Me!Status.ForeColor = vbBlack
> > Me!Status.BackColor = vbRed
> > End If
> > End Sub
> >
> >
> >
> >
> > --
> > Buon Natale, Happy Chritmas.
> >
> > Wayne
> > Manchester, England.
> > Scusate,ma il mio Inglese fa schiffo :-)
> > Percio se non ci siamo capiti, mi mandate un
> > messagio e provero di spiegarmi meglio.

Thanks again Wayne for all your help, Buon Natale

"Wayne-I-M" wrote:

> Hi again, I just read your code and it should be cut down to this - as you
> have duplicated some inserts.
>
> Private Sub Status_AfterUpdate()
> If Me!Status = "GO" Or "NOGO" Or "Postponed" Or "GO/NOGO" Or "GATE 3
> GO/NOGO" Then
> Me!Status.ForeColor = vbWhite
> Me!Status.BackColor = vbBlue
> End If
> If Me!Status = "Y" Or "K" Then
> Me!Status.ForeColor = vbBlack
> Me!Status.BackColor = vbYellow
> End If
> If Me!Status = "G" Or "Status Readout" Or "Plan Readout" Then
> Me!Status.ForeColor = vbBlack
> Me!Status.BackColor = vbGreen
> End If
> If Me!Status = "R" Then
> Me!Status.ForeColor = vbBlack
> Me!Status.BackColor = vbRed
> End If
> End Sub
>
>
> I find the less code the is the less can go wrong with the typing -
> sometimes ??
>
> Hope this helps
>
> --
> Buon Natale, Happy Chritmas.
>
> Wayne
> Manchester, England.
> Scusate,ma il mio Inglese fa schiffo :-)
> Percio se non ci siamo capiti, mi mandate un
> messagio e provero di spiegarmi meglio.
>
>
>
> "Wayne-I-M" wrote:
>
> > I am sorry - I thought you would know about end if's. My fualt for no
> > explaining
> > If your form is set to Black and white you dont need the "else" so I have
> > taken it off
> >
> > One other thing - are you "sure" your users will know what all the colours
> > mean. You may better with a text box with some words in to explain. Of
> > course you know your system so it may be ok.
> >
> > Use this
> >
> >
> > Private Sub Status_AfterUpdate()
> > If Me!Status = "GO" Then
> > Me!Status.ForeColor = vbWhite
> > Me!Status.BackColor = vbBlue
> > End If
> > If Me!Status = "NOGO" Then
> > Me!Status.ForeColor = vbWhite
> > Me!Status.BackColor = vbBlue
> > End If
> > If Me!Status = "Postponed" Then
> > Me!Status.ForeColor = vbWhite
> > Me!Status.BackColor = vbBlue
> > End If
> > If Me!Status = "GO/NOGO" Then
> > Me!Status.ForeColor = vbWhite
> > Me!Status.BackColor = vbBlue
> > End If
> > If Me!Status = "GATE 3 GO/NOGO" Then
> > Me!Status.ForeColor = vbWhite
> > Me!Status.BackColor = vbBlue
> > End If
> > If Me!Status = "Y" Then
> > Me!Status.ForeColor = vbBlack
> > Me!Status.BackColor = vbYellow
> > End If
> > If Me!Status = "K" Then
> > Me!Status.ForeColor = vbWhite
> > Me!Status.BackColor = vbBlack
> > End If
> > If Me!Status = "G" Then
> > Me!Status.ForeColor = vbBlack
> > Me!Status.BackColor = vbGreen
> > End If
> > If Me!Status = "Status Readout" Then
> > Me!Status.ForeColor = vbBlack
> > Me!Status.BackColor = vbGreen
> > End If
> > If Me!Status = "Plan Readout" Then
> > Me!Status.ForeColor = vbBlack
> > Me!Status.BackColor = vbGreen
> > End If
> > If Me!Status = "R" Then
> > Me!Status.ForeColor = vbBlack
> > Me!Status.BackColor = vbRed
> > End If
> > End Sub
> >
> >
> >
> >
> > --
> > Buon Natale, Happy Chritmas.
> >
> > Wayne
> > Manchester, England.
> > Scusate,ma il mio Inglese fa schiffo :-)
> > Percio se non ci siamo capiti, mi mandate un
> > messagio e provero di spiegarmi meglio.
> >
> >
> >
> > "ron b" wrote:
> >
> > > Hi Wayne,
> > > I tried your code instructions, but they did not seem to work. My code is as
> > > follows, please let me know what I did wrong.
> > >
> > > Private Sub Status_AfterUpdate()
> > >
> > > If Me!Status = "GO" Then
> > > Me!Status.ForeColor = vbWhite
> > > Me!Status.BackColor = vbBlue
> > >
> > > If Me!Status = "NOGO" Then
> > > Me!Status.ForeColor = vbWhite
> > > Me!Status.BackColor = vbBlue
> > >
> > > If Me!Status = "Postponed" Then
> > > Me!Status.ForeColor = vbWhite
> > > Me!Status.BackColor = vbBlue
> > >
> > > If Me!Status = "GO/NOGO" Then
> > > Me!Status.ForeColor = vbWhite
> > > Me!Status.BackColor = vbBlue
> > >
> > > If Me!Status = "GATE 3 GO/NOGO" Then
> > > Me!Status.ForeColor = vbWhite
> > > Me!Status.BackColor = vbBlue
> > >
> > > If Me!Status = "Y" Then
> > > Me!Status.ForeColor = vbBlack
> > > Me!Status.BackColor = vbYellow
> > >
> > > If Me!Status = "K" Then
> > > Me!Status.ForeColor = vbWhite
> > > Me!Status.BackColor = vbBlack
> > >
> > > If Me!Status = "G" Then
> > > Me!Status.ForeColor = vbBlack
> > > Me!Status.BackColor = vbGreen
> > >
> > > If Me!Status = "Status Readout" Then
> > > Me!Status.ForeColor = vbBlack
> > > Me!Status.BackColor = vbGreen
> > >
> > > If Me!Status = "Plan Readout" Then
> > > Me!Status.ForeColor = vbBlack
> > > Me!Status.BackColor = vbGreen
> > >
> > > If Me!Status = "R" Then
> > > Me!Status.ForeColor = vbBlack
> > > Me!Status.BackColor = vbRed
> > >
> > > Else
> > > Me!Status.ForeColor = vbBlack
> > > Me!Status.BackColor = vbWhite
> > > End If
> > > End Sub
> > >
> > >
> > > "Wayne-I-M" wrote:
> > >
> > > > Hi Ron
> > > >
> > > > I have given also the boarder and forecolor as well as background - if you
> > > > dont need these just delete the lines - change "Some Color" to what you lik
> > > > the look of.
> > > >
> > > > I have used AfterUpdate as this seems the most obvious but you can also
> > > > change the event to what you want
> > > >
> > > >
> > > >
> > > > Private Sub FieldName_AfterUpdate()
> > > > If Me!FieldName = "ABC" Then
> > > > Me!FieldName.ForeColor = vbRed
> > > > Me!FieldName.BackColor = vbBlue
> > > > Me!FieldName.BorderColor = vbBlack
> > > >
> > > > If Me!FieldName = "DEF" Then
> > > > Me!FieldName.ForeColor = Some color
> > > > Me!FieldName.BackColor = Some color
> > > > Me!FieldName.BorderColor = Some color
> > > >
> > > > If Me!FieldName = "GHI" Then
> > > > Me!FieldName.ForeColor = Some color
> > > > Me!FieldName.BackColor = Some color
> > > > Me!FieldName.BorderColor = Some color
> > > >
> > > > If Me!FieldName = "JKL" Then
> > > > Me!FieldName.ForeColor = Some color
> > > > Me!FieldName.BackColor = Some color
> > > > Me!FieldName.BorderColor = Some color
> > > >
> > > > If Me!FieldName = "MNO" Then
> > > > Me!FieldName.ForeColor = Some color
> > > > Me!FieldName.BackColor = Some color
> > > > Me!FieldName.BorderColor = Some color
> > > >
> > > > If Me!FieldName = "PQR" Then
> > > > Me!FieldName.ForeColor = Some color
> > > > Me!FieldName.BackColor = Some color
> > > > Me!FieldName.BorderColor = Some color
> > > > End If
> > > > End Sub
> > > >
> > > >
> > > > Hope this helps
> > > >
> > > > --
> > > > Buon Natale, Happy Chritmas.
> > > >
> > > > Wayne
> > > > Manchester, England.
> > > > Scusate,ma il mio Inglese fa schiffo :-)
> > > > Percio se non ci siamo capiti, mi mandate un
> > > > messagio e provero di spiegarmi meglio.
> > > >
> > > >
> > > >
> > > > "ron b" wrote:
> > > >
> > > > > 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.

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-

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