Is Null Testing for Combo On Opening Form

[Access User]

I have a combo box which has three possible values, e.g "Poor", "Fair" and
"Good". I have conditionally formatted each so that when they choose Poor
it's got a green background, Fair gets a yellow and Poor gets a red
background treatment. When they open the form they see an empty data entry
field. The field is required entry. I would like the background to be Red
when they open the form to grab their attention.

I have tried the following VBA but get a 424 error:

Private Sub Form_Load()

If Me.TECHADEQ.Value Is Null Then
Me![TECHADEQ].BackColor = vbRed
End If

End Sub

Is there an other approach?

 

[Dirk Goldgar]

I have a combo box which has three possible values, e.g "Poor", "Fair" and
"Good". I have conditionally formatted each so that when they choose Poor
it's got a green background, Fair gets a yellow and Poor gets a red
background treatment. When they open the form they see an empty data entry
field. The field is required entry. I would like the background to be Red
when they open the form to grab their attention.

I have tried the following VBA but get a 424 error:

Private Sub Form_Load()

If Me.TECHADEQ.Value Is Null Then
Me![TECHADEQ].BackColor = vbRed
End If

End Sub

Is there an other approach?

I haven't tried this out, but why not just set the control's normal
BackColor to red? That way, if any of the three possible values has been
chosen, the conditional formatting will set the color accordingly, but if
not, the default color of red will be used.

 

[Linq Adams via AccessMonster.com]

Dirk's suggestion seems the best approach to me, but for future reference the
correct syntax in Access VBA is not

If Me.TECHADEQ.Value Is Null Then

but rather

If IsNull(Me.TECHADEQ.Value) Then

 

[Access User]

I tried that but it had the effect of causing the look up list's responses to
be appear uniformly in RED (albeit with black foreground). I think that
looked kind of funky.

I have a combo box which has three possible values, e.g "Poor", "Fair" and
"Good". I have conditionally formatted each so that when they choose Poor
it's got a green background, Fair gets a yellow and Poor gets a red
background treatment. When they open the form they see an empty data entry
field. The field is required entry. I would like the background to be Red
when they open the form to grab their attention.

I have tried the following VBA but get a 424 error:

Private Sub Form_Load()

If Me.TECHADEQ.Value Is Null Then
Me![TECHADEQ].BackColor = vbRed
End If

End Sub

Is there an other approach?

I haven't tried this out, but why not just set the control's normal
BackColor to red? That way, if any of the three possible values has been
chosen, the conditional formatting will set the color accordingly, but if
not, the default color of red will be used.

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

(please reply to the newsgroup)

 

[Access User]

Unless I'm mistooked, this approach via VBA has the same net effect on the
display as Dirk's.

 

[Stockwell43]

Hi Access User,

Try this:

If IsNull(TECHADEQ) = True Then
Me.TECHADEQ.BackColor = vbRed
End If

 

[Access User]

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.

Hi Access User,

Try this:

If IsNull(TECHADEQ) = True Then
Me.TECHADEQ.BackColor = vbRed
End If

I have a combo box which has three possible values, e.g "Poor", "Fair" and
"Good". I have conditionally formatted each so that when they choose Poor
it's got a green background, Fair gets a yellow and Poor gets a red
background treatment. When they open the form they see an empty data entry
field. The field is required entry. I would like the background to be Red
when they open the form to grab their attention.

I have tried the following VBA but get a 424 error:

Private Sub Form_Load()

If Me.TECHADEQ.Value Is Null Then
Me![TECHADEQ].BackColor = vbRed
End If

End Sub

Is there an other approach?

 

[Stockwell43]

Try this in your Forms Current Event

If IsNull(Me.txtDateFrom) = "True" Then
Me.txtDateFrom.BackColor = "255"

ElseIf IsNull(Me.txtDateFrom) = "False" Then
Me.txtDateFrom.BackColor = "16777215"
Else
End If

I just tried it in a test database and it worked fine, give a go.

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.

Hi Access User,

Try this:

If IsNull(TECHADEQ) = True Then
Me.TECHADEQ.BackColor = vbRed
End If

I have a combo box which has three possible values, e.g "Poor", "Fair" and
"Good". I have conditionally formatted each so that when they choose Poor
it's got a green background, Fair gets a yellow and Poor gets a red
background treatment. When they open the form they see an empty data entry
field. The field is required entry. I would like the background to be Red
when they open the form to grab their attention.

I have tried the following VBA but get a 424 error:

Private Sub Form_Load()

If Me.TECHADEQ.Value Is Null Then
Me![TECHADEQ].BackColor = vbRed
End If

End Sub

Is there an other approach?

 

[Stockwell43]

Sorry, I forgot to change the name:

If IsNull(Me.TECHADEQ) = "True" Then
Me.txtDateFrom.BackColor = "255"

ElseIf IsNull(Me.TECHADEQ) = "False" Then
Me.txtDateFrom.BackColor = "16777215"
Else
End If

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.

Hi Access User,

Try this:

If IsNull(TECHADEQ) = True Then
Me.TECHADEQ.BackColor = vbRed
End If

I have a combo box which has three possible values, e.g "Poor", "Fair" and
"Good". I have conditionally formatted each so that when they choose Poor
it's got a green background, Fair gets a yellow and Poor gets a red
background treatment. When they open the form they see an empty data entry
field. The field is required entry. I would like the background to be Red
when they open the form to grab their attention.

I have tried the following VBA but get a 424 error:

Private Sub Form_Load()

If Me.TECHADEQ.Value Is Null Then
Me![TECHADEQ].BackColor = vbRed
End If

End Sub

Is there an other approach?

 

[Access User]

Hi,

I think you meant to say try this

If IsNull(Me.TECHADEQ) = "True" Then
Me.TECHADEQ.BackColor = "255"

ElseIf IsNull(Me.TECHADEQ) = "False" Then
Me.TECHADEQ.BackColor = "16777215"
Else
End If

but it seemed to not have the effect on the lookup list's background color
we're looking to get, i.e. to display white background.

Sorry, I forgot to change the name:

If IsNull(Me.TECHADEQ) = "True" Then
Me.txtDateFrom.BackColor = "255"

ElseIf IsNull(Me.TECHADEQ) = "False" Then
Me.txtDateFrom.BackColor = "16777215"
Else
End If

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.

Hi Access User,

Try this:

If IsNull(TECHADEQ) = True Then
Me.TECHADEQ.BackColor = vbRed
End If



:

I have a combo box which has three possible values, e.g "Poor", "Fair" and
"Good". I have conditionally formatted each so that when they choose Poor
it's got a green background, Fair gets a yellow and Poor gets a red
background treatment. When they open the form they see an empty data entry
field. The field is required entry. I would like the background to be Red
when they open the form to grab their attention.

I have tried the following VBA but get a 424 error:

Private Sub Form_Load()

If Me.TECHADEQ.Value Is Null Then
Me![TECHADEQ].BackColor = vbRed
End If

End Sub

Is there an other approach?

 

[Dirk Goldgar]

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.

 

[Access User]

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

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)