Special search function

[ChewDoggie]

Hello All!

I did a keyword search of this forum and didn't find anything (not sure about the keywords I used).

I have a search textbox on a form (txtLname), and I'd like to resolve the search with each user keypress.

So, if the "user" presses "z", all of the names beginning with "z" appear. If the user then presses "i", then all last names beginning with "zi" appear, etc.

Currently, I have a "Find" button and I do a general search in the cmdFind_Click subroutine, but that isn't done til the user either clicks the find button.

I tried to add a call to the cmdFind_Click subroutine in the txtLname_Keypress event, but I keep getting strange results, i.e., when the user presses "a", the list highlights the names beginning with "K". That kind of thing.

Anyone have ideas how to make this work?

Thanks.

AMACycle

 

[Fion]

I know how to make this work for a combobox (search for autocomplete combobox on here and you will find the detailed description)
I'm not sure how this would be done in a textbox, but I have one or two ideas.
First, what about when your user types a letter, in that _keypress function, you just call your wexisting find function? that would eliminate the having to press the find button.
Also, when you use _keypress it passes the key in ascii char, I believe. You then have to convert it to a normal character, and I believe there is an offset in the converstion.. try returing your variable in a msgbox to see what is actually being used in the search...

Good luck

Fion

 

[johnwm]

Use a SQL stored procedure or a constructed query, using the text typed in as the key. Do the query in the KeyUp event.

strSQL = "Select * From tblNames Where fldName Like '" & txtName.Text & "%'"

Warning - do a search on SQL injection attacks OR make sure you trap punctuation marks!

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

Essex Steam UK for steam enthusiasts

 

[ChewDoggie]

Thank you ever1, particularly johnwm. I noticed that when I built the sql statement in the cmdFind_Click routine, the value in txtLname.text wasn't there yet, so I did as you suggested, called cmdFind_Click in the keyup event and that did the trick. Now to write the code for non-alphabetic chars as you warned, which shouldn't be too difficult.

Thanks again!

AMACycle

 

[mattKnight]

Personally, I'd do this with a variation...

I'd use a timer (set to about 500 - 750 msec), that fires the SQL search routine. I'd then re-enable the timer from the textbox change event. (so each time the txtbox changes there is a new delay between starting to search)

This measn that you aren't needlessy searching you database, until the user has finished their data entry.

some code snippets to help you (you'll need to tweak it to suit your app, mblnSearchOn is a module level switch to enable searching, whic you can safely remove

Private Sub txtName_Change()
If (Len(txtName.Text)) > 0 And (mblnSearchOn = True) Then
    tmrSearch.Interval = 750
    tmrSearch.Enabled = True
 '   txtId.Text = "" not needed this app...
Else
    tmrSearch.Enabled = False
End If
    
End Sub

Private Sub tmrSearch_Timer()
tmrSearch.Enabled = False 'One shot system
' so when timer fires, we need to re-enable it elsewhere
'SearchDB routine goes here
End Sub

Take Care

Matt
If at first you don't succeed, skydiving is not for you.

 

[ChewDoggie]

Thank you. Another avenue to consider.

AMA