Strange Gremlin in Search Field (1 Viewer)

[cambonner]

seems a really convoluted way of doing a search - you have the searchfor textbox populating the vsearchstring variable which in turn then populates another textbox (srchtxt). Then if there is a space in it you requery a listbox (searchresults) and set the focus to it, requery the whole form and return focus back to searchfor and set the selength.

Otherwise you move the focus to the searchresults listbox (where you get your error), requery the whole form and finally return focus back to searchfor and set the sellength again.

I'm guessing the idea is the user can search for several names separated by a space - i.e. user enters 'smith jones' and listbox will be populated with records which contain smith or jones.

Think we would need to see the sql to your QRY_SearchAll which is presumably the rowsource to your listbox. My guess is that you are using a case sensitive comparison in your criteria and lower case i returns no matches so the listbox has no rows, so cannot accept the focus

To clarify: The purpose of the client search function is to allow the user to enter any string of characters that may be present anywhere in the client's name. The displayed results, which continuously shrink as more of the string is typed, will be a list of clients' full names. E.g. if "man" is entered, these results will appear in a box below the search field: Mandy Brown, Tom Roman, Amanda Smith, Jane Mansfield. From this list, the user selects the searched-for client which opens the client's form containing specifics about family size, last food pickup dates, etc.

 

[cambonner]

I was asking whether one can actually set focus to a control when it already has the focus.
I've just tried on a test DB of mine and it appears you can.

Can you suggest what and where in the code I would enter this?

 

[SHANEMAC51]

But there's one exception to this: if the string starts with a lower case letter i, the next letter typed is placed before rather than after the i. (If the string starts with an upper case I this fluke does not happen.) This is not a computer or keyboard bug,

whatever the code is - why is there only one letter, what is it guilty of, there may be a handler for the characters being entered

 

[cambonner]

whatever the code is - why is there only one letter, what is it guilty of, there may be a handler for the characters being entered

I've just entered all combination pairs of letters into the search box and it is only the lower case i that creates the problem. Could it be a virus problem rather than a coding issue? What is a "handler" for the characters, and how might I investigate this?

 

[SHANEMAC51]

I've just entered all combination pairs of letters into the search box

I meant the event

Private Sub ShipRegion_KeyPress(KeyAscii As Integer)
Dim strCharacter As String

' Convert ANSI value to character string.
str Character = Chr(KeyAscii)
' ' ' ' Convert character to upper case, then to ANSI value.
KeyAscii = Asc(UCase(strCharacter))
End Sub

 

[cambonner]

I meant the event

Private Sub ShipRegion_KeyPress(KeyAscii As Integer)
Dim strCharacter As String

' Convert ANSI value to character string.
str Character = Chr(KeyAscii)
' ' ' ' Convert character to upper case, then to ANSI value.
KeyAscii = Asc(UCase(strCharacter))
End Sub

I entered (by copy-paste) your suggested new code just after the line End If (See following). This seems to have stopped the leftward shift of the "i", but now I get these messages when I enter any and all characters into the search box. I suspect I entered the new code in the wrong place. Can you help me correct this?

Private Sub SearchFor_Change()
 'Create a string (text) variable
    Dim vSearchString As String

'Populate the string variable with the text entered in the Text Box SearchFor
    vSearchString = SearchFor.Text

'Pass the value contained in the string variable to the hidden text box SrchText,
'that is used as the sear4ch criteria for the Query QRY_SearchAll
    SrchText.Value = vSearchString

'Requery the List Box to show the latest results for the text entered in Text Box SearchFor
    Me.SearchResults.Requery


'Tests for a trailing space and exits the sub routine at this point
'so as to preserve the trailing space, which would be lost if focus was shifted from Text Box SearchFor
    If Len(Me.SrchText) <> 0 And InStr(Len(SrchText), SrchText, " ", vbTextCompare) Then
        'Set the focus on the first item in the list box
            Me.SearchResults = Me.SearchResults.ItemData(1)
            Me.SearchResults.SetFocus
        'Requery the form to refresh the content of any unbound text box that might be feeding off the record source of  the List Box
            DoCmd.Requery
        'Returns the cursor to the the end of the text in Text Box SearchFor,
        'and restores trailing space lost when focus is shifted to the list box
            Me.SearchFor = vSearchString
            Me.SearchFor.SetFocus
            Me.SearchFor.SelStart = Me.SearchFor.SelLength
            
        Exit Sub
    End If
    
