Conditional formatting in a form

Question

Conditional formatting in a form

peter478

SSCrazy

Points: 2939
More actions
October 11, 2011 at 5:54 am

#73819

Hello ,
I have a table displayed in a form in access .(data are stored in mssql2000)
I need to mark " id " (in red) that meets condition.
---------------------
' sp1 (float)
' id (int) if condition is valid this field need to be marked in red.
' OK (int)
' NOK(int)
' storno (int)
-------------------------------
Table SO contain these fields:
id sp1 OK NOK storno Text10
1 2.5 100 20 0 10.2
2 6.5 205 15 0 2
3 7.1 99 3 0 1.5
-------------------------------
In form is created text10 field (from this field I call function that calculate every record from table, and result is float number e.g. 10.2)
When I open this form i call code:
Private Sub Form_Open(Cancel As Integer)
Me.Requery
Me.RecordSource = "SELECT dbo.SO.* FROM dbo.SO WHERE (storno = 0)ORDER BY id DESC"
'*********** this doesnt work********'
If Me.sp1 > Me.Text10 Then
Me.id.BackColor = RGB(255, 0, 0)
End If
'********************************'
End Sub
Thanks for your help!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

WendellB SSCrazy Eights Points: 8678 More actions · Answer 1

Depending on the version of Access you are using, you may be able to do this without using VBA. Several questions about your situation:

What version (or versions) of Access are you using?

Is the form (or subform) a continuous form, or does it display just a single record?

Is this an unbound form, and is that why you are setting the data source?

Have you tried the conditional formatting feature in Access?

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!

peter478 SSCrazy Points: 2939 More actions · Answer 2

Depending on the version of Access you are using, you may be able to do this without using VBA. Several questions about your situation:

1.What version (or versions) of Access are you using?

2.Is the form (or subform) a continuous form, or does it display just a single record?

3.Is this an unbound form, and is that why you are setting the data source?

4.Have you tried the conditional formatting feature in Access?

1. I use Access2007

2. yes it is continues form similar to a table

3. yes it is unbound ...and I need to filter some data in the form via data source.

4. yes through conditional formatting it works ...right now I found solution.

but is there a way how to solve it via VBA?

I would like to colour whole row, without setting conditional formatting for every field.

Many thanks

WendellB SSCrazy Eights Points: 8678 More actions · Answer 3

peter478 (10/11/2011)
1. I use Access2007
2. yes it is continues form similar to a table
3. yes it is unbound ...and I need to filter some data in the form via data source.
4. yes through conditional formatting it works ...right now I found solution.
but is there a way how to solve it via VBA?
I would like to colour whole row, without setting conditional formatting for every field.
...

The problem is that in general the properties for objects on a continuous form apply to all records, which is why conditional formatting was implemented beginning with Access 2000. So there isn't any other good way. You can manipulate conditional formatting using VBA as described in this MSDN Article, but your example doesn't appear to need to change the conditional formatting dynamically.

You might find these topics on other forums that focus more on Access to be useful as well:

Conditional Formatting vba code

Background color of forms controls

Fun Conditional Formatting Access Question

Hope this helps put things in perspective.

Wendell

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 4

if all the fields for id, sp1, and text10 are available in the form's record source, you could do something like this:

create two text boxes, same size, overlay them one on top of the other, and make the background "transparent". make sure that neither one is named the same as any of the fields.

for the first text box, use =IIf(Sp1>text10,id,Null) and make the fore color = red

for the other text box, use =IIf(Sp1>text10,Null,id) and make the fore color = black

WendellB SSCrazy Eights Points: 8678 More actions · Answer 5

Unfortunately, the font color property of the text box on a continuous form applies to all of the records being displayed, so as the records are displayed, all of the text boxes in that colunm will be set according to the result of the evaluation, resulting in a flicker as the records populate the form, and at the end they will be set according to the evaluation of the last record. Conditional formatting is the only way I know of to deal with this problem.

Wendell

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 6

WendellB (10/12/2011)
Unfortunately, the font color property of the text box on a continuous form applies to all of the records being displayed

Yes, that is why you would use TWO text boxes - when the IIf statement is evaluated, the False one is Null and does not display; the True one contains the value and does display.

WendellB SSCrazy Eights Points: 8678 More actions · Answer 7

Apologies, I didn't realize you were suggesting that as the conditional formatting expression. And I think the original poster has worked out a solution using conditional formatting.

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!