?
Solved

Datasheet background color

Posted on 1998-08-18
15
Medium Priority
?
629 Views
Last Modified: 2009-12-16
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
Comment
Question by:zebada
  • 4
  • 4
  • 3
  • +3
15 Comments
 
LVL 5

Expert Comment

by:kulikuli
ID: 1959589
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
 
LVL 12

Expert Comment

by:Trygve
ID: 1959590
Is it a secret solution or is it possible for the rest of us to have a look too ?
(This sounds interesting) ;- )
0
 
LVL 6

Expert Comment

by:devtha
ID: 1959591
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
LVL 6

Author Comment

by:zebada
ID: 1959592
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
 

Expert Comment

by:eugeneo
ID: 1959593
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
 

Expert Comment

by:eugeneo
ID: 1959594
Sorry I meant Control Source, not recordsource:)
0
 
LVL 6

Expert Comment

by:devtha
ID: 1959595
I am also interested in the secret solution.....
0
 
LVL 6

Author Comment

by:zebada
ID: 1959596
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
 

Expert Comment

by:shaloo
ID: 1959597
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
 
LVL 6

Author Comment

by:zebada
ID: 1959598
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
 
LVL 5

Expert Comment

by:kulikuli
ID: 1959599
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
 

Accepted Solution

by:
eugeneo earned 800 total points
ID: 1959600
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
 
LVL 5

Expert Comment

by:kulikuli
ID: 1959601
Eugenio's solution comes close to mine. Are you going to accept his or do you want mine?
0
 
LVL 5

Expert Comment

by:kulikuli
ID: 1959602
I can send you a working sample is you like
0
 
LVL 6

Author Comment

by:zebada
ID: 1959603
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
This following write-up describes a different way to copy Lotus Notes Calendar to Outlook. Along with this, we will also learn the reason behind this NSF to PST migration. Users can prefer different procedures as per their convenience.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question