'New Sub routine to convert ANSI to character string
Private Sub ShipRegion_KeyPress(KeyAscii As Integer)
Dim strCharacter As String

' Convert ANSI value to character string.
Str Character = Chr(KeyAscii)
' ' ' ' Convert character to upper case, then to ANSI value.
KeyAscii = Asc(UCase(strCharacter))
End Sub

'Set the focus on the first item in the list box
    Me.SearchResults = Me.SearchResults.ItemData(1)
    Me.SearchResults.SetFocus

'Requery the form to refresh the content of any unbound text box that might be feeding off the record source of  the List Box
    DoCmd.Requery

 

[snow-raven]

I know you've gone deep-dive into the code already (TL/DR), but I get this behavior sometimes when I copy data from the wed. Something to do with the line break character used in most HTML. I can avoid it if I'm careful to stop copying with the last text letter (harder than it sounds when I'm in a hurry).
So, my question is, are you copy pasting when you encounter this problem?

 

[theDBguy]

I entered (by copy-paste) your suggested new code just after the line End If (See following). This seems to have stopped the leftward shift of the "i", but now I get these messages when I enter any and all characters into the search box. I suspect I entered the new code in the wrong place. Can you help me correct this?

Private Sub SearchFor_Change()
 'Create a string (text) variable
    Dim vSearchString As String

'Populate the string variable with the text entered in the Text Box SearchFor
    vSearchString = SearchFor.Text

'Pass the value contained in the string variable to the hidden text box SrchText,
'that is used as the sear4ch criteria for the Query QRY_SearchAll
    SrchText.Value = vSearchString

'Requery the List Box to show the latest results for the text entered in Text Box SearchFor
    Me.SearchResults.Requery


'Tests for a trailing space and exits the sub routine at this point
'so as to preserve the trailing space, which would be lost if focus was shifted from Text Box SearchFor
    If Len(Me.SrchText) <> 0 And InStr(Len(SrchText), SrchText, " ", vbTextCompare) Then
        'Set the focus on the first item in the list box
            Me.SearchResults = Me.SearchResults.ItemData(1)
            Me.SearchResults.SetFocus
        'Requery the form to refresh the content of any unbound text box that might be feeding off the record source of  the List Box
            DoCmd.Requery
        'Returns the cursor to the the end of the text in Text Box SearchFor,
        'and restores trailing space lost when focus is shifted to the list box
            Me.SearchFor = vSearchString
            Me.SearchFor.SetFocus
            Me.SearchFor.SelStart = Me.SearchFor.SelLength
            
        Exit Sub
    End If
    
'New Sub routine to convert ANSI to character string
Private Sub ShipRegion_KeyPress(KeyAscii As Integer)
Dim strCharacter As String

' Convert ANSI value to character string.
Str Character = Chr(KeyAscii)
' ' ' ' Convert character to upper case, then to ANSI value.
KeyAscii = Asc(UCase(strCharacter))
End Sub

'Set the focus on the first item in the list box
    Me.SearchResults = Me.SearchResults.ItemData(1)
    Me.SearchResults.SetFocus

'Requery the form to refresh the content of any unbound text box that might be feeding off the record source of  the List Box
    DoCmd.Requery

View attachment 98592 View attachment 98593

The error message says: "Expected End Sub."

If you have something like this at the top:

Private Sub something...()

At the very bottom of everything that comes after that, you'll need something like this:

End Sub

So, make sure you have one of those.

 

[Gasman]

If all this fails, MajP's FAYT classes would be a good replacement.

 

[isladogs]

You can set focus to any control that allows that event, whether or not it already has focus.
But I'm not sure why you asked the question....

 

[Cronk]

I can't replicate your issue using the code you have supplied. Perhaps it's time to provide a copy of your database. Remove any confidential data first.

 

[cambonner]

I can't replicate your issue using the code you have supplied. Perhaps it's time to provide a copy of your database. Remove any confidential data first.

That would be wonderful. I'll provide a dummy set of client data. Shall I zip the files and attach to this thread, or can I email it to you?

 

[Cronk]

Zip the files and attach the zip to a message to this thread. I'm not saying I will have the time then to look but someone is sure to.

 

[cambonner]

Attached are 2 zipped files: db structure with code and (anonymized) data tables for our local food pantry. To reiterate the issue: I'm trying to solve a problem that occurs in the Existing Client Name search box: Whenever a small "i" is typed as the leading character in the search string, its position shifts to the second position when a second character is entered, which itself shifts to the first position. This only happens when "i" is typed first; it doesn't happen with any other lower or upper case letter, including a capital "I".

 

[CJ_London]

would help if you disabled all the stuff when the app opens, restore tabs, all the stuff that prevents editing, looking at code etc. Really don't have the time to spend disabling it all or working out where to look. And would also help if you mentioned which form is the problem and which control. I looked in the FRM_Existing_Client_Data_Form form but do not see a search box.

 

[cambonner]

Whatever time you have to give is appreciated. As I mentioned earlier, I'm a complete novice when it comes to modifying the code, and even some of the properties/macros. My role before I took over maintenance of the db was formatting and other simple changes. So, I'm not really sure how to do what you're asking, i.e., disabling, restoring, etc. I usually just left-shift-click to open the db to see all of its objects and codes, and the go to design view to make changes. The form that is giving problems is FRM_SearchMulti, where the search box is upper left. When I enter small "i" I get Run-time error 2110 with the message "RFP Database can't move to the controlSearchResults." When I press Debug, the following code is displayed:

'Set the focus on the first item in the list box
Me.SearchResults = Me.SearchResults.ItemData(1)
Me.SearchResults.SetFocus

'Requery the form to refresh the content of any unbound text box that might be feeding off the record source of the List Box
DoCmd.Requery

'Returns the cursor to the the end of the text in Text Box SearchFor
Me.SearchFor.SetFocus

If Not IsNull(Len(Me.SearchFor)) Then
Me.SearchFor.SelStart = Len(Me.SearchFor)
End If
End Sub

 

[theDBguy]

Whatever time you have to give is appreciated. As I mentioned earlier, I'm a complete novice when it comes to modifying the code, and even some of the properties/macros. My role before I took over maintenance of the db was formatting and other simple changes. So, I'm not really sure how to do what you're asking, i.e., disabling, restoring, etc. I usually just left-shift-click to open the db to see all of its objects and codes, and the go to design view to make changes. The form that is giving problems is FRM_SearchMulti, where the search box is upper left. When I enter small "i" I get Run-time error 2110 with the message "RFP Database can't move to the controlSearchResults." When I press Debug, the following code is displayed:

'Set the focus on the first item in the list box
Me.SearchResults = Me.SearchResults.ItemData(1)
Me.SearchResults.SetFocus

'Requery the form to refresh the content of any unbound text box that might be feeding off the record source of the List Box
DoCmd.Requery

'Returns the cursor to the the end of the text in Text Box SearchFor
Me.SearchFor.SetFocus

If Not IsNull(Len(Me.SearchFor)) Then
Me.SearchFor.SelStart = Len(Me.SearchFor)
End If
End Sub

Hi. I just tried your files, and I didn't get any errors. Just confirm, here's what I did:

I opened FRM_SearchMulti and in the "Enter any part of Client's Name," I entered "iam" (without the quotes). The listbox below showed "William (Billy)." No errors.

Did I do the steps correctly?

 

[cambonner]

Yes you did it correctly. What a puzzle! When I do the same thing, even on other computers (home, food pantry, wife's) I get the funny "i" behavior. A virus perhaps buried in the code, that somehow disappeared at your end???

 

[theDBguy]

Yes you did it correctly. What a puzzle! When I do the same thing, even on other computers (home, food pantry, wife's) I get the funny "i" behavior. A virus perhaps buried in the code, that somehow disappeared at your end???

Well, my guess is it's a "Settings" thing. Let me dig around to see if I can find something to make it act weird.

 

[cambonner]

Well, my guess is it's a "Settings" thing. Let me dig around to see if I can find something to make it act weird.

Yesterday, a forum member, Shanemac51, suggested a work-around by converting Ascii characters to upper case, using the code below. I tried inserting the code into the offending procedure, and it seemed to solve the small "i" problem, but I got other error messages. I suspect that I inserted the new code segment in the wrong place. Is this something you can try for me using the db files I sent to you?

Private Sub ShipRegion_KeyPress(KeyAscii As Integer)
Dim strCharacter As String

' Convert ANSI value to character string.
str Character = Chr(KeyAscii)
' ' ' ' Convert character to upper case, then to ANSI value.
KeyAscii = Asc(UCase(strCharacter))
End Sub