Solved

Conditional format in forms

Posted on 2004-04-22
12
353 Views
Last Modified: 2008-02-01
In a form (in continuous view), I have combo controls with possibility of entering values from 1 to 5.  I want for each value to have back color conditioned by the value : 1 : Red to 5 : green (see below)

Before, when I had only 4 values, I was using the conditional format of Access menu allowing you to put 4 different formats (including default) and it was working great

Now I have 5 choices, I cannot use it anymore and I have tries some code but it does not work,
On the after update event of the control named c1,c2....cx, I have put the following :

Private Sub c3_AfterUpdate()
If c3 = 1 Then
Me.c3.BackColor = RGB(255, 0, 0)
ElseIf c3 = 2 Then
Me.c3.BackColor = RGB(255, 144, 0)
ElseIf c3 = 3 Then
Me.c3.BackColor = RGB(235, 232, 0)
ElseIf c3 = 4 Then
Me.c3.BackColor = RGB(198, 225, 169)
ElseIf c3 = 5 Then
Me.c3.BackColor = RGB(0, 234, 72)
End If

End Sub

It works when I have only one record. When I am filling a second  record, the first record backcolour changes as well (which was not the case with the Access conditional format from the menu).

Please help
0
Comment
Question by:pixie2003
[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
  • 6
  • 5
12 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 10897339
I guess the field c3 isn't bound to a field in the query or table that fills the continuous form.
Just make sure the field is in the recordsource, otherwise Access will see it as one control and the color will indeed be the same for all rows :-(

Nic;o)
0
 

Author Comment

by:pixie2003
ID: 10897347
I have done this code but it does not work (error message : number of condition ....)
Private Sub c1_AfterUpdate()

Dim objfrc As formatcondition
Me.ActiveControl.FormatConditions.Delete
Set objfrc = Me.ActiveControl.FormatConditions.Add(acFieldValue, acEqual, 1)
Set objfrc = Me.ActiveControl.FormatConditions.Add(acFieldValue, acEqual, 2)
Set objfrc = Me.ActiveControl.FormatConditions.Add(acFieldValue, acEqual, 3)
With Me.ActiveControl.FormatConditions(0)
.BackColor = RGB(255, 0, 0)
End With
With Me.ActiveControl.FormatConditions(1)
.BackColor = RGB(0, 255, 0)
End With
Set objfrc = Me.ActiveControl.FormatConditions.Add(acFieldValue, acEqual, 4)
Set objfrc = Me.ActiveControl.FormatConditions.Add(acFieldValue, acEqual, 5)
With Me.ActiveControl.FormatConditions(3)
.BackColor = RGB(255, 200, 255)
End With
With Me.ActiveControl.FormatConditions(4)
.BackColor = RGB(200, 255, 0)
End With

End If
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10897582
Now you're switching to field c1, did yo try to bind c3 to a field from the recordsource ?

Nic;o)
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:pixie2003
ID: 10897642
c1,c2,c3 ...c12 are combo boxes in the continuous form.
By putting the above code in the after update event of the controls, it does work, but i cannot add up more than 3 conditions ? How can I add up more ?
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10897667
I thought you were trying to get your code from the question text working, as that's the "old fashioned" way and should work, atleast when the combo's are "bound".

Nic;o)
0
 

Author Comment

by:pixie2003
ID: 10897683
I tries to make up something to make something change the background color of the control and I took my c3 control as example and after I discovered the Formatcondition property. That's the question has evolved . But now I am stacked because i cannot addup more  than 3 conditions and i need 5.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10897742
So you'll need the "old" code.
When having twelve combo's with the same value you could use one sub like:

SUB SetBackColor()
If me.currentcontrol.value = 1 Then
me.currentcontrol.BackColor = RGB(255, 0, 0)
ElseIf c3 = 2 Then
me.currentcontrol.BackColor = RGB(255, 144, 0)
ElseIf c3 = 3 Then
me.currentcontrol.BackColor = RGB(235, 232, 0)
ElseIf c3 = 4 Then
me.currentcontrol.BackColor = RGB(198, 225, 169)
ElseIf c3 = 5 Then
me.currentcontrol.BackColor = RGB(0, 234, 72)
End If
End sub

And have in the afterupdate event just a call like:
call SetBackColor

Nic;o)
0
 

Author Comment

by:pixie2003
ID: 11056725
1 ) Can I use
"ElseIf currentcontrol.Value = 2 then" instead of "ElseIf c3=2 then"
2) Is this code not going to change all the colors of all the records in the continuous form to the back colour of the active control ?

0
 
LVL 54

Expert Comment

by:nico5038
ID: 11056818
1) Guess so, just try :-)
2) Access is picky on this when the controls aren't bound to a field. Bound fields should work OK.

Nic;o)
0
 

Author Comment

by:pixie2003
ID: 11056827
What is a "bound field" ? (Sorry, english is not my native Language !)
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 100 total points
ID: 11056889
A field that has a datasource (field from the table/query the form is based on) set. You'll see the name of the data source field in the white box.

Nic;o)
0
 
LVL 2

Expert Comment

by:ApK
ID: 11867660
In Access 2003, there is no CurrentControl property, and if I use ActiveControl instead, then the combo-box on every record changes colour. It is defintely a bound field.

Furthermore, I assume you want the formatting to apply as soon as you've opened the form, not just when you change the combo-box selection?

I wonder why Microsoft limited conditional formats to 3 (plus a default)?

Anyway, there is an alternative way of doing it here: http://support.microsoft.com/default.aspx?scid=kb;en-us;193207 (Microsoft Knowledge Base Article - 193207). It's rather hacky, but if it works, then so be it.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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 how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

726 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