Searchfilter - not possible to type trailing space

[ORION]

Hello,

I'm working on an Access database that should be used in a French speaking country. In order to avoid that the user must type all accents of search terms correct, I have added a string with replace commands to allow easy recovery of the searched items.

I use a split form as search form. In the header of the form a textbox is placed where the usere enters his search as he/she types.

The following code works fine (can be used to type space between words)

Code: Select all

Private Sub txt_filter_Change()

'--------------------------------------------
'CODE frm_Select_Patient - 001
'--------------------------------------------

  ' If the textbox is cleared, clear the form filter.
  If Nz(Me.txt_Filter.Text) = "" Then
    Me.Form.Filter = ""
    Me.FilterOn = False
  Else  ' If a partial value is typed, filter for a partial name match.

     Me.Form.Filter = "[Full_Name] Like '*" & _
                     Replace(Me.txt_Filter.Text, "'", "''") & "*'"
      Me.FilterOn = True

  End If
  
  ' Move the cursor to the end of the box.
  Me.txt_Filter.SetFocus
  Me.txt_Filter.SelStart = Len(Me.txt_Filter.Text)
  
End Sub

But if I replace the line after else for a declared search string, like in the following example it, becomes impossible to type a space at the end of the searchbox. The cause seems to be that the focus on this textbox is lost.

Code: Select all

Private Sub txt_filter_Change()

'--------------------------------------------
'CODE frm_Select_Anamnèse - 002
'--------------------------------------------

Dim strSansAccents As String

  ' If the box is cleared, clear the form filter.
  If Nz(Me.txt_Filter.Text) = "" Then
    Me.Form.Filter = ""
    Me.FilterOn = False

  Else  ' If a partial value is typed, filter for a partial company name match.
    'remove all accents and compare with the "maladie" without accents (so that the user does not have to care about accents)
    strSansAccents = Replace(Replace(Replace(Replace(Replace(Replace(Me.txt_Filter.Text, "é", "e"), "ë", "e"), "è", "e"), "ê", "e"), "ï", "i"), "à", "a")
    Me.Form.Filter = "[MaladieSansAccents] Like '*" & _
                     Replace(strSansAccents, "'", "''") & "*'"
    Me.FilterOn = True

  End If
  
  ' Move the cursor to the end of the box.
 Me.txt_Filter.SetFocus
 Me.txt_Filter.SelStart = Len(Me.txt_Filter.Text)

 
End Sub

Can anyone explain me how to keep the focus on the textbox or the reason why the filtering works different in both cases?

Kind regards,

Orion

[HansV]

Welcome to Eileen's Lounge!

The moment a text box loses focus, Access removes all trailing spaces. There is no way to prevent this, and I don't see how the first procedure would allow you to enter a trailing space. Are you absolutely sure about that?

[ORION]

Hello Hans,

I recently replaced the original combobox that I used for the search as you type application with a textbox, and was fully convinced that the first option worked fine, but indeed it does not. This leads me to the conclusion that if I want to type more than one word into the seach filter I should use a combobox.
I experienced that using a combobox in combination with the replacement string slowed down the application.

What I've learned is that I should forget the idea to use a textbox for a search based on several words.

I've also red again your post concerning Force Access NOT to trim trailing spaces (Access 2010) and understood that a workaround is to copy a tekst with a space in it but not at the end.

Thanks for your help and helping me understand.

Kind regards,

Albert

Kind regards,

Orion

[HansV]

You're welcome! Glad to have been able to help.