Solved

Datasheet background color

Posted on 1998-08-18
15
529 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
 
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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 200 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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

705 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now