Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Datasheet background color

Posted on 1998-08-18
15
Medium Priority
?
587 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

927 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