CONDITIONAL FORMATTING

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

graypaarmy - 25 Feb 2008 07:52 GMT

I am building a database for an HR department. I have a combo box in a form
with such choices as MILT, BRVT, TARDY, etc. I am looking to have each have
it's own color when chosen. I have more than 3 so I am guessing I need to
code it, but I have no idea how. Can someone help????

Reply to this Message

John Spencer - 25 Feb 2008 13:37 GMT

You would be able to do this only if you are displaying a single form - a
continuous form cannot (to my knowledge) be coded to display different
colors.

I would create a Sub on the form which you could call in the form's current
event and in the after update event of the combobox

Private Sub sSetColor()
SELECT Case Me.YourComboBoxName & ""
Case "MILT"
Me.YourComboBoxName.ForeColor = vbRed
Case "BRVT"
Me.YourComboBoxName.ForeColor = vbGreen
Case ""
Me.YourComboBoxName.ForeColor = vbBlack
END SELECT
End Sub

A slightly better method might be to use a table with columns for the values
MILT, BRVT, etc and another column with the color number then you could grab
the color in your routine. You would need a multi-column combobox with the
first column having your values and the second column (with a width of zero)
having the color number. That way you would not need to change the sub if
you added additional

PrivateSub sSetColor()
IF Len(Me.ComboBoxName & "") > 0 then
Me.YourComboboxName.ForeColor = Me.YourComboboxName.Column(1)
End IF
End Sub

Signature

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

>I am building a database for an HR department. I have a combo box in a
>form
> with such choices as MILT, BRVT, TARDY, etc. I am looking to have each
> have
> it's own color when chosen. I have more than 3 so I am guessing I need to
> code it, but I have no idea how. Can someone help????

Reply to this Message

graypaarmy - 25 Feb 2008 16:08 GMT

Sorry John, I am new at this and am trying to understand. Here is what I put
in and nothing happened....

Private Sub Type_of_Incident_AfterUpdate()
Private Sub sSetColor()
Select Case Me.Type_of_Incident & ""
Case "MILT"
Me.Type_of_Incident.ForeColor = vbRed
Case "BRVT"
Me.Type_of_Incident.ForeColor = vbGreen
Case ""
Me.Type_of_Incident.ForeColor = vbBlack
End Select
End Sub

End Sub

I may not be putting this in the right place....I don't know. Please bare
with me and please help...lol.

> You would be able to do this only if you are displaying a single form - a
> continuous form cannot (to my knowledge) be coded to display different
[quoted text clipped - 32 lines]
> > it's own color when chosen. I have more than 3 so I am guessing I need to
> > code it, but I have no idea how. Can someone help????

Reply to this Message

Ron2006 - 25 Feb 2008 16:21 GMT

Three additional things:

1) Formating like this WILL NOT operate in a "Datasheet" view.
Conditional formating is the only thing that will function.

2) Continuous form will sort of work but it will show ALL of the forms
the same as the one it is sitting on, so for all practical purposes it
will also NOT operate properly for "Continuous" forms.

3) This code ALSO needs to be duplicated in the OnCurrent event of the
form.

Ron

Reply to this Message

John Spencer - 25 Feb 2008 17:03 GMT

Well, I would rather bear with you than bare with you. The latter could
cause me lots of grief.

Add the Private Sub sSetColor as a separate function. That you call from
the after update event and from the form's current event

Private Sub Type_of_Incident_AfterUpdate()
'When you change the selection in Type_of_Incident
sSetColor
End Sub

Private Sub Form_Current()
'When you move to a new record
sSetColor
End Sub

Private Sub sSetColor()

Select Case Me.Type_of_Incident & ""
Case "MILT"
Me.Type_of_Incident.ForeColor = vbRed
Case "BRVT"
Me.Type_of_Incident.ForeColor = vbGreen
Case ""
Me.Type_of_Incident.ForeColor = vbBlack
End Select

End Sub

Oh, and sorry about the grammar lesson. It just strikes me as funny how
many people manage to get the wrong bear/bare in that statement.

