Solved: KeyPress, Keyup, Keydown or somthing else ?

Hey,
i have a search form that uses a very simple query with a WHERE clause to filter values in a list box for the purpose of searching through records.

I have a simple filter system setup so you can enter a search value

Put into the " WHERE x LIKE 'Value here'"

I am looking for a way to make the system Refresh the list everytime a new character is pressed in the text box.

EG im searching for last name doe

i hit D and it eliminates all people who do not have a D as the first character

Then i hit o and it eliminated all people who do not have an o

as i have it you have to type the whole string first (after update).

But i have been having some issues using the keypress events as they don't seem to work quite how i would have imagined them to work.

any suggestions on how i can go about implementing this ?

at the moment i have this Sub to do the updating

[vba]Private Sub UpdateCriteria()
Dim SqlText As String, temp As String
If IsNull(Me.FilterText) Or Me.FilterText = "" Then
temp = "*"
Else
temp = Me.FilterText
End If
SqlText = "SELECT [MedicalID#], LastName, Firstname, Birthdate FROM tblPatient"
Select Case Me.SearchOption
Case 1
SqlText = SqlText & " WHERE [MedicalID#] LIKE '*" & temp & "*'"
Case 2
SqlText = SqlText & " WHERE Firstname LIKE '*" & temp & "*'"
Case 3
SqlText = SqlText & " WHERE Lastname LIKE '*" & temp & "*'"
End Select
Me.PatList.RowSource = SqlText
Me.PatList.Requery
End Sub[/vba]

The OnChange event might do what you want... but depending on the size of the recordset you are querying, this could cause unexpected slowdowns because you're requerying the recordset with every keypress. Also, depending on how fast your users type, they might be entering data faster than the query can complete.

Hi Movian,

I would query when the length of the text entered is = 3 the first time (Similar to what CreganTur is saying) this would eliminate the fast typist problem cause you can "lock" the input until the query returns. Pop up a form that says "Hey I'm Busy here." maybe.

With just 3 characters it would eliminate a lot of records. If more is needed every 2 after that. The only issue I see with this off the cuff concept is if the user backspaces due to fat fingers on the keyboard (I have this problem).:D

As a last resort I think I would use a button to run the query. Let the user type in what they think would narrow down the search and pick the button to run the query. This is a bad idea but as I said a last resort. :)

Can you query/filter the recordset? (I don't have access to play with here)

well for now im jsut going to stick with an after update.

Meaning they just have to hit enter or tab when they are happy with their search criterion...