Is Null Testing for Combo On Opening Form

"Access User" wrote in message
...
As is true of the other profers, the effect this has is to not only color
the
background of the field being entered on the form but the background color
of
the three choices appearing in the look up list when the user clicks on
the
down pointing arrow. I would really prefer to have just the entry field on
the form showing up with the red background when it's value is nullity AND
the native black foreground and white background.


I was thinking that you wanted 4 different colors to indicate the states
Good, Fair, Poor, and Null. But I just realized you only want 3 colors to
represent these states, because you say you want the BackColor to be red if
the combo = "Poor" OR it is Null. In that case, you can just modify your
original format condition for "Poor" to change it to be:

Expression Is: [TECHADEQ] = "Poor" OR IsNull([TECHADEQ])

The normal BackColor of the combo would left set to white. I think that
ought to do it.

If you decide you *do* want 4 different colors for the 4 different states, I
believe you can do it using conditional formatting, but it's cumbersome
because conditional formatting supports only three format conditions for a
given control. So what you have to do is change at least one of the format
conditions on the fly.

To do this, you would set up the initial format conditions for your TECHADEQ
combo box as you currently have them. For example, they might be:

Condition1: Field Value Is / equal to / "Poor" -- BackColor =
magenta
Condition2: Field Value Is / equal to / "Fair" -- BackColor =
yellow
Condition3: Field Value Is / equal to / "Good" -- BackColor = green

Then you'd have code in the form's Current event to modify one of the format
conditions, along these lines:

'----- start of example code -----
Private Sub Form_Current()

With Me.TECHADEQ

If IsNull(.Value) Then
.FormatConditions(0).Modify acExpression, , "IsNull([TECHADEQ])"
.FormatConditions(0).BackColor = vbRed
Else
.FormatConditions(0).Modify acFieldValue, acEqual, "Poor"
.FormatConditions(0).BackColor = vbMagenta
End If

End With

End Sub
'----- end of example code -----

I'm not 100% sure this is going to show up right when you first open the
form; it may need some tinkering.


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

That's great! It works - my background's still white on the lookup list and
it's red whether it's null or = "Poor".

Brilliant!!

Thank you :-)

"Dirk Goldgar" wrote:

"Access User" wrote in message
...
As is true of the other profers, the effect this has is to not only color
the
background of the field being entered on the form but the background color
of
the three choices appearing in the look up list when the user clicks on
the
down pointing arrow. I would really prefer to have just the entry field on
the form showing up with the red background when it's value is nullity AND
the native black foreground and white background.


I was thinking that you wanted 4 different colors to indicate the states
Good, Fair, Poor, and Null. But I just realized you only want 3 colors to
represent these states, because you say you want the BackColor to be red if
the combo = "Poor" OR it is Null. In that case, you can just modify your
original format condition for "Poor" to change it to be:

Expression Is: [TECHADEQ] = "Poor" OR IsNull([TECHADEQ])

The normal BackColor of the combo would left set to white. I think that
ought to do it.

If you decide you *do* want 4 different colors for the 4 different states, I
believe you can do it using conditional formatting, but it's cumbersome
because conditional formatting supports only three format conditions for a
given control. So what you have to do is change at least one of the format
conditions on the fly.

To do this, you would set up the initial format conditions for your TECHADEQ
combo box as you currently have them. For example, they might be:

Condition1: Field Value Is / equal to / "Poor" -- BackColor =
magenta
Condition2: Field Value Is / equal to / "Fair" -- BackColor =
yellow
Condition3: Field Value Is / equal to / "Good" -- BackColor = green

Then you'd have code in the form's Current event to modify one of the format
conditions, along these lines:

'----- start of example code -----
Private Sub Form_Current()

With Me.TECHADEQ

If IsNull(.Value) Then
.FormatConditions(0).Modify acExpression, , "IsNull([TECHADEQ])"
.FormatConditions(0).BackColor = vbRed
Else
.FormatConditions(0).Modify acFieldValue, acEqual, "Poor"
.FormatConditions(0).BackColor = vbMagenta
End If

End With

End Sub
'----- end of example code -----

I'm not 100% sure this is going to show up right when you first open the
form; it may need some tinkering.


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)