Solved

Conditional format in forms

Posted on 2004-04-22
12
350 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

932 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

14 Experts available now in Live!

Get 1:1 Help Now