Search based on changing text 2

[ZOR]

I have a textbox/memo field. The user types in descriptive text of a sports event. During typing in text, the word being typed in maybe of someones name currently stored in a names table. Has anyone attempted to do such a thing that searches during a word being typed in then rebuilds its search when spacebar hit to type in further words. Need to have an idea how to capture the current word being typed. Would a routine involving Keycode be best? Thanks

 

[PHV]

Use the Change event and the Text property of the control.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

How are ya ZOR . . .

Have a look at the [blue]On Change[/blue] event of the control . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[ZOR]

Thanks both, your answers were in a minute of each other? Put back your lost star PHV. It's where do I go from there, re capturing space bar etc, as when a user types in text it has to be held in a growing string for a query to work and then refreshed after a space has been entered. But then I think what happens if a user types in the word Franp, the query would have been working to find Frank until the user used backspace to correct the p to a k. Actually while thinking as typing this thread, the text of the memo field could be put in a label so that would reflect the changed word and be used for search. Any other thoughts welcomed. Regards

 

[MajP]

I picture this working like MS Word where you type and it finds mispellings dynamically. This is not bullet proof but works ok, but I do not know how efficient. Try it in Nortwind and use the last name "fuller".

here is my strategy
1)After hitting a spacebar it takes the memo and splits it into an array
2) It takes each word and if it is common or if it has already been searched and not found, it ingnores it


mdlFindNames

Public aCheckedWords() As Variant
'you can get a few thousand of these from the internet
Public Function getCommonWords() As String
 getCommonWords = getCommonWords & "the,or,will,number,of,one,up,no,and,had,other,way,"
 getCommonWords = getCommonWords & "a,by,about,could,to,word,out,people,"
 getCommonWords = getCommonWords & "in,but,many,my,is,not,then,than,"
 getCommonWords = getCommonWords & "you,what,them,first,that,all,these,water,it,were,so,been,"
 getCommonWords = getCommonWords & "he,we,some,call,was,when,her,who,"
 getCommonWords = getCommonWords & "for,your,would,oil,on,can,make,its,"
 getCommonWords = getCommonWords & "are,said,Like,Now,as,there,him,find,"
 getCommonWords = getCommonWords & "with,use,into,long,his,an,time,down,"
 getCommonWords = getCommonWords & "they,each,has,day,I,,which,,look,did,"
 getCommonWords = getCommonWords & "at,she,two,get,be,do,more,come,"
 getCommonWords = getCommonWords & "this,how,write,made,have,their,,go,may,from,if,see,part"
End Function
Public Function isCommon(strWord As String) As Boolean
  If InStr(getCommonWords, strWord) Then
    isCommon = True
  End If
End Function
Public Sub addToCheckedWords(strWord As String)
 If isArrayEmpty(aCheckedWords) Then
   ReDim aCheckedWords(1)
   aCheckedWords(1) = strWord
 Else
   ReDim Preserve aCheckedWords(UBound(aCheckedWords) + 1) As Variant
   aCheckedWords(UBound(aCheckedWords)) = strWord
 End If
End Sub
Private Function isArrayEmpty(arr As Variant) As Boolean
  Dim dblCount As Double
  isArrayEmpty = True
On Error Resume Next
' CAUSES AN ERROR IF EMPTY
  dblCount = UBound(arr)
If Err.Number > 0 Then Exit Function
  isArrayEmpty = False
End Function
Public Function isInArray(arr As Variant, strWord As String) As Boolean
  Dim intCounter As Integer
  If Not isArrayEmpty(arr) Then
    For intCounter = LBound(arr) To UBound(arr)
      If UCase(strWord) = UCase(arr(intCounter)) Then
        isInArray = True
      End If
    Next intCounter
  End If
End Function

Public Function NameExists(strName As String) As Boolean
  Dim strWhere As String
  strWhere = "LastName = '" & strName & "'"
  If Not IsNull(DLookup("LastName", "Employees", strWhere)) Then
    NameExists = True
  End If
End Function

form code

Private Sub txtCheckNames_KeyPress(KeyAscii As Integer)
  Dim aWords() As String
  Dim intCounter As Integer
  If KeyAscii = 32 Then
    aWords() = Split(txtCheckNames.Text, " ")
    For intCounter = LBound(aWords) To UBound(aWords)
      If Not isCommon(aWords(intCounter)) And Not isInArray(aCheckedWords, aWords(intCounter)) Then
         If NameExists(aWords(intCounter)) Then
           'do something here because name is found
           MsgBox "Name found " & aWords(intCounter)
         Else
           Call addToCheckedWords(aWords(intCounter))
         End If
      End If
    Next intCounter
  End If
End Sub

 

