Search as you type in continues forms

[sseiharath]

Hi All,

Who could help me to make a text box live search (mean: search as you type) in header of continue form and a button to clear the text box will show all record after click.
I would like to search fields such as: [ServiceName],[UnitName],[StaffName],...
the database in attach file.
Thanks.
Hopeful.

 

[isladogs]

Add an AfterUpdate event in the search text control which filters the records for your search criteria
Add a Click event to the Ckear button to remove the filter again

There are lots of examples available online including in this forum if you do a search

 

[CJ_London]

use the search textbox change event with code something like


me.filter="[ServiceName] like '" & searchtextboxname.text & "*'"
me.filteron=true


I recommend not including an initial * - most people know the start of what they are looking for and it will be quicker if the field is indexed. Train users to enter an initial * if required.

note the text property is only available whilst the control has the focus.

This code will convert numbers to text but booleans and dates need to be treated differently - they need to be formatted. e.g. iif(myyesnofield,'Yes','No') and format(mydatefield,'dd/mm/yyyy')

to search multiple fields, separate with an OR

"[ServiceName] like '" & searchtextboxname.text & "*' OR [UnitName] like '" & searchtextboxname.text & "*'"...etc

 

[ClearlyDarkly]

AfterUpdate would be once the textbox loses focus.

I don't know how, but it would be an "On Key Press"

Private Sub txtSearch_KeyPress(KeyAscii As Integer)
Dim txtSearch As String

With Me

    Me.Refresh
    txtSearch = Me.txtSearch
    
    Me.txtSearch = txtSearch
    Me.txtSearch.SelStart = Len(txtSearch) + 1

End With

End Sub

This allows you to keep typing after each keypress, you can then use TxtSearch as the Query Criteria to change the record source of the SubForm table.

 

[isladogs]

Hi

You are of course right about the AfterUpdate event though that easily solved by setting the focus to another control e.g. Clear button
In the past I have also used a slightly different approach with no code in the txtSearch box and a cmdSearch button with the code

I personally wouldn't use a KeyDown or KeyPress event as the results will continually change as you type which could be confusing & also slow things down if you have a lot of records to search

See attached screengrabs

 

[sseiharath]

Hi CJ London,

A great help, thanks a lot, I put your code on change event when I type 2nd letters... why the first letter disappeared? when I continues typing, display only last letter in text box, how should I do, if I want to display all letters or hold words as I type in text box?.
Thanks.

 

[sseiharath]

Hi ClearlyDarkly,

Your code error when I put in key press event.
Please look in attach file.
Thanks.

 

[CJ_London]

I put your code on change event when I type 2nd letters... why the first letter disappeared?

same reason as for clearlydarkly's keypress suggestion. You would use similar code for the solution, but referencing the .text property. I've got an example somewhere,, but really busy right now.

Take a look at the links provided right at the bottom of this thread

 

[CJ_London]

found the code, but the search control is on a mainform and the filter is applied to a subform control called SFData. FldStr is set elsewhere and basically adds square brackets to a field name.

Private Sub QuickFind_Change()
    
    SFData.Form.Filter = FldStr & " like '" & Replace(QuickFind.Text, "'", "''") & "*'"
     SFData.Form.FilterOn = True

End Sub

 

[missinglinq]

The Inimitable Master of Access, Allen Browne, has a hack for doing this very thing:

Allen Browne's Find As You Type Utility

Linq ;0)>

 

[sseiharath]

Thanks a lot missingling very useful link page for coding.

 

[missinglinq]

You're quite welcome! I always check out Allen's site first, when researching a new problem for someone, as he's addressed so many issues complex issues, over the years! You can even find some of his articles under Microsoft's Access Help.

Linq ;0)>