Strange Gremlin in Search Field (1 Viewer)

[cambonner]

This may not be appropriate for this forum, but I'll give it a shot. I am the administrator for an MS Access 2016 database for our local food pantry. It's been working well for the past 6 years, except for one (minor) quirk: When a string of characters is entered into the search box to find a client, all works well no matter what sting is entered. If the string appears anywhere in the client's name results appear. 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, since I can duplicate the problem no matter which PC I'm using. Any help someone can offer will be much appreciated.

 

[Gasman]

Have you checked for code?
What happens when you enter a third character?
I'm thinking that some code moves the cursor to start of control?

 

[theDBguy]

Hi. I can't duplicate your problem. I may not be doing it right though. Can you give us a step by step instruction on how to duplicate the issue? Thanks.

 

[Pat Hartman]

Has this problem been going on for long? I think it happened to me last week when I was fighting with a corrupted database. I thought I was losing my mind. It is such a small thing it is hard to find once it is gone

Anyway, I didn't pursue it because I could tell the db was corrupted so I just went into my excise corruption mode and once the db was working again, I haven't seen it since.

So, to start getting rid of corruption.

1. Compile. Fix any compile errors
2. Compact
3. decompile
4. compact again

If that didn't work
1. Create a new, empty db.
2. Open the new db and import all objects
3. Compile. There should not be compile errors since you fixed all of them in the first section.
4. Reset your startup form and any references you might have.

If that didn't work
1. export all forms/reports/queries/macros/modules to text
2. Create a new empty db.
3. Create new links to BE
4. Import all objects from text.
5. Compile
6. Reset your startup form and any references you might have.

If you get to the last set, we can provide the code you need to export/import to/from text.

 

[LarryE]

Maybe some sort of auto-correct setting in auto-correct options?

 

[cambonner]

Have you checked for code?
What happens when you enter a third character?
I'm thinking that some code moves the cursor to start of control?

Thanks for your response. When I enter a third character, it is placed correctly in the third position, but the second typed character remains in the first position, with the "i" having been bumped to second position. E.g, If I enter "ipr" the search box displays "pir"; if I type "Ipr" this is correctly displayed as "Ipr". I didn't design the db and a lot of the code is in SQL, which I am not familiar with. I can adjust settings through the properties menu (design and layout), but that's the limit of my comfort level. Can I send you screen shots of the query, form and SQL code that seem to be involved in this problem?

 

[CJ_London]

suspect you have some code in the control change event which is taking the focus to another control and on it's return you not setting the selstart value

 

[isladogs]

Any chance there is a timer event causing this? Timers can certainly cause similar behaviour in the VBE.

 

[cambonner]

suspect you have some code in the control change event which is taking the focus to another control and on it's return you not setting the selstart value

Any chance there is a timer event causing this? Timers can certainly cause similar behaviour in the VBE.

To my knowledge there are no timer events built into the db.

 

[CJ_London]

what about the change event?

 

[cambonner]

To my knowledge there are no timer events built into the db.

what about the change event?

 

[cambonner]

Unfortunately, I'm not very skilled with Access. I developed the db with help from a programmer who used visual basic and SQL for many functions; I focused on formatting and other property adjustments. Below is a screenshot of the form that is affected and a shot of the code that I think may be involved.

Is this helpful?

 

[theDBguy]

Ah, so we are talking about a custom search box after all. That's why I couldn't duplicate it, because I thought we were talking about the little search box on the status bar at the bottom.

As others have already said, it looks like you have code in the Change event of that Textbox, as evidenced by your second image (look on the Property Sheet) and the image of the code you also posted. You will need to step through that code as you enter text in the search box to see why it's doing what it's doing, instead of what it's supposed to do.

 

[Gasman]

Would also be more beneficial if you posted code as code and not as a picture?

 

[cambonner]

Thanks! I think I'm narrowing in on the problem. I've opened the Existing Client Search Page form, typed in the problematic lower case "i" and get a time run error message, as the attached file shows. (I whited out the address field to protect client confidentiality.) I don't get this message with any other first letter entry, including an upper case "I". When I click on the Debug button, the sub-routine page opens with the (presumably) offending line a code highligted, as the second attached PDF file shows.

Is this helpful in understanding/correcting my problem?

 

[cambonner]

Is this better than a PDF file for the code?

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

'Set the focus on the first item in the list box
Me.SearchResults = Me.SearchResults.ItemData(1)
Me.SearchResults.SetFocus (WHEN I DEBUG, THIS LINE IS HIGHLIGHTED IN YELLOW!!!)

'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

 

[Gasman]

Getting there, better in code tags as that keeps the indentation, making it easier to read?

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

    'Set the focus on the first item in the list box
    Me.SearchResults = Me.SearchResults.ItemData(1)
    Me.SearchResults.SetFocus (WHEN I DEBUG, THIS LINE IS HIGHLIGHTED IN YELLOW!!!)

    '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

Can you set focus to a control that already has the focus?, as you do it further up within the If block?

 

[CJ_London]

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

 

[cambonner]

Getting there, better in code tags as that keeps the indentation, making it easier to read?

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

    'Set the focus on the first item in the list box
    Me.SearchResults = Me.SearchResults.ItemData(1)
    Me.SearchResults.SetFocus (WHEN I DEBUG, THIS LINE IS HIGHLIGHTED IN YELLOW!!!)

    '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

Can you set focus to a control that already has the focus?, as you do it further up within the If block?

Getting there, better in code tags as that keeps the indentation, making it easier to read?

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

    'Set the focus on the first item in the list box
    Me.SearchResults = Me.SearchResults.ItemData(1)
    Me.SearchResults.SetFocus (WHEN I DEBUG, THIS LINE IS HIGHLIGHTED IN YELLOW!!!)

    '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

Can you set focus to a control that already has the focus?, as you do it further up within the If block?

Really appreciate your help, but I'm confused how to do what you suggest: "Can you set focus to a control that already has the focus?, as you do it further up within the If block?." As I mentioned, I'm a total neophyte at coding and at 78 years old, my learning curves tend to be steep. Can you insert the code line(s) you're suggesting into the code that I sent previously? Then I can test it out to see if my problem is solved. Much obliged!

 

[Gasman]

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.