[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Conditional format in forms

Posted on 2004-04-22
12
Medium Priority
?
363 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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 400 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

650 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