Microsoft Access Team Blog

PingBack from http://microsoft-sharepoint.simplynetdev.com/roll-your-own-%e2%80%9ctraffic-light%e2%80%9d-icon-set/

if you are using one of the 140 colors on the color chart you can write the formula with the color names instead of the hex

"<font face=Webdings size=1 color=" & IIf([Status]="Active","red",IIf([Status]="Resolved","yellow",IIf([Status]="Closed","forestgreen"))) & ">n </font>"

This is an excellent tip.  I was using conditional formatting to highlight certain percentages, but now I use this.  It looks much more professional.  Thanks Clint!

Clint,

Sorry to say this, but your status indicators are faulty.

Color-blind people may not be able to tell one of your status lights from another.

You need to provide a better way of doing those (see the ranking/grading dots style used in Popular Mechancs for a guide on what these dots should look like: fully shaded, only left/right half shaded, unshaded circle.

Oops...sorry I mean "Consumer Reports" and not "Popular Mechanics" previously.

Nice idea, thanks for posting it.

Instead of the multiple nested IIf functions, perhaps a better function to use would be the Choose or the Switch function.

Other than having the ability to use more then 3 conditions, does this have any other advantages over conditional formatting?

Gilad

I think “choose” is a great idea. In fact, what I would I do is create a table with two columns that drives the combo box and not have write any complex “iff” or “choose” code at all. I would simply base the combo box on a table with 2 columns like:

Status       StatusRTF

Active       <font face="Wingdings" size=4 color=red>l</font>

Resolved   <font face=Webdings size=4  color=yellow>l</font>

Closed      <font face=Webdings size=4 color=green>l</font>

Now, simply build the combo box with the above two columns, but set the 2nd column length as 0 so as to not display.  The traffic light text box can then be bound to the 2nd column of the combo box like:

 =([cboStatus].[Column](1))

If one needed lots of different text to display as a result of the combo choice, then setting the 2nd column in our combo table as a memo field and “enable” rich text editing would make this a breeze to maintain. One could change the color or character that displays without even having to type in any rich text mark up codes at all. You just edit the column to display whatever you want and no iff() or choose functions needed.

>Other than having the ability to use more then 3 conditions, does this have any other advantages over conditional formatting?

Well, for one thing using rich text means you can change the text value displayed. You can also change the font size. You can’t do either of these things with conditional formatting. So, I think this solution has more flexibility then conditional formatting.

I think flexibility is the key issue here. For example one poster pointed out the problem of people who can’t see colors well.  With rich text not only can we change the color, but we also very easy change the character (symbol) that displays for a given combo box. Some cities use a square + circle + triangle for the 3 traffic lights. So, now I just edit and we get:

Status       StatusRTF

Active     <font face="Wingdings" size=4 color=red>n</font>

Resolved  <font face= Wingdings 3 size=4 color=yellow>p</font>

Closed      <font face= Wingdings size=4 color=green>l</font>

Done! That was easy!

So, the flexibility of rich text starts to really shine and we easily have catered to the user request to accommodate people with color difficulties.

I think the cute example given here is to simply open up our minds that rich text offers many solutions for access developers.

We never had rich text support built into access. Now that we have this great feature then us developers can now start cooking up all kinds of new ideas and ways of doing things based on this feature.

I never gave the rich text concept much thought until the post here a few days ago about the cascading combo boxes. See my response to the cascading combo boxes. That combo box demo is cool, but it really opened up my mind as to what rich text can do for our applications.

So, it simply going to take some time for me to change my thinking here, and I am just starting to realize what a great new feature that rich text suport in a2007 is for us access developers.

Albert D. Kallal

Edmonton, Alberta Canada

kallal@msn.com

Wow, Albert, that's a great idea.

I wonder if this can actually replace conditional formatting which sometimes causes some flickering on the screen, and a delay in rendering.

I think there is still a need for situations where you can directly edit text and then have it change its formatting, in a way that depends on its content or on the content of other fields. This is different then the situation here where the format of one control is changed based on the content in another control, and where the set of values is predetermined, in this case three colors and shapes. So in this case the format of the combo itself is not effected.

I am not fluent in HTML but maybe there is a way to do just that. Maybe, just like in conditional formatting, you can have the format of a particular control changed based on some complex criteria that may or may not be dependant on the content of this control?

What do you think?

Gilad

>you can have the format of a particular control changed based on some complex criteria that may or may not be dependant on the content of this control?

Sure you can do this. You can always use the forms on-current event to set the value of some display text in a text box (and in 2007 that box can be rich text enabled).

However, if you are talking about a continues form, then the text box has to be bound to something (perhaps a function).

Remember, we also have a new image control in 2007 that also could have been used here. We could have bound the image control to a expression that resolves to one of 3 graphic images in a folder. That graphic could be a nice image of a stop, yield, and “go” graphic sign. And, the new image control works in continues forms. This means that we can now display different images in a continues form (and, we co do this without code).

So, for display of the graphic image or things like a flag of persons country as a graphic beside their name in a continues form, this is now possible in 2007. (and without even writing code).  This is really nice for us developers since then we don’t have to resort some 3rd party grid control or use an ActiveX listview control that allows graphics to repeat in a grid. We now have this ability native to ms-access.

We needed a new picture control for years and now access 2007 gave us one. This opens up a whole new class of applications in which developers can easily and safely use pictures in ms-access now.

Albert D. Kallal

Edmonton, Alberta Canada

kallal@msn.com