Conditional Formatting in Access with Disabled or 'Read-only' Textboxes

Question

I have an Access Continuous Form with some bound textboxes. I want to provide a visual indicator of the status of each record, so the textbox contains a value, either 0, 1 or 2 from the table. The conditional format turns this box either Green for 0, Yellow for 1, and Red for 2 including the text colour so the box is literally just a colour for the user.

I have disabled the textbox to prevent the user being able to click into the box and seeing that it is a textbox, but setting Enabled = False to the properties seems to turn off with conditional formatting. I have then applied the Enabled box in the Conditional Formatting but whilst that does disable the textbox, it turns off the conditional formatting, and if you lock it, it won't change the text colour.

Is there another control I'd be better off using here, or is there away to allow the textbox conditional format to work without the user being able to click into the textbox?

Thanks @FoxfireAndBurnsAndBurns - I did mention that unfortunately, doing this does keep the BackColor property intact, but unfortunately changes the ForeColor to a default Grey/Black. — RazorKillBen

Andre Andre · Accepted Answer · 2019-12-02T16:48:32

To disable clicking into the textbox, it must have

Enabled = False
Locked = True

To not overwrite this with Conditional Formatting, the format condition must include Enabled = False as well.

Note that in the Conditional Formatting dialog, it will look as if the color doesn't work - the dialog doesn't know about Locked = True. But it will work in the form.

I remember having problems setting Enabled = False in the dialog (this was in Access 2010), so I used code to create it:

' myForm must be open in Design view
Public Sub MyCreateCondFormat()

    Dim F As Form_myForm
    Dim fld As TextBox
    Dim objFrc As FormatCondition

    Set F = Form_myForm

    Set fld = F.myTextbox
    fld.FormatConditions.Delete

    Set objFrc = fld.FormatConditions.Add(acFieldValue, acEqual, "42")
    With objFrc
        .Enabled = False    ' <----------
        .ForeColor = &H0000FF
    End With

End Sub

Conditional Formatting in Access with Disabled or 'Read-only' Textboxes

2 Answers