Solved Programatically change color on continous form conditional formatting (1 Viewer)

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:13
Joined
Feb 19, 2002
Messages
42,970
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

Member
Local time
Today, 14:13
Joined
May 17, 2022
Messages
34
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

Member
Local time
Today, 14:13
Joined
May 17, 2022
Messages
34
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

You've got your good things, and you've got mine.
Local time
Today, 10:13
Joined
May 21, 2018
Messages
8,463
Can you do something like this where you paste a single textbox behind the data textboxes and make their background transparent.
Format.jpg
 

REZ

Member
Local time
Today, 14:13
Joined
May 17, 2022
Messages
34
No, each text box has different RGB values, so one name can have 5 boxes next to it in different colors
 

REZ

Member
Local time
Today, 14:13
Joined
May 17, 2022
Messages
34
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

You've got your good things, and you've got mine.
Local time
Today, 10:13
Joined
May 21, 2018
Messages
8,463
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.

Code:
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

Member
Local time
Today, 14:13
Joined
May 17, 2022
Messages
34
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

You've got your good things, and you've got mine.
Local time
Today, 10:13
Joined
May 21, 2018
Messages
8,463
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.
Code:
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

You've got your good things, and you've got mine.
Local time
Today, 10:13
Joined
May 21, 2018
Messages
8,463
This seems to work pretty well using the Paint event.
Code:
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.
Paint.jpg
 

Attachments

  • ChangeFormat.accdb
    2.4 MB · Views: 209

REZ

Member
Local time
Today, 14:13
Joined
May 17, 2022
Messages
34
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

Super Moderator
Staff member
Local time
Today, 10:13
Joined
Feb 19, 2002
Messages
42,970
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

Member
Local time
Today, 14:13
Joined
May 17, 2022
Messages
34
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
 

Users who are viewing this thread

Top Bottom