Solved

Conditional format in forms

Posted on 2004-04-22
12
349 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
  • 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
 

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

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

11 Experts available now in Live!

Get 1:1 Help Now