CONDITIONAL FORMATTING

  • Thread starter Thread starter graypaarmy
  • Start date Start date
G

graypaarmy

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????
 
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
--
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
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.
 
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
 
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.
--
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
thing is that I didn't get an error message. What else could it be???
 
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).



--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
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

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
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.
 
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.
 
Back
Top