Solved

Change line color in continuous sub form based on textbox value

Posted on 2002-04-05
7
1,252 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Expert Comment

by:TThorpe
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks Volibrawl that worked just fine.........
0
 

Expert Comment

by:alexisbr
Comment Utility
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
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…
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 the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

744 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

18 Experts available now in Live!

Get 1:1 Help Now