Signature

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

> Sorry John, I am new at this and am trying to understand. Here is what I
> put
[quoted text clipped - 16 lines]
> I may not be putting this in the right place....I don't know. Please bare
> with me and please help...lol.

Reply to this Message

graypaarmy - 25 Feb 2008 17:26 GMT

thanks for the grammer lesson....lol. I hat to say it but I copied and
pasted exactly what you have here and it still don't work. I guess a good
thing is that I didn't get an error message. What else could it be???

> Well, I would rather bear with you than bare with you. The latter could
> cause me lots of grief.
[quoted text clipped - 47 lines]
> > I may not be putting this in the right place....I don't know. Please bare
> > with me and please help...lol.

Reply to this Message

John Spencer - 25 Feb 2008 17:46 GMT

If Type_of_Incident control is a combobox, what are the columns in the
combobox?

Is Type_of_Incident based on a LOOKUP field? If so, what you see ain't
necessarily what you get. You could be seeing text and storing numbers. In
that case, the code would work, but it would never see the actual value of 1
or 2 or 3 (stored value).

Signature

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

> thanks for the grammer lesson....lol. I hat to say it but I copied and
> pasted exactly what you have here and it still don't work. I guess a good
[quoted text clipped - 53 lines]
>> > bare
>> > with me and please help...lol.

Reply to this Message

graypaarmy - 25 Feb 2008 17:55 GMT

The type of incident control is a combo box in a subform.

> If Type_of_Incident control is a combobox, what are the columns in the
> combobox?
[quoted text clipped - 61 lines]
> >> > bare
> >> > with me and please help...lol.

Reply to this Message

graypaarmy - 25 Feb 2008 18:12 GMT

sorry, it's actually a lookup field.......maybe it can't happen?

> The type of incident control is a combo box in a subform.
>
[quoted text clipped - 63 lines]
> > >> > bare
> > >> > with me and please help...lol.

Reply to this Message

John Spencer - 25 Feb 2008 20:17 GMT

No, it can still happen.
Now you have to do some work and open the table that the Lookup field is
attached to. What is the structure of the data in that table?
If it is two columns - one column with a number and one with the text then
you will have to put the equivalent values in the Case statements. They
will read more like

Select Case Me.Type_of_Incident & ""
Case "1"
Me.Type_of_Incident.ForeColor = vbRed
Case "2"
Me.Type_of_Incident.ForeColor = vbGreen
...

Case "12"
Me.Type_of_Incident.ForeColor = 12713921 'Pale green
End Select

Signature

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

> sorry, it's actually a lookup field.......maybe it can't happen?
>
[quoted text clipped - 75 lines]
>> > >> > bare
>> > >> > with me and please help...lol.

Reply to this Message

graypaarmy - 25 Feb 2008 21:07 GMT

i am not very good at explaining.....but i will try. This lookup field is
attatched to a table named penalties, with 5 columns (employee id, last name,
first name, date of incident, type of incident). I want the users to be able
to make changes on the form and it change on my table. What i want the
colors for is if an employee would call off of work because of military
leave, but not bring in an excuse, the word MILT would be chosen and turn
red. But, if they were to bring in an excuse, then it would be blue. I
figure I will have to name one b-MILT and one r-MILT but that would be easy
once i figure out how to C.F. each one.

> No, it can still happen.
> Now you have to do some work and open the table that the Lookup field is
[quoted text clipped - 93 lines]
> >> > >> > bare
> >> > >> > with me and please help...lol.

Reply to this Message

graypaarmy - 25 Feb 2008 21:11 GMT

oh, btw...i used the lookup wizard, and typed in the values that I
wanted....they are not part of any of my tables. hope that helps.

> I am building a database for an HR department. I have a combo box in a form
> with such choices as MILT, BRVT, TARDY, etc. I am looking to have each have
> it's own color when chosen. I have more than 3 so I am guessing I need to
> code it, but I have no idea how. Can someone help????

Reply to this Message