CODEWITHSUNDEEP
ms-access
Ridhwan Z.
Ridhwan Z.

Reputation: 27

Conditional formatting based on text value input

I'm doing a form and having difficulty in highlighting field based on textbox input. I cannot highlight the field that contains many information in it.

This is my input field, I search for "Family"

This is my field where I have multiple values in it, separated by comma

I cannot highlight the entire field eventhough the word "family" is in that field

Anyone got idea how to solve this? My form after using vba I searched for "words", this is the first record, it works fine

Next record, there is no "words" in the field, but it still follow the previous rule from picture above

Upvotes: 2

Views: 453

Answers (1)

Santosh
Santosh

Reputation: 12353

  1. On click of the Search button, the code checks if the textbox has the keywords.
  2. It then loops through all textboxes to find textboxes with matching keywords and highlights them
Private Sub cmdSearch_Click()


    Dim ctrl As Control
    If Nz(Me.txtSearch, "") <> "" Then

        For Each ctrl In Me.Controls
            If TypeName(ctrl) = "TextBox" And ctrl.Name <> "txtSearch" Then

                If InStr(1, ctrl, Me.txtSearch, vbTextCompare) > 0 Then
                   ctrl.BackColor = vbRed
                Else
                      ctrl.BackColor = vbWhite
                End If
            End If
        Next

    End If

End Sub

To reset textboxes on moving to next record 

    Private Sub Form_Current()
    Dim ctrl As Control
    'Me.txtSearch = ""

    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "TextBox" Then
                  ctrl.BackColor = vbWhite
        End If
    Next
    cmdSearch_Click
End Sub

output

enter image description here

Upvotes: 1

Related Questions