Solved Programatically change color on continous form conditional formatting

[Pat Hartman]

My ORIGINAL post, all the way back at #3 suggested a function might work. The OP wasn't buying and everyone else is suggesting that VBA will work.

 

[REZ]

MajP, that worked perfectly. I modified it slightly to loop through several controls, as there can be several categories per student.
Thanks for the help.

 

[REZ]

I'm getting too many formatconditions because I'm trying to do so many for about 5 controls. How do I get to set to Multiple without putting it in for each control?

 

[MajP]

Can you do something like this where you paste a single textbox behind the data textboxes and make their background transparent.

 

[REZ]

Can you do something like this where you paste a single textbox behind the data textboxes and make their background transparent.
View attachment 100692

 

[REZ]

No, each text box has different RGB values, so one name can have 5 boxes next to it in different colors

 

[REZ]

When setting format conditions manually you can highlight several controls and the condition will then say "multiple". is there a way of doing that with the code you wrote?

 

[MajP]

When setting format conditions manually you can highlight several controls and the condition will then say "multiple". is there a way of doing that with the code you wrote

I would have thought the same thing. But does not appear to be the methods or way to do this.

In my code when I create "Con" it creates a format condition object and it is added to the format conditions collection of the control.
You would think you could assign that condition to another control.

Set con = Me.CatID.FormatConditions.Add(acFieldValue, acEqual, "'" & category & "'")
With con
.BackColor = RGB(r, g, b)
End With

You would think there would be another constructor that would allow you to do something like
SomeOtherControl.formatConditions.Add (Con)
But that does not exist. It appears to me when you do this manually that the controls are not sharing a format condition, but a copy is made for each control.

 

[REZ]

So if there are about 40 conditions that I'm trying to set to 5 controls, I'm going to get an error when the form loads, as its more than the 50 max?
Or is it 50 for each control?

 

[MajP]

That form I posted is actually not using CF, but instead it is using the Paint event of the form. Depending on how you use the form it may work. If it is just viewing withought updates you may be fine. It gets squirrelly when the form has to refresh a lot.

Sometimes this works fine as in the simple case above, other times it just flickers.
Here is the code without CF for that form.

Private Sub Detail_Paint()
  Me.Text50.BackColor = RGB(Rnd() * 255, Rnd * 255, Rnd * 255)
End Sub

As for the number, there is very unclear documents. I have seen things posted saying you should only be able to do 3 in code, but that is proved wrong.

 

[MajP]

This seems to work pretty well using the Paint event.

Private Sub Detail_Paint()
  Me.FldOne.BackColor = GetColor(Me.FldOne)
  Me.FldTwo.BackColor = GetColor(Me.FldTwo)
  Me.FldThree.BackColor = GetColor(Me.FldThree)
 
End Sub

Public Function GetColor(Category As Variant) As Long
  Dim r As Integer
  Dim g As Integer
  Dim b As Integer
  
  If Not IsNull(Category) Then
    r = DLookup("Red", "tblCategories", "CatID = '" & Category & "'")
    g = DLookup("Green", "tblCategories", "CatID = '" & Category & "'")
    b = DLookup("Blue", "tblCategories", "CatID = '" & Category & "'")
    GetColor = RGB(r, g, b)
  Else
    GetColor = RGB(255, 255, 255)
  End If
End Function


Private Sub FldOne_GotFocus()
  FldOne.BackColor = GetColor(Me.FldOne)
End Sub

Private Sub FldTwo_GotFocus()
  FldTwo.BackColor = GetColor(Me.FldTwo)
End Sub

For the demo I purposely did not add the got focus event to FldThree. This shows that for some reasons you will get what appears as random color changes when you enter a control. Once you add the got focus event no problem.
You will have to play with your form to see if it works.

 

[REZ]

Thanks DBguy. It works fine without the gotfocus event, and it loads faster than the format conditions. For some reason the form flickers on and off if clicked on, but it's supposed to be for viewing only, so should be fine.
Thanks again!

 

[Pat Hartman]

It flickers because it has to run the paint event again. Initially when the form loads, you don't see the flicker because Access makes the whole thing visible at the end.

 

[REZ]

It shouldn't be a problem because there aren't more than 4 or 5 records on the form, so don't need to scroll down.
Thanks everyone for the help