• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 610
  • Last Modified:

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
0
zebada
Asked:
zebada
  • 4
  • 4
  • 3
  • +3
1 Solution
 
kulikuliCommented:
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.
0
 
TrygveCommented:
Is it a secret solution or is it possible for the rest of us to have a look too ?
(This sounds interesting) ;- )
0
 
devthaCommented:
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

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
zebadaAuthor Commented:
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.
0
 
eugeneoCommented:
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!

0
 
eugeneoCommented:
Sorry I meant Control Source, not recordsource:)
0
 
devthaCommented:
I am also interested in the secret solution.....
0
 
zebadaAuthor Commented:
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.


0
 
shalooCommented:
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.
0
 
zebadaAuthor Commented:
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.

0
 
kulikuliCommented:
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
0
 
eugeneoCommented:
Resubmitting previous solution:
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!
0
 
kulikuliCommented:
Eugenio's solution comes close to mine. Are you going to accept his or do you want mine?
0
 
kulikuliCommented:
I can send you a working sample is you like
0
 
zebadaAuthor Commented:
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.


0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 4
  • 4
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now