Condition Formatting at After Update event (TextBox)

[excellenthelp]

Hello.

I am attempting to write a simple code but could not see why the code will not perform. It just simply does nothing. No error is stated.

Private Sub Text165_AfterUpdate()

    Select Case Me.AccredStatus
        Case Is = "Surrendered"
        Me.AccredStatus.BackColor = vbYellow
        Case Is = "Recognized"
        Me.AccredStatus.BackColor = vbYellow
        Case Is = "Suspended"
        Me.AccredStatus.BackColor = vbRed
        Case Is = "Revoked"
        Me.AccredStatus.BackColor = vbRed
        Case Else
        Me.AccredStatus.BackColor = vbBlack
    End Select
    
End Sub

Help will be much appreciated!

[June7]

Should use Conditional Formatting from the ribbon, not VBA code.

[orange]

I would try a Me.Requery or Me.Reformat or Me.Refresh to repopulate the screen/form with your updates.
I forget which of these commands should used.

Give it a try.
Add the line to be tested after your End Select.


Just googled and found this. Refresh is the one.
Good luck.

[excellenthelp]

I must apologize for not including a brief explanation of what I would like to come out of this.

What I would like to see is when the value in the textbox changes to 'Surrendered', the backcolor of the textbox will turn red at the moment when I change records in a form. When I come across a record with 'Accredited', the backcolor will change to black. I want the backcolor change to be done automatically as I cruise through the records in a form.

How can we make this a possibility?

Thanks!

[excellenthelp]

June7, Thank you for the response.

I had tried to use the conditional formatting from the ribbon, however, I would then have to keep clicking on the textbox to enforce the conditional formatting application to perform. I want it to do that without having me clicking there all the time...

Orange, it does not work. Does nothing still. hah

[June7]

You want backcolor of ALL records to be the same depending on the value of the current record?

You want this to occur as you navigate records as well as when the textbox is edited?

That involves two events - form Current event and textbox AfterUpdate event.

It is not necessary to click anything for Conditional Formatting to have effect. It is automatic. I have used it.

[Missinglinq]

...when the value in the textbox changes to 'Surrendered', the backcolor of the textbox will turn red at the moment when I change records in a form. When I come across a record with 'Accredited', the backcolor will change to black. I want the backcolor change to be done automatically as I cruise through the records in a form....

Assuming that this is a Single View Form (as it sounds) you can do this with your code current in the Form_Current event. If this is not a Single View Form, you must use Conditional Formatting, as June7 suggested.

I'm confused, however, because your code currently is in the AfterUpdate event of a Control named Text165 but it refers to a Control named AccredStatus; I would have expected it to be in the AccredStatus_AfterUpdate event.

Linq ;0)>

[orange]

Good point Linq!

[excellenthelp]

Everyone, thank you for the responses! June7 is right- Conditional Formatting is the best way to go (Just as I expected). I had discovered why it was not working in the first place- the Back Style property of the text box was set to 'TRANSPARENT', thus preventing the color from appearing and actually show the changes in color based on the criteria established by the conditional formatting rules.

So, it is best to leave the Back Style to Normal and just assign the Back Color property the same value as the Back Color of the Form or whatever the text box is placed upon.

Marking this as SOLVED.

Thanks again!!!