Change BackColor Help

[mattc66 via AccessMonster.com]

I have the below DLookup. My hope is to change the backcolor if the available
is 0 or less available in the qryICStockOnHand_C1. It doesn't fail, but it
doesn't change the color. Can anyone see my error?

If DLookup("[Available]", "qryICStockOnHand_C1", "[Item] = '" & [Item] &
"'") <= 0 Then
Me.Item.BackColor = vbYellow
Else
Me.Item.BackColor = vbWhite
End If

 

[Jeff Boyce]

Matt

Have you added a breakpoint in that expression so you can step through it
and see what value(s) it returns?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[mattc66 via AccessMonster.com]

Yes and it appears to go through it. I think my problem may be my form is a
datasheet.

Matt

Have you added a breakpoint in that expression so you can step through it
and see what value(s) it returns?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have the below DLookup. My hope is to change the backcolor if the
available

[quoted text clipped - 7 lines]

Me.Item.BackColor = vbWhite
End If

 

[mattc66 via AccessMonster.com]

I changed it to a continuous form. It now changes the background color.
However it changes the background color for all in the column. Would it be
possible to just change the one field?

Yes and it appears to go through it. I think my problem may be my form is a
datasheet.

Matt

[quoted text clipped - 11 lines]

 

[fredg]

I changed it to a continuous form. It now changes the background color.
However it changes the background color for all in the column. Would it be
possible to just change the one field?

Yes and it appears to go through it. I think my problem may be my form is a
datasheet.

Matt

[quoted text clipped - 11 lines]

Me.Item.BackColor = vbWhite
End If

You can't do this using VBA.
Delete all of that code.

Set the control source of an unbound control to:

= DLookup("[Available]", "qryICStockOnHand_C1", "[Item] = '" & [Item]
& "'")

I'll assume that expression above is returning the correct value.

Then use the Control's Conditional Formatting property.
Select the control. Click on Format + Conditional formatting.
Set Condition1 to:
Expression Is less than or equal to 0
Select the Yellow color.
Save it.

Oh, and you can use Form Datasheet View for this if you wish.

 

[mattc66 via AccessMonster.com]

That changes the color of the unbound field. Can it change the color of the
whole row?

I changed it to a continuous form. It now changes the background color.
However it changes the background color for all in the column. Would it be

[quoted text clipped - 8 lines]
You can't do this using VBA.
Delete all of that code.

Set the control source of an unbound control to:

= DLookup("[Available]", "qryICStockOnHand_C1", "[Item] = '" & [Item]
& "'")

I'll assume that expression above is returning the correct value.

Then use the Control's Conditional Formatting property.
Select the control. Click on Format + Conditional formatting.
Set Condition1 to:
Expression Is less than or equal to 0
Select the Yellow color.
Save it.

Oh, and you can use Form Datasheet View for this if you wish.

 

[Jeff Boyce]

As far as I know, the conditional formatting works on controls.

If you want all the controls in the "row" to change color, you might have to
set conditional formatting on each one...

Regards

Jeff Boyce
Microsoft Office/Access MVP

That changes the color of the unbound field. Can it change the color of
the
whole row?

I changed it to a continuous form. It now changes the background color.
However it changes the background color for all in the column. Would it
be

[quoted text clipped - 8 lines]

Me.Item.BackColor = vbWhite
End If

You can't do this using VBA.
Delete all of that code.

Set the control source of an unbound control to:

= DLookup("[Available]", "qryICStockOnHand_C1", "[Item] = '" & [Item]
& "'")

I'll assume that expression above is returning the correct value.

Then use the Control's Conditional Formatting property.
Select the control. Click on Format + Conditional formatting.
Set Condition1 to:
Expression Is less than or equal to 0
Select the Yellow color.
Save it.

Oh, and you can use Form Datasheet View for this if you wish.

 

[fredg]

That changes the color of the unbound field. Can it change the color of the
whole row?

I changed it to a continuous form. It now changes the background color.
However it changes the background color for all in the column. Would it be

[quoted text clipped - 8 lines]

Me.Item.BackColor = vbWhite
End If

You can't do this using VBA.
Delete all of that code.

Set the control source of an unbound control to:

= DLookup("[Available]", "qryICStockOnHand_C1", "[Item] = '" & [Item]
& "'")

I'll assume that expression above is returning the correct value.

Then use the Control's Conditional Formatting property.
Select the control. Click on Format + Conditional formatting.
Set Condition1 to:
Expression Is less than or equal to 0
Select the Yellow color.
Save it.

Oh, and you can use Form Datasheet View for this if you wish.

Your original post just tried to set the Item control's color. If you
wanted the row it would have been nice to have you ask for that at
that time.

Expression is
[Item] <= 0

Place that expression in the conditional formatting property of EACH
of the other controls you wish to change the color of. Write it
exactly as I have (I assume the name of the control is [Item]).

In looking at my previous reply, I had meant to write:
Set Condition1 to Field Value is.
That would be correct for setting just the one control's back color.
You can leave that as is, but all of the other controls dependent upon
the [Item] value needs to read
Expression Is.

 

[mattc66 via AccessMonster.com]

Thanks that works.. Sorry for not being clear at first.

That changes the color of the unbound field. Can it change the color of the
whole row?

[quoted text clipped - 23 lines]
Your original post just tried to set the Item control's color. If you
wanted the row it would have been nice to have you ask for that at
that time.

Expression is
[Item] <= 0

Place that expression in the conditional formatting property of EACH
of the other controls you wish to change the color of. Write it
exactly as I have (I assume the name of the control is [Item]).

In looking at my previous reply, I had meant to write:
Set Condition1 to Field Value is.
That would be correct for setting just the one control's back color.
You can leave that as is, but all of the other controls dependent upon
the [Item] value needs to read
Expression Is.