Search code examples
ms-accessvbams-access-2016

Truncating trailing spaces in Access for Search-As-You-Type

I am creating a dynamic search-as-you-type that filters a list of data as the user types in a text box. 

Private Sub TxtSearch_Change()
Dim CursorPosition As Long
Dim strSearch As String
Dim sqlSearch As String

CursorPosition = TxtSearch.SelStart

Me.Dirty = False 'set the dirty property to false to save the current value
strSearch = ""
If Not IsNull(Me.TxtSearch.Value) Then
    strSearch = Me.TxtSearch.Value
End If

searchLength = Len(strSearch)
If searchLength < CursorPosition Then
    For i = 1 To (CursorPosition- searchLength)
        strSearch = strSearch + " "
    Next
End If

'Check if a keyword has been entered or not
If strSearch = "" Then
    Me.TxtSearch.SetFocus
    sqlShowAll = "SELECT * FROM qrySearch"
    Forms![frmSearch]!fsubTest.Form.RecordSource = sqlShowAll
Else
    sqlSelect = "SELECT * FROM qrySearch WHERE ("
    sqlLastName = "(LastName Like ""*" & strSearch & "*"")"
    sqlFirstName = " OR (FirstName Like ""*" & strSearch & "*"")"
    sqlFullName = " OR (FullName Like ""*" & strSearch & "*"")"
    sqlEnd = ")"
    sqlAllNames = sqlLastName & sqlFirstName & sqlFullName
    sqlSearch = sqlSelect & sqlAllNames & sqlEnd
   Forms![frmSearch]!fsubTest.Form.RecordSource = sqlSearch

End If

TxtSearch.SelStart = CursorPosition

End Sub

Access truncates trailing spaces in text fields. Is there a way to get around this? I have already implemented a for loop to restore the trailing space for search purposes, but I'd like to save the trailing space so that as the user continues typing the space has not disappeared. For example, I could enter "Jane " and search for "Jane " but when returned to the text box, I would see "Jane" so I could never type "Jane Doe" but only "JaneDoe".

Solution

  • Here's the code I use to accomplish what you're looking for. I have the search box "Searchfor" as where I type, and "SearchResults" as a combobox with the data. There's also a text box "SrchText" which is used by the query "QRY_SearchAll." That query is a series of "Like "" & [Forms]![FRM_SearchMulti]![SrchText] & """ for each field I want displayed in the combo box, see picture. QrySearchall image

    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 = 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

'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

    One warning about this sytem: It uses requery instead of refresh. This is fine for a reasonable number of records on a reasonbly fast system. I found that when I tried to use this same code for data on an ancient Sharepoint server, I'd be hitting a 10 second delay after each letter I type. So if you're dealing with a lot of records or a slow server, you may want to change 'requery' to 'refresh.'