[ZOR]

Many thanks, I will give it a try, however can someone tell me why this does not work. The label UU just keeps showing all the text going into Text1. Thanks

Dim GY As Integer, GT As Integer

Private Sub Text1_Change()
If GT = 0 Then
GT = 1
End If

If IsNull(Me.Text1.Text) = False Then
GY = Len(Me.Text1.Text)
End If

Me.UU.Caption = Mid$(Text1.Text, GT, GY)
End Sub

Private Sub Text1_KeyPress(KeyAscii As Integer)
If KeyAscii = 32 Then
Me.UU.Caption = ""
GT = GY
End If

End Sub

 

[PHV]

You may try this single event procedure:

Private Sub Text1_Change()
Dim myArray
If Trim(Me!Text1.Text & "") = "" Then
  Me!UU.Caption = ""
Else
  myArray = Split(Trim(Me!Text1.Text))
  Me!UU.Caption = myArray(UBound(myArray))
End If
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ZOR]

PHV your a star. Thats the solution I was looking for. I'm back to a deficit of a star so I will make it up somewhere else. Now I can use that in a query and move on. Regards all.

 

[MajP]

That is part of the logic I was not sure about, without it getting too complicated.

I choose not to only look at the last word because It would be hard to do the following

1)I start typing.
"In event A John Smith won silver"
2)But I decide to tab back and change that to read.
"In event A Mike Jones won gold, and John Smith won silver

So I think the logic you need is to look not at the last word but the last word from the cursor position.

 

[MajP]

I think this pulls the last word typed, vice the last word. It seems to work and will make the code more efficient.

Private Sub txtCheckNames_KeyPress(KeyAscii As Integer)
  Dim aWords() As String
  Dim intCounter As Integer
  Dim strCurrentText As String
  Dim strLastWord As String
  If KeyAscii = 32 Then
    strCurrentText = Left(txtCheckNames.Text, txtCheckNames.SelStart)
    aWords() = Split(Trim(txtCheckNames.Text), " ")
    strLastWord = aWords(UBound(aWords))
      Debug.Print strLastWord
      If Not isCommon(strLastWord) And Not isInArray(aCheckedWords, strLastWord) Then
         If NameExists(strLastWord) Then
           'do something here because name is found
           MsgBox "Name found " & strLastWord
         Else
           Call addToCheckedWords(strLastWord)
         End If
      End If

  End If
End Sub

 

[ZOR]

Hi Majp, and thanks. No, the code PHV provided is perfecto, as when a user starts typing a word, the query goes to see if it matches the start part of a name and puts it in a listbox for the user to select from to replace whats he's just typed in. Havent got to that bit yet which sounds a bundle of fun. If the user uses the backspace then it does update the content in the label so would requery with that data. But thanks for your inclusion of your code, star this time. So the bit I have to suss out now is when the user types in Fran and selects a Frank Butcher from the list, Fran gets replaced with Frank Butcher and the cursor in the textbox moves forward for the next word. Its just to reduce the users time typing in names. Regards

 

[MajP]

Yes that works with back space, but my point was if you arrow or mouse back in the text


"the fox jumps over"
then i back arrow or use the mouse to insert my cursor after the

"the (start typing) brown fox jumps over"

My code will return the word brown. PHVs will return "over"

I wrote the above code wrong
should read

aWords() = Split(Trim(strCurrentText), " ")

 

[ZOR]

Thanks for your explanation. I see what you mean, a user could type in a lot of text, then realise a few words back he'd mistyped, uses his mouse to go back and the action fails. I will have to think whether the user should get a kick if a listbox does not popup with a name at the time of typing which he knows should happen, or I go for the full job. I will try your code and see where I get, it's certainly worth all the options, especially as it extends to common words. Will let you know and thanks.

 

[ZOR]

I fired it up, changed If Not IsNull(DLookup("LastName", "Employees", strWhere)) to be for my table, however tracing/debugging as nothing seemed to happen, found it goes to aCheckedWords. Don't know why it seemed dead, I typed in the word "Will" which was amongst the common words and nothing happened possibly because the words not in the Athletes table? Don't quite know how my Athlete names can relate to common words, but I will keep playing with it.

 

[ZOR]

Typing in the word Will, it never reaches the Funtion NameExists(strName As String) As Boolean

Regards

 

[MajP]

My idea was to avoid calling the dlookup for all words, but that logic is not bullet proof. Because as you shown "Will" is a first name and a common word in that list. However you could also check if the word is capitlaized and then always search for it so "Will" will search but not "will

 

[MajP]

You could avoid the common word search all together or improve on the logic, I was just demonstrating a possible heuristic.

 

[ZOR]

Understood with thanks. Have a good weekend