Solved Programatically change color on continous form conditional formatting

[REZ]

Hi, I'm trying to change the color of a textbox on a continous form. I need to color to change based on the RGB values in the text box.
Is it possbile to do?

 

[theDBguy]

Hi, I'm trying to change the color of a textbox on a continous form. I need to color to change based on the RGB values in the text box.
Is it possbile to do?

Maybe by code? Otherwise, what values would be in the Textbox? Can you put them in a lookup table?

 

[Pat Hartman]

You can't do this with code on a continuous form. Given the way Conditional formatting works (you pick a color from a pick list rather than type it in), that won't work but you might be able to have the Conditional formatting call a function and pass in the RGB value. But I'm not sure that would work either.

 

[Eugene-LS]

I need to color to change based on the RGB values in the text box.

https://docs.microsoft.com/ru-ru/office/vba/api/access.formatconditions.add
https://www.access-programmers.co.u...d-based-on-the-value-of-another-field.301500/

Spoiler: Example VBA code

Public Sub SetFormatConditions()

Dim iVal%, sVal$, iCondition%
Dim objFrm As Form
Dim objCtrl As Control
Dim objFC As FormatCondition
'----------------------------------------------------------------------------------------------
On Error GoTo SetFormatConditions_Err
    
    Set objFrm = Forms("График_ТО").Form
    
    For iVal = 2 To 12
        sVal = "txtM" & Format(iVal, "00")
        Set objCtrl = objFrm.Controls(sVal)
    
        With objCtrl
            While .FormatConditions.Count > 0
                .FormatConditions.Delete
            Wend
            For iCondition = 1 To 2
                'Expression.Add (Type, Operator, Expression1, Expression2)
                Select Case iCondition
                    Case 1
                        sVal = "[txtRol" & Format(iVal, "00") & "]=-1"
                        Set objFC = .FormatConditions.Add(acExpression, acBetween, sVal)
                        objFC.ForeColor = 255
                        objFC.BackColor = 13952764 '= light red (rose)
                        objFC.Enabled = False
                    Case 2
                        sVal = "[txtRol" & Format(iVal, "00") & "]=1"
                        Set objFC = .FormatConditions.Add(acExpression, acBetween, sVal)
                        objFC.ForeColor = 0
                        objFC.BackColor = 14151142  '= light green
                        objFC.Enabled = False
                End Select
            Next iCondition
        End With
    Next iVal

SetFormatConditions_End:
    On Error Resume Next
    Err.Clear
    Exit Sub

SetFormatConditions_Err:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in Sub" & _
           "SetFormatConditions - modTools.", vbCritical, "Error!"
    'Debug.Print "SetFormatConditions_Line: " & Erl & "."
    Err.Clear
    Resume SetFormatConditions_End
End Sub

 

[REZ]

I've been trying to set the conditinal formatting by code.
I've got this so far:
Dim FC As FormatCondition
Me![T1].FormatConditions.Delete

Set FC = Me![T1].FormatConditions.Add(acExpression, acEqual _
, "not isnull([T1])")
FC.BackColor = RGB(Left(T1, 3), 0, 0)

but its changing the color of all of the records based on the value of the first one.
I thought formatconditions would evaluate each record separately.

 

[strive4peace]

@REZ , forms apply the same formatting to all rows whereas reports can be different for each row, so perhaps you want to use a subreport for color? That won't be able to be edited, but it could be different with coding.

 

[REZ]

Thanks strive4peace.
It doesn't need to be edited.
I've been trying with reports and subreports, but I'm getting the same results
Can you help me with the coding?

 

[strive4peace]

hi @REZ

you're welcome

If it doesn't need to be edited, why use a form to begin with? You can use the Format event of a report section to change colors programmatically ... so maybe you can figure it out yourself

if you still need help, post a stripped down copy of your database with only the tables you need with sample data, and your form and subreport, or report. That can then be modified and posted back.

 

[REZ]

I created the report and put the code in the Detail Format event. It only works in print preview.
How can i get it to work in layout view?

