Text Box Search Having Issue Finding Name of Clinic

[CharissaBelle]

I have an unbound textbox (txtFilter) in the header of a form that is being used to show a specific record in the form. The user types in part or all of the name of the Clinic and it will go to that record.



Here is my issue:
One of the clinic names is First In Health. If the user types in 'Firs' and then hits enter, it finds First In Health perfectly. If the user types in 'First', it goes to the next record. If the user types in 'first i', it works. If the user types in 'first in', it doesn't. See what I mean? I can see what is happening, I just don't know how to fix it.

        Set rs = Me.Recordset.Clone        rs.FindFirst "([ClinicName] > """ & Me.txtFilter & "*"")"
        If Not rs.EOF Then Me.Bookmark = rs.Bookmark

[jwhite]

Try changing: rs.FindFirst "([ClinicName] > """ & Me.txtFilter & "*"")"
to: rs.FindFirst "([ClinicName] Like '*" & Me.txtFilter & "*'")"

[Bob Fitz]

Why not use a combo box instead of a text box.

[CharissaBelle]

Try changing: rs.FindFirst "([ClinicName] > """ & Me.txtFilter & "*"")"
to: rs.FindFirst "([ClinicName] Like '*" & Me.txtFilter & "*'")"

That works as long as the user types in part of a name that exists. If they mistype and add a letter or something, it goes to the first record. I would like it to go to the next record so the user can then use arrows to find what they may be looking for.

[CharissaBelle]

Why not use a combo box instead of a text box.

That would work, but I am only showing a small part of my code. I actually have a combobox that somewhat controls the textbox. In the first combobox, the user can choose what they want to search for in the form. Then when they type something into the textbox, it looks at what is selected in the combobox to determine how it should search.

[davegri]

How about:

        Set rs = Me.Recordset.Clone        
        rs.FindFirst "([ClinicName] >= """ & Me.txtFilter & "*"")"
        If Not rs.EOF Then Me.Bookmark = rs.Bookmark

[Micron]

You maybe could make use of the OnChange event and apply a filter to the form. As the user types, the filter is modified for each keystroke. If they type a invalid character combination, the records disappear. As long as the underlying record source is not huge or you're not going over a network as you filter, performance should be acceptable.
Maybe that's not conducive to what you're trying to do anyway. Just thought I'd throw it in here.

[CharissaBelle]

Thank you everyone. I'll keep plugging away and trying different things.