Solved

Change Color based on Value

Posted on 2003-11-18
23
823 Views
Last Modified: 2008-02-01
I have a form. The Data source for this is a table. I want to change colors for certain fields based on the value. Like

col1       col2          col3                col4 col5 col6 cols
aaa        abc          123232323      Aa3  AA  Aa2  AA

If you notice the value of col4 and col5 are yesterday and today value and same for col6 and col7.

Question

1.How do I write a something

If col4 <> col5  then color=red

2.At which palce in MS access 97 db I put this ?


Sorry I am MS- Access ignorant

Thanks
0
Comment
Question by:jitganguly
  • 9
  • 8
  • 5
  • +1
23 Comments
 
LVL 32

Accepted Solution

by:
jadedata earned 200 total points
Comment Utility
Hey jitganguly!

 Are you telling me that your example shows all the possible determining data values to decide what color to make a form control?
 "If you notice the value of col4 and col5 are yesterday" - I don't see a yesterday in this at all.

 If the examples are not REAL data, show an example WITH REAL data and explain what colors you want to use based on which value or values.

regards
Jack
0
 
LVL 5

Expert Comment

by:morpheus30
Comment Utility
Open the form in design view and double click the upper left corner of the form where the horizontal and vertical rulers meet.  You'll notice a black dot appears in the middle of the square when you click on it.  If you double click it, you'll see the properties window open for that form.  Look for the Events tab.

Click the ellipsis (three dots) next to the onOpen event and type the following code:

On Error GoTo ClearForm_Err

Dim db As Database
Dim rs As Recordset
Dim sql As String
Dim ctlInput As Control
Dim strField As String

sql = "SELECT * FROM tableSource WHERE [Primary Key of TableSource] = " & Me.ControlWithPrimaryKeyValue

Set db = CurrentDB()
Set rs = db.OpenRecordset(sql)

If rs("Field1") <> rs("Field2") Then
For Each ctlInput In Me
    If intDataError Is TextBox  Then
      ctlInput.BackColor = 255 'This value changes the color to red.  Substitute for whatever color you want
    End If
Next
End If
0
 
LVL 5

Expert Comment

by:morpheus30
Comment Utility
Sorry disregard the line that reads: "On Error GoTo ClearForm_Err".  That was from my original code
0
 
LVL 5

Expert Comment

by:morpheus30
Comment Utility
If you want to test the change of color in your forms before making it fancy, do it like this...

Dim ctlInput As Control
Dim strField As String

For Each ctlInput In Me
    If intDataError Is TextBox  Then
      ctlInput.BackColor = 255 'This value changes the color to red.  Substitute for whatever color you want
    End If
Next
0
 
LVL 5

Expert Comment

by:morpheus30
Comment Utility
Man, I'm getting tired....  Sorry, but USE THIS CODE:

Dim ctlInput As Control

For Each ctlInput In Me
    If ctlInput Is TextBox  Then
      ctlInput.BackColor = 255 'This value changes the color to red.  Substitute for whatever color you want
    End If
Next

0
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
everything under control there morpheus30???
0
 
LVL 5

Expert Comment

by:morpheus30
Comment Utility
LOL...

Too many distractions here make me overlook stuff...  I think we're cool here
0
 

Expert Comment

