Strange Gremlin in Search Field

[SHANEMAC51]

It's been working well for the past 6 years, except for one (minor) quirk:

''with this code , it works
''''Populate the string variable with the text entered in the Text Box SearchFor
    searchstring = "" & Search For.Text

''at the same time - does not work normally
'Populate the string variable with the text entered in the Text Box SearchFor
    searchstring = "" & Search For.value

''perhaps the error has already been fixed

 

[cambonner]

Please help me locate precisely where the new code should be inserted in the existing code. Does it go right before the line Me.SearchResults.SetFocus?

 

[SHANEMAC51]

Please help me locate precisely where the new code should be inserted in the existing code. Does it go right before the line Me.SearchResults.SetFocus?

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

 

[cambonner]

''with this code , it works
''''Populate the string variable with the text entered in the Text Box SearchFor
    searchstring = "" & Search For.Text

''at the same time - does not work normally
'Populate the string variable with the text entered in the Text Box SearchFor
    searchstring = "" & Search For.value

''perhaps the error has already been fixed

Not yet fixed -- most likely due to my inexperience. I inserted the 2 new code lines you suggested (searchstring = "" & SearchFor.Text and searchstring = "" & SearchFor.value). But I still get the same runtime error 2110, when I enter small "i". Am I also to include the code (changing to upper case) that you suggested earlier? If so, where?

Update: Don't get runtime error, but the problem with the small i persists.

 

[GK in the UK]

Don't think you've put the code suggested by Shanemac51 in the right place. Post #26, get rid of your new code that starts 'New Sub routine to convert ANSI to character string

In the search form properties sheet, select the search field and click the ellipsis (...) On Key Press event. Pick Code Builder.

The code module will open and you will get this:

Private Sub SearchFor_KeyPress(KeyAscii As Integer)

End Sub

Add the code line so it looks like this: (this is a one-line version of Shanemac51's code)

Private Sub SearchFor_KeyPress(KeyAscii As Integer)
       KeyAscii = Asc(UCase(Chr(KeyAscii)))
End Sub

You will find that all characters entered in the search box will become upper case. It's a work-around, I don't know what the issue with the lower case "i" actually is.

Your search form code is adapted from here: Dynamically search multiple fields | Access World Forums (access-programmers.co.uk)

 

[Cronk]

I also cannot replicate your problem. I wonder though if it's spell checking. Try turning off Capitalize first letter File | Options | Proofing |Autocorrect Options

 

[theDBguy]

I also cannot replicate your problem. I wonder though if it's spell checking. Try turning off Capitalize first letter File | Options | Proofing |Autocorrect Options

I have all of those settings checked and still did not have any problems. Were yours unchecked?

 

[Cronk]

@dbGuy Yes, just a long shot for the OP

@cambonner
Make a copy of your FE and replace the code in Sub SearchFor_Change() with the following

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

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

    Me.SearchResults.RowSource = "SELECT TBL_Client_Basic_Info.[First Name], TBL_Client_Basic_Info.[Last Name], " & _
         "TBL_Client_Basic_Info.[Street Address], TBL_Client_Basic_Info.[Client ID] " & _
         "FROM TBL_Client_Basic_Info " & _
         "WHERE TBL_Client_Basic_Info.[First Name] Like " & Chr(34) & "*" & vSearchString & "*" & Chr(34) & _
         " Or TBL_Client_Basic_Info.[Last Name] Like " & Chr(34) & "*" & vSearchString & "*" & Chr(34) & _
         " ORDER BY TBL_Client_Basic_Info.[First Name];"

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

    If Len(Me.SearchFor & "") > 0 Then
        Me.SearchFor.SelStart = Len(Me.SearchFor)
    End If

There was a lot of lines just devoted to removing any trailing blank. RTrim() handles that in one line. Also, to get the first item in the list box, it's necessary to use ItemData(0) as it's zero based.

Let's know how you get on.

 

[SHANEMAC51]

"WHERE TBL_Client_Basic_Info.[First Name] Like " & Chr(34) & "*" & vSearchString & "*" & Chr(34) & _ " Or TBL_Client_Basic_Info.[Last Name] Like " & Chr(34) & "*" & vSearchString & "*" & Chr(34) & _

 " WHERE TBL_Client_Basic_Info.[First Name] Like "'*" & vSearchString & "*'" & _
   " Or TBL_Client_Basic_Info.[Last Name] Like "'*" & vSearchString & "*'" & _

 

[CarlettoFed]

Attach a sample file and maybe it's easier to help you.

 

[cambonner]

Don't think you've put the code suggested by Shanemac51 in the right place. Post #26, get rid of your new code that starts 'New Sub routine to convert ANSI to character string

In the search form properties sheet, select the search field and click the ellipsis (...) On Key Press event. Pick Code Builder.

The code module will open and you will get this:

Private Sub SearchFor_KeyPress(KeyAscii As Integer)

End Sub

Add the code line so it looks like this: (this is a one-line version of Shanemac51's code)

Private Sub SearchFor_KeyPress(KeyAscii As Integer)
       KeyAscii = Asc(UCase(Chr(KeyAscii)))
End Sub

You will find that all characters entered in the search box will become upper case. It's a work-around, I don't know what the issue with the lower case "i" actually is.

Your search form code is adapted from here: Dynamically search multiple fields | Access World Forums (access-programmers.co.uk)

Wow! It worked, it worked, it worked. You provided such an easy-to-follow and elegant solution. Really appreciated.
Thanks so much for your help!

 

[Cronk]

" WHERE TBL_Client_Basic_Info.[First Name] Like "'*" & vSearchString & "*'" & _ " Or TBL_Client_Basic_Info.[Last Name] Like "'*" & vSearchString & "*'" & _

That's fine until you are searching for a surname such as O'Connor when a run time error will occur.

 

[cambonner]

That's fine until you are searching for a surname such as O'Connor when a run time error will occur.

Don't have any O'Connor in our client pool, but have an O'Riley which worked as expected (no run-time error).

 

[Cronk]

I was referring to the code in #49. If you used my code, it does not have a problem with names containing single quotes.