Solved

Change line color in continuous sub form based on textbox value

Posted on 2002-04-05
7
1,261 Views
Last Modified: 2008-03-04
Hey guys, I have a continuous subform with several text boxes and a box behind them. what I need to do is have the background color of the box change based on the value of one of the text boxes. Example: (field status) if textbox value = "current" then background color should be green, If status = "delinquent" then background color should be red. How do I acheive this ?...

Thanks for any help...

M568.....
0
Comment
Question by:Monty Miller
7 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 6921607
Check out:
http://www.mvps.org/access/forms/frm0024.htm

(Many fine other stuff too on that site)

Nic;o)
0
 
LVL 9

Accepted Solution

by:
Volibrawl earned 100 total points
ID: 6921639
This question is frequently asked.  In my opinion, there is no "good" way to do it.  But, at least you are asking for a single box, rather that the whole line.  I have a simple technique that is pretty effective for that. Just follow these instrucions and you will have it working in less than 2 minutes.

This is the technique for  "changing" the background color of a field based on some condition.  It does not REALLY change the background color, but it does appear that way for all purposes.

Assumptions:
You have a field on the form/report named DueDate.
You want all DueDate's that are passed today's date to have an Orange Background (red is bad 'cuz you can't see the text well).

1. Add a NEW standard text box to your form.  Let's call it Back1.

2. In the data source of BACK1, put the condition to check for and the following display text in the data source, in this case:
=iif([DueDate]<date(),"gggggggggg","".
This will fill the box with g's if the due date has passed.  Otherwise it will fill it with "nothing" ("").

3. Format this box as follows:
Font=Webdings       -> g is a solid block in Webdings
Size=14                       -> use any size but 14 is a good start, you need a size large enough to fill the box..
Foreground color=ORANGE      -> you will have a solid box of orange blocks  
Background color= normal bg color  -> whatever color you want for Condition NOT True.
You can set it sunken or outlined if you like.

4. Size the box appropriately and Place it directly BEHIND your REAL control.  Send it to the BACK.  Remove any tab stops and lock it.

5. Make your RealControl background Transparent.  Make the  foreground any color that will show on both colors (True and False )

~ Now, the Back1 field will "show thru" your REAL control and you will see the Solid Orange ColorBlocks appearing to be the background  when TRUE and the Normal background when FALSE.

You can "stack" as many different BackColors as you want, being sure that the Background is set to Transparent for all except the bottom one (it could be Transparent too if you like).

Be sure your REAL control is Sent to Front, otherwise you can't edit it.  Also be sure, all your Back controls have no tab stops and are locked to avoid accidentally tabbing into one.

cheers
0
 
LVL 9

Expert Comment

by:Volibrawl
ID: 6921658
To adapt the above specifically to your form, you would have say 4 NEW text boxes, utltimately all stacked right on top of each other. All would be formatted as wingding, but each would have a different "text" color.

BoxGreen=iif(status='current','ggggggggggg',"")
BoxRed  =iif(status='delinquent','ggggggggggg',"")
etc.

0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Expert Comment

by:TThorpe
ID: 6921739
If I understand the question correctly try something like this

Private Sub Form_Current()
   If IsNull(Text159) Then Exit Sub
    If Text159.Value > 5 Then
        Text159.BackColor = 255
    Else
        Text159.BackColor = -2147483643
    End If
End Sub


Tim
0
 

Author Comment

by:Monty Miller
ID: 6921879
TThorpe

This works only on the currently selected record and changes the background for all. I need the color to remain only for the records that meet the criteria.... I am using a continous form
0
 

Author Comment

by:Monty Miller
ID: 6921908
Thanks Volibrawl that worked just fine.........
0
 

Expert Comment

by:alexisbr
ID: 24927774
I don't know if you are still out there, Volibrawl, since this post was from 7 years ago.  However, I just wanted to say thank you for this wonderful explanation.  I have been reviewing posts all day and this is the first one that told me exactly what I needed to do to make the continuous form look like it was changing the background of the text box without using conditional formatting.  Since I have more than 3 conditions, conditional formatting was not an option for me.  This logic was very easy to implement but I would never have thought to do this.  Thanks again.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

839 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