MS Access: Action "onchange" event after a delay?

Question

Good morning!

I have a "fancy" search function in Microsoft Access where the list of possible options shrinks as you type in the search field. Unfortunately the computer and server can't keep up with these rapid requeries of the data.

Currently the command to requery with the field in the 'onchange' function of the search box. I'd like to add a delay so it only runs the requery when the search box has not changed for a second. Thus if someone types in a 8 letter word, it isn't running 8 requeries.

The current idea I have for it, which I know there must be something better, is..

"On change, set search box value to X and wait 1 second. After 1 second, if X = search box value, run the requery. An issue is that it would be rapidly rewriting the X value and have a 'wait' command floating for each letter.

Hopefully there's a way to write an event trigger of "When field X has changed, but not changed for the past second."

Thank you!

As requested, here is my current code

'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
    Me.SearchResults2.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

Obviously this is not MY code, it's from somewhere on the interweb. It works fantastic for databases stored locally, but everything is moving to our Sharepoint server which is running on a 386 in a moldy basement powered by a narcoleptic gerbil.

Answer 1

You can simply use the Timer of the current form. No need for a separate form or anything.

Private Sub DoSearch()

    ' Your current code
    ' but you should look into removing as many "Requery" from there as possible!

End Sub

Private Sub SearchFor_Change()

    ' Wait for x Milliseconds until the search is started.
    ' Each new change restarts the timer interval.
    ' Use 1000 (1 s) for slow typists or a really slow server
    ' 200 ms feels right for a normal typist
    Me.TimerInterval = 200

End Sub

Private Sub Form_Timer()

    ' Disable timer (will be enabled by the next SearchFor_Change)
    Me.TimerInterval = 0
    ' Now run the search
    DoSearch

End Sub

Note: you may need to move some of the cursor-handling code from DoSearch() to SearchFor_Change(), specifically:

Me.SearchFor.SelStart = Len(Me.SearchFor)

Answer 2

Assign a shortcut key like (Ctrl+ J) to the logic in on change event and call it on demand once you have finished typing search keyword.

1. Remove on change event.
2. Create other procedure which has the logic of on change event and assign a shortcut key
3. Press shortcut to get search suggestion

---

Other approach

Add below validation to Change event which will check for length of string and will trigger only if length of string is >=8

Private Sub txtSearch_Change()

    If Len(Nz(txtSearch.Text, 0)) >= 8 Then


    End If

End Sub

Answer 3

I'm going a little outside my comfort area, since I hardly use MS Access forms, but why are you bothering the Server/Database so much? In my experience, each query costs the same amount of time, whether it returns 1 record or 100,000 records.

So even before the user types anything, why don't you just do a single query to return a sorted list. After that, it takes almost no time to use VBA to process the results and find everything in the list that starts with whatever the user types in (it's sorted after all).

Except for the initial load, users who are local to the database or on the other side of the world will experience the same snappy response from your interface.

----------

Like I said, I haven't messed with Access Forms a lot, so this is more of a strict VBA solution. Maybe there is a better way to do it without going outside the Access Forms box that someone could enlighten us with.

You should basically just call LoadItemList when you load the form, or whenever you need to.

Public dbConn As ADODB.Connection
Private ItemList As Variant
Private RecordCount As Long

Sub LoadItemList()
Dim SQL As String
Dim RS As New ADODB.Recordset

    SQL = "SELECT T.Name FROM Table T"
    Set RS = dbConn.Execute(SQL)
    If Not RS.EOF Then
        ItemList = RS.GetRows
        RecordCount = UBound(ItemList, 2) - LBound(ItemList, 2) + 1
    End If
End Sub

Then replace DoCmd.Requery with AddItemtoCombobox SearchResults, SearchFor.Text

Sub AddItemtoCombobox(Control As ComboBox, Filter As String)
Dim Index As Long
    Control.Clear
    If Not IsEmpty(ItemList) Then
        For Index = 0 To RecordCount - 1
            If ItemList(Index) Like Filter Then Control.AddItem ItemList(Index)
        Next
    End If
End Sub

Again, maybe there is a better way that is built into Access...

Answer 4

The technical term that you're looking for is debounce.

What you can do is on your on change event, keep track of the current search string

in terms of pseudocode.

sub onChange()
   Form.timerinterval = 0
   setSearchString
   form.timerinterval = delay

So in terms of the explanation, if your on change is called, disable the timer. Update your search string, then reset the timer to fire after a certain amount of time. The form should be a hidden form that contains the code that you want to execute