Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Change Color based on Value

Posted on 2003-11-18
23
Medium Priority
?
869 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 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Industry Leaders: 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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

773 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