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

REZ

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

I’m here to help
Staff member
Local time
Today, 13:58
Joined
Oct 29, 2018
Messages
21,358
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

Super Moderator
Staff member
Local time
Today, 16:58
Joined
Feb 19, 2002
Messages
42,976
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

Registered User.
Local time
Today, 23:58
Joined
Dec 7, 2018
Messages
481
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/

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

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

AWF VIP
Local time
Today, 15:58
Joined
Apr 3, 2020
Messages
1,003
@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

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

AWF VIP
Local time
Today, 15:58
Joined
Apr 3, 2020
Messages
1,003
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

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

Super Moderator
Staff member
Local time
Today, 16:58
Joined
Feb 19, 2002
Messages
42,976
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.
PB_ProjectStatus.JPG
 

GPGeorge

Grover Park George
Local time
Today, 13:58
Joined
Nov 25, 2004
Messages
1,776
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.
1652909062196.png
 

REZ

Member
Local time
Today, 20:58
Joined
May 17, 2022
Messages
34
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’m here to help
Staff member
Local time
Today, 13:58
Joined
Oct 29, 2018
Messages
21,358
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

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

Super Moderator
Staff member
Local time
Today, 16:58
Joined
Feb 19, 2002
Messages
42,976
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

Registered User.
Local time
Today, 13:58
Joined
Jun 7, 2017
Messages
81
As its a dropdown wont something like this work ...
Code:
if Me.TXTBOX.value = "RED" then Me.TXTBOX.BackColor = RGB(237, 28, 36)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:58
Joined
Oct 29, 2018
Messages
21,358
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?
 

MajP

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

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

You've got your good things, and you've got mine.
Local time
Today, 16:58
Joined
May 21, 2018
Messages
8,463
Fyi with this method you can have up to 50 colors/categories
 

Users who are viewing this thread

Top Bottom