Conditional formatting on a combo box

[aeon]

Working in Access 2007 on XP

I have a combo box that I'm using in a form to select the WorkCategory assigned to a particular person in my database. WorkCategory is a lookup field in a table called Person. It is looking up the category type from another table called WorkCategory which is a list of 3 types of categories (offshore, onshore, visitor).

In the form, when I use the drop-down menu of the combo box to select one of the work categories, I want to have a different background colour for each.

I've been using conditional formatting on the combo box but cannot get it to work?

Tried the following:-

Condition 1: Field Value Is equal to "Offshore"
Condition 2: Field Value Is equal to "Onshore"
Condition 3: Field Value Is equal to "Visitor"

and

Condition 1: Expression Is [WorkCategory]="Offshore"
Condition 2: Expression Is [WorkCategory]="Onshore"
Condition 3: Expression Is [WorkCategory]="Visitor"

and

Condition 1: Expression Is WorkCategory="Offshore"
Condition 2: Expression Is WorkCategory="Onshore"
Condition 3: Expression Is WorkCategory="Visitor"

Hope someone can help. Thanks

[Lysander]

Working in Access 2007 on XP

I have a combo box that I'm using in a form to select the WorkCategory assigned to a particular person in my database. WorkCategory is a lookup field in a table called Person. It is looking up the category type from another table called WorkCategory which is a list of 3 types of categories (offshore, onshore, visitor).

In the form, when I use the drop-down menu of the combo box to select one of the work categories, I want to have a different background colour for each.

I've been using conditional formatting on the combo box but cannot get it to work?

Tried the following:-

Condition 1: Field Value Is equal to "Offshore"
Condition 2: Field Value Is equal to "Onshore"
Condition 3: Field Value Is equal to "Visitor"

and

Condition 1: Expression Is [WorkCategory]="Offshore"
Condition 2: Expression Is [WorkCategory]="Onshore"
Condition 3: Expression Is [WorkCategory]="Visitor"

and

Condition 1: Expression Is WorkCategory="Offshore"
Condition 2: Expression Is WorkCategory="Onshore"
Condition 3: Expression Is WorkCategory="Visitor"

Hope someone can help. Thanks

You could be asking for two things, and I am not sure what one it is.

If you want to set the colour of the combo box, after you have selected the option, then use the after_update event and set the colour depending upon the value.
e.g.
if cboWorkCategory="Offshore" then cboWorkCategory.BackColor=-2147483633

If you want to display the list of 3 options, with 3 different colours at the same time, I don't think you can do this easily. You could use an option group of the 3 values, so 3 different buttons with 3 different colours.

[aeon]

Thanks for your help...I tried using if statements but this did not work. I've also tried a select case as below...this has not worked either.

Private Sub WorkCategory_AfterUpdate()
 
Select Case Me.WorkCategory & ""
Case "Offshore"
    Me.WorkCategory.BackColor = vbBlue
Case "Onshore"
    Me.WorkCategory.BackColor = vbGreen
Case "Visitor"
    Me.WorkCategory.BackColor = vbRed
End Select
 
End Sub

[Lysander]

Thanks for your help...I tried using if statements but this did not work. I've also tried a select case as below...this has not worked either.

Private Sub WorkCategory_AfterUpdate()

Select Case Me.WorkCategory & ""
Case "Offshore"
Me.WorkCategory.BackColor = vbBlue
Case "Onshore"
Me.WorkCategory.BackColor = vbGreen
Case "Visitor"
Me.WorkCategory.BackColor = vbRed
End Select

End Sub

I have just knocked up a short test on one of my forms, and this works

Private Sub Mandal_ID_AfterUpdate()
If Mandal_ID = 1 Then
    Mandal_ID.BackColor = vbBlue
Else
    Mandal_ID.BackColor = -2147483643
End If
End Sub
 

Try adding the following to your code, to see if you actually getting the correct value, as I made that mistake, testing for Mandal Name, when the box actually held Mandal ID

Select Case Me.WorkCategory & ""
Case "Offshore"
    msgbox "TEST, I HAVE GOT HERE"
    Me.WorkCategory.BackColor = vbBlue
Case "Onshore"
    Me.WorkCategory.BackColor = vbGreen
Case "Visitor"
    Me.WorkCategory.BackColor = vbRed
End Select