Colouring items in a list

[Bug16]

I'm currently displaying about 100 items in a list that is populated via a SQL statement. These are the condition of certain items.

I now want to be able to colour the items in the list depending on their condition. I.e. Green = good, Red = bad.

I'm beginning to suspect this can't actually be done. Obviously it's easy to colour the list itself but not the individual items in the list.

 

[wbwillson]

Is your list on a form or report? You can set the conditional formatting properties for a report (or use VBA), I use this to color individual fields which are null in my report so I can quickly see what I'm missing. If its for a form you can set the color via VBA.

Example:

Private Sub fldCondition_AfterUpdate()

If fldCondition.Value = "GOOD" Then
fldLastName.ForeColor = 65280 'Green
Else
fldLastName.ForeColor = 255 'Red
End If

End Sub

Bill

 

[TonyJollans]

Bug16,

To reiterate wbwillson, what sort of list are you talking about?


wbwillson,

I love the idea of coloured nulls!

But seriously what do you mean by conditional formatting? The only way I know to colour individual items in a report is with a bit of VBA as per your example and I didn't think you could do it all in a form. There isn't a Form equivalent of a Report Detail 'On Print' Event. Please tell me more.

Thanks,
Tony

 

[wbwillson]

Tony,

When in the design view of a report, selecting a field then going to the Top Menu Bar and selecting "Format > Conditional Formatting" will bring up the "Conditional Formatting" dialog box. In this dialog you can enter an expression for up to 3 criteria. Example: fldCondition.Value = "Good", you then change the font weight, color, background etc..if this criteria is met.

For the form, I usually place my VBA in the OnChange event so that it looks at the form every time the user switches to a new record. Maybe I'm doing it wrong, but it works for me.

Bill

 

[TonyJollans]

Thanks, Bill, but Conditional Formatting does not show in my Access 97. Is it new in a later version?

As far as Forms go it's fine in Single Form mode but in Continuous Forms mode don't you affect every record? Or is my version out of date here as well?

Thanks,
Tony

 

[wbwillson]

Tony,

I'm using Access 2000 so maybe it is a new feature in this version. Also my forms are all single form, but could you set the code in each fields Got Focus event, and then have some code do a quick "run-through" setting the focus on all fields on the form to achieve the effect?

Bill

 

[Bug16]

The list is actually an Access Form listbox. Apologies for not be clearer.

You cannot use Conditional Formatting on the listbox.

 

[TonyJollans]

Bug16,

I'm pretty sure that it can't be done. Format options for listbox contents are fairly limited.

Tony

 

[Bug16]

As I suspected then. Thanks to all for the advice!

 

[compucop]

bug I've posted on this topic numerous times. You need to use a listview control, if you are using access 2000. Search for conditionally formatting a listbox on this site, i posted the code. If you can't find post back. Later.

 

[Bug16]

Splendid! I found the thread and it works a treat!

Cheers!