Solved Conditional Formatting - Unbound Textbox and LIKE statement (1 Viewer)

[Sonnydl]

Hello. I'm wondering if I can use conditional formatting on a form when I'm looking for only partial text in a single unbound textbox.

I can do this with VBA but was wondering if it can be done via Conditional Formatting, in case I want to do it that way in the future or in other instances.

I've got an unbound textbox that shows a status and the date that status was set. The text is input into txtDocStatus via VBA code (look for the last status of a document and show it with the date. That information is not present anywhere on this particular form.

• If the status is Approved (e.g., Approved: 06-Dec-2023), the this is the base case and the textbox has the standard formatting.
• If the status is missing completely, the textbox value is No Status Found and I can use conditional formatting on the form to turn the box red. The Conditional Formatting Rules Manager rule is Value = "No Status Found" and this works as expected.
• If the status is Submitted, then the textbox value is that word and the date (e.g., Submitted: 05-Dec-2023). Obviously, I cannot hardcode a value into Conditional Formatting, because the date portion is variable.

For this latter case I've tried a few options without success:

• Value = Like "Submitted*"
• Expression is Instr(txtDocStatus,"Submitted")>0 <--gets changed to Expression is Instr("txtDocStatus","Submitted*")>0
• Expression is Instr(me.txtDocStatus, "Submitted") > 0
• Expression is Instr(Forms!frmDocuments!txtDocStatus, "Submitted") > 0

Has anyone had success with something like this?

Thank you in advance.

 

[GaP42]

On that works for me is to use the instr function to see if the string exists in the text, which returns a value eg
=IIf(InStr([AnswerValDescription],"Other")>0,"T","F")
Then apply conditional formatiting to that control to see if "T" is returned: using field value = "T" then format as needed.

 

[CJ_London]

What does 'without success' actually mean?

perhaps the issue is with this

The text is input into txtDocStatus via VBA code (look for the last status of a document and show it with the date. That information is not present anywhere on this particular form.

What vba code are you using and from what event ?- and what is the order of the conditional formatting rules?

 

[gemma-the-husky]

perhaps you can get a true/false field populated in the underlying query, and then you can use that field to determine the conditional formatting. You can use code to evaluate the field value, which is probably easier than trying to use options in the cf management.
It doesn;t have to be true/false. Your function could return anything you want.

 

[CJ_London]

this works for me

expression is....[txtDocStatus] Like "Submitted*"

 

[Sonnydl]

this works for me

expression is....[txtDocStatus] Like "Submitted*"

This didn't work for me. So you can refer to a control on a form in the Conditional Formatting interface by just bracketing the name of the control? I think I've seen what you suggested as a way to refer to data in a control (a bound control) but not for an unbound control.

 

[Josef P.]

Example:

Example accdb see attachment.

 

[Sonnydl]

What does 'without success' actually mean?

perhaps the issue is with this



What vba code are you using and from what event ?- and what is the order of the conditional formatting rules?

"Without success" means that the control isn't conditionally formatting when the unbound control contains the word "Submitted."

There are just two CONDITIONAL FORMATTING rules applied to this unbound control. The first is the "Value =" and the fixed text. It works as expected. The question I have is how to write an expression that refers to the unbound control using a "LIKE" or other formula? If the Field Value is modifier included the option "contains" there would be no problem. But the only modifiers are:

• between
• not between
• equal to <--works for the fixed value of "No Status Found"
• not equal to
• greater than
• less than
• greater than or equal to
• less than or equal to

Hence, I'm wondering if I can write an expression that looks at the value of the unbound control and determines if it meets (or doesn't meet, even) a certain condition (contains the word "Submitted") to format that control differently.

I have not employed the VBA code to format this control as yet, but it's easy enough to do and I'd do it as part of the OnCurrent event, when I grab create the data that goes into the unbound control. The reason for my question is to see if there's a way to do this without using VBA, so that I might utilize it on other controls in my database without having to write control specific code each time--as the base case for this control is shaded a certain way that other controls may not be.

Here's an example where one of my forms uses particular theme colors if the record is "active," but colors certain tagged controls as grey when the record is no longer "active"

Private Sub Status()

Dim ctl as Control

For Each ctl in Me
    If ctl.Tag = "Status" then
        If IsNull(me.txtDateRemoved) then
            ctl.BackThemeColorIndex = 1
            ctl.BorderThemeColorIndex = 4
            ctl.ForeThemeColorIndex = 0
        Else
            ctl.Backcolor = 1643706
            ctl.Bordercolor = 1643706
            ctl.ForeThemeColorIndex = 1
        End If
    End If
Next ctl

End Sub

 

[Sonnydl]

Thank you @CJ_London and @Josef P. ! Your solutions were exactly what I needed, but I got in my own way. I ignored the fact that the word "Submitted" could also be proceeded by the word "MASTER", so the expression I used in my particular case was:

Expression is [txtDocStatus] Like "*Submit*"

I also learned that when you click on the three-bullet button to the right of your expression field, it brings up the Expression Builder and selecting the control puts it in exactly as you have both indicated in brackets. My goal was to learn a different way, and I have. Thank you!!