Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Change Color based on Value

Posted on 2003-11-18
23
Medium Priority
?
865 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
  • 5
  • +1
23 Comments
 
LVL 32

Accepted Solution

by:
jadedata earned 800 total points
ID: 9774853
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
ID: 9775169
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
ID: 9775183
Sorry disregard the line that reads: "On Error GoTo ClearForm_Err".  That was from my original code
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 5

Expert Comment

by:morpheus30
ID: 9775195
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
ID: 9775210
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
ID: 9775237
everything under control there morpheus30???
0
 
LVL 5

Expert Comment

by:morpheus30
ID: 9775305
LOL...

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

Expert Comment

by:mcmcxlxl
ID: 9777127
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
ID: 9779651
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
ID: 9779701
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
ID: 9779839
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
 
LVL 32

Expert Comment

by:jadedata
ID: 9780008
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
ID: 9780080
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
ID: 9780234
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
ID: 9780267
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
ID: 9780337
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
ID: 9799505
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
ID: 9799516
Well Jack. I saw that in Access 2K. Unfortunately my guys still use Acess 97
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9799530
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
ID: 9799536
>>stay tuned
By that time, my guys wil upgarde to win 2K :-)
Have a nice weekend
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9799564
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
ID: 9799599
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
ID: 9799642
yes, it comes with library code and nominal implementation instructions and is in a demo database!
0

Featured Post

Independent Software Vendors: 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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

730 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