Highlighting search field.

[PhilOfWalton]

I have a continuous form with 4 phone fields on it (Let's call them Phone1 ... Phone 4) (They are text fields)

I have a textbox (TxtPhoneFilter) in the form footer where I enter part of the phone number.

On the OnChange of TxtPhoneFilter, I build a filter something like

Sub TxtPhoneFilter_Change()
 
    Dim PhoneFltr As String
 
    ' If we return no records, tempotarily remove the filter
    If Me.RecordsetClone.RecordCount = 0 Then
        Me.FilterOn = False
    End If
 
    If Nz(TxtPhoneFilter) = "" Then
        Me.FilterOn = False
    End If
 
    TxtPhoneFilter.SetFocus
 
    If PhoneFltr > "" Then                               ' Media Type selected
        PhoneFltr = PhoneFltr & ") OR "
    End If
    PhoneFltr = PhoneFltr & "(sPhone1 Like '*" & Replace(Me.TxtPhoneFilter.Text, "'", "''") & "*'"
    If PhoneFltr > "" Then                               ' Media Type selected
        PhoneFltr = PhoneFltr & ") OR "
    End If
    PhoneFltr = PhoneFltr & "(Phone2 Like '*" & Replace(Me.TxtPhoneFilter.Text, "'", "''") & "*'"
    If PhoneFltr > "" Then                               ' Media Type selected
        PhoneFltr = PhoneFltr & ") OR "
    End If
    PhoneFltr = PhoneFltr & "(Phone3 Like '*" & Replace(Me.TxtPhoneFilter.Text, "'", "''") & "*'"
    If PhoneFltr > "" Then                               ' Media Type selected
        PhoneFltr = PhoneFltr & ") OR "
    End If
    PhoneFltr = PhoneFltr & "(Phone4 Like '*" & Replace(Me.TxtPhoneFilter.Text, "'", "''") & "*'"
    PhoneFltr = PhoneFltr & ")"
 
    Me.Form.Filter = PhoneFltr
    Me.FilterOn = True
 
    Me.TxtPhoneFilter.SetFocus
 
    ' Move the cursor to the end of the input text box.
    If Me.RecordsetClone.RecordCount = 0 Then
        Exit Sub
    End If
 
    Me.TxtPhoneFilter.SelStart = Len(Me.TxtPhoneFilter.Text)
 

This woks very sweetly and progressively filters the form, showing fewer and fewer records as more letters (digits) of the phone number are entered into TxtPhoneFilter.

So suppose we are down to 6 records = 24 phone numbers.

Now what I want to do is to highlight only those phone numbers that match TxtPhoneFilter.

Stymied. Any ideas please?

Phil

[PhilOfWalton]

Sorry, I withdraw the question. Solved it

Phil

[zmbd]

You could share how.... :)
Personally, I would look at the potential of conditional formatting or even add the formatting to your existing code...
There's a nice little blog here that walks thru creating and adding the formatting conditions by VBA
https://blogs.msdn.microsoft.com/fri...cess-with-vba/
:)

[PhilOfWalton]

What give away my trade secrets ... alright then.

I created 4 text boxes with the control source like this
IsPhone1 ControlSource

=IIf(Nz([TxtPhoneFilter])>"",IIf([Phone1] Like "*" & [TxtPhoneFilter] & "*",True,False),False)
 

Then conditional formatting like this
Expression is IsPhone1 True then the alternative format.

Works a treat.

Phil

[jforbes]

Sounds like you go it. I was working on something like this recently, so I mocked up a DB for ya and attached it anyway.

It uses RichText formatted TextBoxes which allows for some creative coloring.

[PhilOfWalton]

Yes that' very neat. The main difference obviously is that you are highlighting individual figures whilst I am highlighting the whole phone number.

Anyway your Db now sits in my sample Db folder were I have loads of bits and pieces for reference for a rainy day..
Thanks again

Phil