Code I used:
Me.T1.BackColor = RGB(Left([T1], InStr(1, [T1], ",") - 1), Mid([T1], InStr(1, [T1], ",") + 1, InStrRev([T1], ",") _
- (InStr(1, [T1], ",") + 1)), Right([T1], Len([T1]) - InStrRev([T1], ",")))

 

[Pat Hartman]

Only certain code runs in layout view.

The reason you can't use Conditional formatting is because you want to set the color with code not by using the color picker. If you can use the color picker, you can use Conditional formatting.

 

[GPGeorge]

May I humbly suggest that this is beginning to look a bit like a PowerBI report, where you have a lot of control over conditional formatting.

 

[REZ]

I'll try explain what I'm trying to achieve.

I have a student database. There are different categories that can be assigned to the student. Each category has a specific color that makes it easily recognisable to the staff. The student can also be linked to another student.
On the student overview form I need a side panel that displays linked students and colored dots/boxes to represent the category/ies
Since the colors on the category are changable by the user, I can't use the color picker to set up conditional formatting.
Also, there are about 40 different categories, I don't know how many rules you can do with conditional formatting.

Any ideas how I could do this?

 

[theDBguy]

I'll try explain what I'm trying to achieve.

I have a student database. There are different categories that can be assigned to the student. Each category has a specific color that makes it easily recognisable to the staff. The student can also be linked to another student.
On the student overview form I need a side panel that displays linked students and colored dots/boxes to represent the category/ies
Since the colors on the category are changable by the user, I can't use the color picker to set up conditional formatting.
Also, there are about 40 different categories, I don't know how many rules you can do with conditional formatting.

Any ideas how I could do this?

You can have up to 50 format conditions. However, as I said earlier, I think you can place the category colors in a lookup table and skip conditional formatting altogether.

 

[REZ]

DBguy, I've got the RGB values in a table, I've also included them as part of the underlying query.
How do I do it without conditional formatting? As I said earlier, the report is only showing the formatting in print preview.

 

[Pat Hartman]

You need to use conditional formatting. Use the type of student to assign the color. You CANNOT use RGB colors from a table. That isn't how conditional formatting works and VBA will not work in a continuous form.

 

[Sodslaw]

As its a dropdown wont something like this work ...

if Me.TXTBOX.value = "RED" then Me.TXTBOX.BackColor = RGB(237, 28, 36)

 

[theDBguy]

DBguy, I've got the RGB values in a table, I've also included them as part of the underlying query.
How do I do it without conditional formatting? As I said earlier, the report is only showing the formatting in print preview.

Hi. Can you post a sample db with test data?

 

[Pat Hartman]

I give up

 

[MajP]

You need to use conditional formatting. Use the type of student to assign the color. You CANNOT use RGB colors from a table. That isn't how conditional formatting works and VBA will not work in a continuous form.

Not true.


It is correct that you need use conditional formatting, but it is incorrect
that it cannot be done dynamically. You need to create this dynamically by
reading your table and creating the formatconditions. In my example I have
a CategoryID field, a Red, Green, Blue field with values in a table called
tblCategories. I read the table and create the formatconditions.

Private Sub Form_Load()
RemoveFormatConditions
AddFormatConditions
End Sub

Public Sub AddFormatConditions()
Dim con As FormatCondition
Dim rs As DAO.Recordset
Dim r As Integer
Dim g As Integer
Dim b As Integer
Dim category As String
Set rs = CurrentDb.OpenRecordset("tblCategories")
Do While Not rs.EOF
category = rs!CatID
r = rs!Red
g = rs!Green
b = rs!Blue
Set con = Me.CatID.FormatConditions.Add(acFieldValue, acEqual, "'" &
category & "'")
With con
.BackColor = RGB(r, g, b)
End With
rs.MoveNext
Loop
Me.Refresh
End Sub

Public Sub RemoveFormatConditions()
Dim con As FormatCondition
For Each con In Me.CatID.FormatConditions
con.Delete
Next con
End Sub

 

[MajP]

Fyi with this method you can have up to 50 colors/categories