Solved

Change line color in continuous sub form based on textbox value

Posted on 2002-04-05
7
1,259 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

815 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

11 Experts available now in Live!

Get 1:1 Help Now