ConditionalFormat in VBA (1 Viewer)

[smig]

I want to color the background of a text box, in a continuous form, based on the color codes stored in the text box itself.

I know ConditionalFormat is the only way, as this is a continuous form

How can it be done?
I want it to be done on the "FormOpen" event.

Thanks

 

[theDBguy]

Hi. Maybe you could start here.

FormatCondition object (Access)

Office VBA reference topic

docs.microsoft.com

 

[smig]

Hi. Maybe you could start here.

FormatCondition object (Access)

Office VBA reference topic

docs.microsoft.com

Not helping me

 

[theDBguy]

Not helping me

Please explain what you don't understand with it, so we can guide you.

 

[theDBguy]

How about this one? (Look towards the bottom.)

Conditional Formats on Access Forms - vb123.com

Conditional formats let you flag exceptional values or highlight important results to your users. Rick Dobson shows you several easy (and powerful) ways to implement conditional formats and lower the bar dramatically when implementing conditional formats. The Access Properties window … Continue...

vb123.com

 

[theDBguy]

You could also take a look at this demo.

UtterAccess.com

www.utteraccess.com

 

[smig]

How about this one? (Look towards the bottom.)

Conditional Formats on Access Forms - vb123.com

Conditional formats let you flag exceptional values or highlight important results to your users. Rick Dobson shows you several easy (and powerful) ways to implement conditional formats and lower the bar dramatically when implementing conditional formats. The Access Properties window … Continue...

vb123.com

I think this one put me on the right track

 

[theDBguy]

I think this one put me on the right track

Good luck!

 

[smig]

Good luck!

Thanks

Is there any way to go over 3 Conditional formats (In VBA) ?
I think I read about it somewhere

 

[theDBguy]

Thanks

Is there any way to go over 3 Conditional formats (In VBA) ?
I think I read about it somewhere

If you have Access version 2010 and up, you can have up to 50 CFs.

Check out this demo.

Workaround for Format Conditions limited to 3

As many of you know, you can set lots (Some say 32, some say 50) of Format Conditions in Design View by right clicking a text box and Selecting Conditional Formatting. But you try it in code and you will get errors if you try to set more than 3. (Internally, there is a Microsoft BUG, not a...

www.access-programmers.co.uk

 

[smig]

If you have Access version 2010 and up, you can have up to 50 CFs.

Check out this demo.

Workaround for Format Conditions limited to 3

As many of you know, you can set lots (Some say 32, some say 50) of Format Conditions in Design View by right clicking a text box and Selecting Conditional Formatting. But you try it in code and you will get errors if you try to set more than 3. (Internally, there is a Microsoft BUG, not a...

www.access-programmers.co.uk

I still had error ""Error 7966. The format condition number you specified is greater than the number of format conditions."

So I looked for it and it seems Access can be real dumb
The error has nothing to do with the number of ConditionalFormats

This one won't work:

Dim strQRY As String
Dim i As Integer
Dim fc As FormatCondition

strQRY = "SELECT * FROM [11111] " & _
    "WHERE Not IsNoe([StatusColor]) "

Set rsStatus = CurrentDb.OpenRecordset(strQRY)

i = 0

With Me.StatusColor
    Me.StatusColor.FormatConditions.Delete
'    With rsStatus
        rsStatus.MoveFirst
        Do While Not rsStatus.EOF
            Set fc = Me.StatusColor.FormatConditions.Add(acExpression, acEqual, "[StatusColor] = " & rsStatus.Fields("StatusColor"), "")
            Set fc = Me.StatusColor.FormatConditions(i)
            fc.BackColor = Val(rsStatus.Fields("StatusColor"))
            i = i + 1
            rsStatus.MoveNext
        Loop
        rsStatus.Close
'    End With
End With

Set fc = Nothing

but using LIKE will work

Dim strQRY As String
Dim i As Integer
Dim fc As FormatCondition

strQRY = "SELECT * FROM [11111] " & _
    "WHERE Not IsNoe([StatusColor]) "

Set rsStatus = CurrentDb.OpenRecordset(strQRY)

i = 0

With Me.StatusColor
    Me.StatusColor.FormatConditions.Delete
'    With rsStatus
        rsStatus.MoveFirst
        Do While Not rsStatus.EOF
            Set fc = Me.StatusColor.FormatConditions.Add(acExpression, acEqual, "[StatusColor] like " & rsStatus.Fields("StatusColor"), "")
            Set fc = Me.StatusColor.FormatConditions(i)
            fc.BackColor = Val(rsStatus.Fields("StatusColor"))
            i = i + 1
            rsStatus.MoveNext
        Loop
        rsStatus.Close
'    End With
End With

Set fc = Nothing

 

[arnelgp]

you can use the Paint event of detail section?

 

[smig]

you can use the Paint event of detail section?

Very interesting

What do we need the Conditional Format if we can use the Paint event ?

 

[isladogs]

Did you try Royce's solution in the first Post of the link given by @theDBguy in post #10?
I know that works and allows you to bypass the 3 CF limit that still exists using code.
Otherwise, use the CF wizard which allows up to 50 conditions.

 

[smig]

Did you try Royce's solution in the first Post of the link given by @theDBguy in post #10?
I know that works and allows you to bypass the 3 CF limit that still exists using code.
Otherwise, use the CF wizard which allows up to 50 conditions.

As I said in #11, I managed to overcome the 3 cf limit.
Using LIKE instead of "=" solved the problem
It seems it has nothing to do with the number of cf

 

[isladogs]

As I said in #11, I managed to overcome the 3 cf limit.
Using LIKE instead of "=" solved the problem
It seems it has nothing to do with the number of cf

Yes I was aware you had found a workround.
My question was whether you had tried Royce's solution

 

[smig]

Yes I was aware you had found a workround.
My question was whether you had tried Royce's solution

No I did not see it before, so i didnt try before.

Now i tried it and found it to be the most mystery bug I ever saw
If i start on 3, as suggested it wont even work with LIKE.
It will error on i = 3

But why do we need the cf at all, if we can simply use the Paint event ?

 

[isladogs]

Try reverting to = and then start at i=3
Or just use the wizard which allows up to 50 CF conditions

I doubt you can do everything that is possible with CF using the Paint event. Or at least, not do so as easily as with CF

 

[smig]

Try reverting to = and then start at i=3
Or just use the wizard which allows up to 50 CF conditions

I doubt you can do everything that is possible with CF using the Paint event. Or at least, not do so as easily as with CF

I tried reversing to =
Didnt work. Wont even work for LIKE.
Actually it will error for the first fc (i=3).
This realy put me into the conclusion that this error has nothing to do with the number of the fc perse

In this situation using the wizard cant be done, as i want to let the end user set the bg color.

In any case i have something that work now, and i can also easily change to paint event, as all i need is painting the bg

 

[Adelina_RO]

Two year later with a solution for a 10 years old problem:
don't add space between the expression elements.

For I = 1 To 10
    With Telefon.FormatConditions.Add(acExpression, acBetween, "1=1")
        .FontBold = True
    End With
Next

--> works perfectly;

For I = 1 To 10
    With Telefonc.FormatConditions.Add(acExpression, acBetween, "1 = 1")
        .FontBold = True
    End With
Next

--> throws the infamous error.
I mean, the solution with the three preceding conditions works, but if you need to constantly update FC on multiple fields, that becomes an issue, especially with more than 3 different controls on a form 1920x1080.