asked on

Datasheet background color

I am display a query in a datasheet and, based on a column's value I want to be able
to change the background color for the row that contains the value.

For example:

Based on the values of column Key, I need to set each row's background color
to red, green or orange if the value of Key for that row is <0, =0 or >0 respectively.

The datasheet view:

Key Data
---- ---------------
-1 Some comment <-- Background needs to be RED
-4 Some other date <-- Background needs to be RED
0 More data <-- Background needs to be GREEN
3 Testing <-- Background needs to be ORANGE
5 Aaaaaaa <-- Background needs to be ORANGE

It doesn't need to be a datasheet view, I'm happy to use a tabular form layout if
this can be achieved that way. (or any other way for that matter).

The closest I've come to this functionality is the format property
but that only changes the foreground color of the control to which it is applied.

Appreciate your help

kulikuli

Probably most experts will tell you this is not possible. However, I figured out how to do this. If you don't get the appropriate answer from another expert you know the only solution is the one I got. I can send a sample to you via email if you accept my answer.

Trygve

Is it a secret solution or is it possible for the rest of us to have a look too ?
(This sounds interesting) ;- )

devtha

What I do is create a continuous datasheet form and then check for the value in one of the fields and then if the value matches then in that row change the backcolor = red for all the fields. I am sure there is another way of changing the color of all the fields by finding the selected row... But theis works..

If Me.[fldEventCategory] = "copy" Then
Me.fldEventCategory.BackColor = RGB(255, 0, 0)
Me.[fldCategoryDescription].BackColor = RGB(255, 0, 0)
End If

zebada

ASKER

Thanks for the answer devtha, but that is what I tried first. Unfortunately if you change the background color for a given control then the background color is changed for ALL rows in which that control is displayed. What I am looking for is to only change the row(s) for which the control contains a certain value (or range of values).

Now on to KULIKULI's solution for the impossible. If you can do this KULIKULI and you tell me how, you get the points. (That's the whole idea isn't it?)
Thanks in advance
zebada.

eugeneo

Ok, I have a really strange solution but it works :)
What you have to do is create a regular tabular form, and then make a textbox the size of the whole detail section. Change the "ForeColor" to red, and make the font-size HUGE. (Make sure that when you put underscores in the box it basically turns all red.) Then, move the textbox to the back. Finally, change the recordsource to: =Iif(Key<0,"_______","")
Repeat the process for the orange and green boxes with the appropriate Iif expressions.
Hope this helps!

eugeneo

Sorry I meant Control Source, not recordsource:)

devtha

I am also interested in the secret solution.....

zebada

ASKER

Thank-you Eugeneo for your wisdom,
I'm not rejecting your answer which by the way was very good.
I'm just allowing KULIKULI the opportunity to propse an answer.
If KULIKULI does not propose an answer or it is rejected then, Eugeneo could you
please repost your answer and I'll give you the points.

shaloo

Joining in this discussion late. My understanding is also that what you require cannot be done. However, you could achieve the same 'user interaction' result by creating a continuous form on a temp table which has one additional field for each color but without your actual 'control field'. Then based on the ranges for your 'control field' values copy the values (number or text; whatever) into the appropriate new field. Then on the continuous form put these new fields on top of one another and choose the foreground (I know you wanted background) to the color for that range of values. When the form is displayed all 5 fields will actually show but per record; only one will actuall have data and that one will show with the right color. I realize this necessitates a tough query (with lots of iif) or a temp table but the result look nice are do not take much time to do.

zebada

ASKER

Thank-you Shaloo,
Another ingenious solution but as far as I can see, just a variation on the answer proposed by Eugeneo. I appreciate that it doesn't require setting font sizes to very large values etc.
However For anyone that is interested in implementing Eugeneos solution here are some more details I discovered:

With a font size of about 20 with a font called webdings and the character 'g' you can fill in a field so it appears as if its background color has been set.

When attempting to display data in one of a number of overlapping fields, set their backstyle to transparent.

Eugeneo's solution gets my vote so I'll allocate the points against that solution.
Earth to Eugeneo, Earth to Eugeneo, Earth to Eugeneo, please resubmit an answer and I'll allocate the points.

Regards to all who responded.

kulikuli

I just read the comments. I see you'd like to have my answer. I'll get back to you soon (today or tomorrow) because I'm very busy at this moment. I'll give you my code then.

Trygve, I'll put it in here so you can comment on it.

Best regards,

kulikuli

ASKER CERTIFIED SOLUTION

eugeneo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kulikuli

Eugenio's solution comes close to mine. Are you going to accept his or do you want mine?

kulikuli

I can send you a working sample is you like

zebada

ASKER

Thanks Eugeneo.

Kulikuli, I am interested in your solution so if you care to post it that would be great.
Sorry but I really think that Eugeneo gets the points because that solution was posted first.