by:mcmcxlxl
Comment Utility
I think the solution is more easy.
In access (i have access2000 and i don't know if it exist in access 97), you can, for each control of a form, set a conditional format : menu format
(i have a french version, so i don't know the english name but it should looks like 'conditional format')

and more: for the background, you could make a very large control (with all other controls placed in front of it). This control has an expression that verifies the condition and use the same system to change the ( =([col4]<>[col5])
With the conditionnal format, set the color to red for this control and his text if his value =-1, for example)

Naturally, the columns must be in a form based on the table. You can't do that directly in a table

I hop it will help.
Michael Belgium

0
 
LVL 20

Author Comment

by:jitganguly
Comment Utility
Jack,
Its not real data. But which part u didn't understand. ?

col1       col2          col3                col4 col5 col6 col7
aaa        abc          123232323      Aa3  AA  Aa2  AA

col1 through col7 are labels i.e. column heading
I need to dynamicaully compare the values of col4 and col5 and change color based on different value i.e.

if col4 <> col5 then change color of col4
or
if col6<> col7 then change color of col7

0
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
I read and read and read, and then.... figured out that col2 did NOT mean color 2......
Meanwhile, after the next pot of coffee got made...

Build a routine that conducts the comparisons and color assignments


Private Sub SetCtrlColor()
  if me("txtField1") <> me("txtField2") then
    me("txtField1").backcolor = vbRed
  endif

  if me("txtField3") <> me("txtField4") then
    me("txtField4").backcolor = vbBlue
  endif

  if me("txtField5") <> me("txtField6") then
    me("txtField7").backcolor = vbGreen
  endif

End Sub

Call this routine from places like Form_Current, or the AfterUpdate of controls that have an impact of the color assignments.
Be sure to have the color assignments with the highest priority at the bottom of the routine so they run last.

I feel better now....
0
 
LVL 20

Author Comment

by:jitganguly
Comment Utility
Jack,
Using this code

If Me("MoodysT") <> Me("MoodysY") Then
    Me!MoodysT.BackColor = vbRed
End If

But its changing colro for all the rows of that column

Do I need to use some value/text property to compare ?

0
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.

 
LVL 32

Expert Comment

by:jadedata
Comment Utility
You will ONLY be able to do this on a single record form.
On a continuous form, while you see multiple occurances of a control they are handled as the same object.

You forgot to tell us about the continuous form thing.....
0
 
LVL 20

Author Comment

by:jitganguly
Comment Utility
But Jack, Is there a way I manipulate only that row ? Your code is working but  I need to do that only for that row

The first row is fine i.e. Moodyst is not equal to MoodysY, but rest of the rows are same i.e. MoodysT is same as MoodysY so it should not change colors.

I do understand that thsi validations exists in control level, but teher must be some control array or some kind of way to uniquley identiy the control in that row. and the question is how do I do that ?
0
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
Access does not have row-by-row conditional formatting like Excel
The phrase:  "There must be...." doesn't make it so.

The "column" in a continuous form is the same control, reiterated down the face of the form.  It is the same control with the data from each row of a dataset.
0
 
LVL 20

Author Comment

by:jitganguly
Comment Utility
I am not here to challenge you but what does this peice of code do ?

For x = 1 To Me.Controls.Count

If Me.Controls(x).ControlType = acTextBox Then
Me!Controls(x).Visible = False
End If
Next
0
 
LVL 20

Author Comment

by:jitganguly
Comment Utility
If this solution cannot be done in Access 97 then what is the solution ?

Basically I need to change color property based on data value on each row and how do I do that ?
0
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
are you working in XP or 2K?
There IS a new option called Conditional Formatting on the menu.
( I am still getting customers out of Acc97 projects...)

If you wish to slap me around a little, you're entitled
!!
0
 
LVL 20

Author Comment

by:jitganguly
Comment Utility
Well Jack. I saw that in Access 2K. Unfortunately my guys still use Acess 97
0
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
thanx for the question and I am still seeking a 97 routine that will do this.... stay tuned.
0
 
LVL 20

Author Comment

by:jitganguly
Comment Utility
>>stay tuned
By that time, my guys wil upgarde to win 2K :-)
Have a nice weekend
0
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
http://www.lebans.com/formatbycriteria.htm
Lets see if this gets you going in a better direction

Let me know.
0
 
LVL 20

Author Comment

by:jitganguly
Comment Utility
Nope.
Remember my criteria is

Highlight(or anything) a column in a row.

Can you modify this function to do that ?
0
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
yes, it comes with library code and nominal implementation instructions and is in a demo database!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

728 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

10 Experts available now in Live!

Get 1:1 Help Now