dynamic search

[Guest]

Hi all..I have created a Search Form where end-user will enter client Name
and depending on what they enter, I have a list box below that will show the
matching results. What I want to do is basically when user enters lets say
mi, the search should display any client name that starts with MI, if the
user keeps entering more characters like MIKE then the search result should
display that. I wrote the code below and works fine except a user has to hit
enter key to display the result. I don't want it to do that way. Similarly, I
also want when end-user erases any character using back space, it should
requery and display the matching result.

Private Sub txtClientName_AfterUpdate()
Dim txtSearchString
Dim strSQL

txtSearchString = Me.txtClientName.Value
strSQL = "select tblClient_ClientName FROM tblClient WHERE
((tblClient.tblClient_ClientName) Like '" & txtSearchString & "*') "


Me.lstResult.RowSource = strSQL
Me.lstResult.Requery
End Sub

PS. I have used every event like lost focus, on change, after update and
nothing seems to work.

 

[Guest]

I am a little confused by your post. First, is it really a list box? List
boxes do not have a change event and you cannot type data into them. If you
type the letter "T", it will position you on the first entry that starts with
"T". Typing "T" again will take you to the second entry starting with "T"
and then typing "H" will take you to the first entry that starts with "H". On
the other hand, the name of the control is txtClientName, which would
indicate it is a text box, but text boxes don't display a list, so I don't
know what you are really using. If you only want to find one value, then the
Combo box does what you are wanting by using the Auto Expand property.

 

[Guest]

Try it on the OnChage event, but change this line

txtSearchString = Me.txtClientName.Value
To
txtSearchString = Me.txtClientName.Text

 

[Guest]

Ofer - Thanks for your help. This really works like a charm. Exactly the way
I want it. Just curious....what is the difference between me.txt.value
opposed to me.txt.text
That .text was causing not to display the records.

 

[Guest]

Quoted from Access help

"the Text property contains the text data currently in the control; the
Value property contains the last saved data for the control"

So when you enter the text box and type a value, the "value" will return
null because that the last saved value, and "text" will return the value you
typed

 

[Damian]

Hi, I'm trying to do something very similar to what Micky was trying to do.
Rather than using a list box however, my results are displayed in a subform
(datasheet view). It works fine after hitter 'enter' but can anyone offer a
suggestion on how I might alter my code below so that my search results will
automatically update itself as each letter is typed in please? Cheers, Damian

Private Sub SearchTerm_AfterUpdate()

Dim strSQL As String
Dim strWhere As String
strWhere = "1=1 "

strSQL = "SELECT * FROM qryFindSpecies "

If Not IsNull(Me.SearchTerm) Then
strWhere = strWhere & " AND SearchScientific Like ""*" &
Me.SearchTerm & "*"" OR SearchCommon Like ""*" & Me.SearchTerm & "*"""
End If

strSQL = strSQL & " WHERE " & strWhere
Me.frmFindSpecies.Form.RecordSource = strSQL

Exit_SearchTerm_AfterUpdate:
Exit Sub
End Sub

 

[Damian]

I should add that it appears that I use a slightly different method to
achieve the same end, hence the previous fix that was suggested for Micky
won't wok in my case. Cheers, D