Searching specific fields vba

[Mike Krailo]

Yes Kitty, that's easy to do. I updated the sample to do just that. And arnelgp, that is a killer filter demo, I will use that one for sure.

 

[isladogs]

And another multiple filter example...
For more info on this and another example, see https://www.access-programmers.co.u...ering-3-multiple-combos-option-groups.302189/

 

[kitty77]

Perfect!!

 

[kitty77]

Yes Kitty, that's easy to do. I updated the sample to do just that. And arnelgp, that is a killer filter demo, I will use that one for sure.

Is there any way to make the search highlight the field or something? So, if I do a search that has several fields, it brings back those records but you don't see where that value is in that field.

Thanks...

 

[arnelgp]

using Conditional Format and using mr.Krailo's sample.

 

[kitty77]

Perfect. So, where does the yellow highlighting happen? In the search button?

 

[isladogs]

In case you are interested, Allen Browne has a different approach to this using rich text. See http://allenbrowne.com/AppSearchHighlight.html

 

[kitty77]

Here is my code... So, how can I make this highlight either the whole field or the search value?


Please use Code Tags

Private Sub Command48_Click()
    
      If Nz(Me.SearchBox, vbNullString) = vbNullString Then
      MsgBox "No search term entered!"
   ElseIf DCount("Mrecordid", "Search") = 0 Then
      ' Check if number of records is zero and display message instead of filtering to no records
      MsgBox " Nothing found using that search term! "
      Me.SearchBox = vbNullString
      Me.SearchBox.SetFocus
      Exit Sub
   Else
      Me.RecordSource = "Search"
      Me.Requery
   End If
    
End Sub

 

[arnelgp]

this is your code:

Private Sub Command48_Click()
    'remove format condition
    Call FormFormatCondition
    If Nz(Me.SearchBox, vbNullString) = vbNullString Then
        MsgBox "No search term entered!"
    ElseIf DCount("Mrecordid", "Search") = 0 Then
        ' Check if number of records is zero and display message instead of filtering to no records
        MsgBox " Nothing found using that search term! "
        Me.SearchBox = vbNullString
        Me.SearchBox.SetFocus
        Exit Sub
    Else
        Me.RecordSource = "Search"
        'add format condition
        Call FormFormatCondition(Me.SearchBox)
    End If

End Sub


'arnelgp
Private Function FormFormatCondition(Optional ByVal strText As String = "")
    Dim c As Access.Control
    Dim cf As Access.FormatCondition
    If Len(strText) = 0 Then
        'remove conditional format
        On Error Resume Next
        For Each c In Me.Controls
            If "TextboxComboboxListbox" Like "*" & TypeName(c) & "*" Then
                c.FormatConditions.Delete
            End If
        Next
    Else
    
        'add conditional format
        For Each c In Me.Controls
            If "TextboxComboboxListbox" Like "*" & TypeName(c) & "*" Then
                Set cf = c.FormatConditions.Add(1, 2, "[" & c.ControlSource & "] Like '*" & strText & "*'")
                cf.BackColor = vbYellow
            End If
        Next
    End If
    'end of arnelgp


End Function

 

[kitty77]

Ok, I copied and pasted the code into my form. The search still works but nothing turns yellow like your sample?
Do I have to change the name of some fields?

 

[arnelgp]

upload a sample db to make it easier.

 

[kitty77]

Ok, I got it to work. However, when I hit the clear button it keeps the fields yellow from the previous search?
How can I make it clear that previous yellow or am I missing something?

 

[kitty77]

Got it! Was missing a the CALL in the clear button. Works perfect!!!