Solved

Conditional format in forms

Posted on 2004-04-22
12
351 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

772 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