Search a list while the user types

Build a form whose customer list filters itself 400 ms after the last key press, and whose counter of open sessions refreshes every 30 seconds. Both run on one WklTimerList; no DoEvents loop and no form timer are needed.

• Microsoft 365 Access or the Access Runtime, 32-bit or 64-bit, with the WKL Forms Object Library 1.60 or later installed and registered for the current Windows user (How to: Install the released package).
• A table tblCustomers with the fields ID (Number, primary key) and Name (Short Text), and a few records; a table tblSessions with ID (Number, primary key) and IsOpen (Yes/No).

Create a form frmSearch in Design view with:

• a text box txtSearch,
• a list box lstCustomers (Row Source Type Table/Query, two columns, first column width 0),
• a text box txtOpen,
• the timer list: Form Design › Controls › ActiveX Controls, WKL Timer List, name it Timer1. It shows its name in Design view and is invisible in Form view.

The tasks are stored with the form. Define them once in code in Design view: paste the procedure into a standard module and run it from the Immediate window (SetupSearchTasks). The designer of the control (property sheet, tab Other, row Custom, [...], page Tasks) holds the same settings in a table.

Public Sub SetupSearchTasks()
    Dim t As Object
    On Error GoTo ErrorHandler
 
    DoCmd.OpenForm "frmSearch", acDesign
    Set t = Forms("frmSearch").Controls("Timer1").Object
    t.Tasks.Clear
    t.Tasks.Add "search"
    t.Tasks("search").Delay = 400        ' 400 ms after the last Restart
    t.Tasks.Add "count"
    t.Tasks("count").Interval = 30000    ' every 30 seconds
    DoCmd.Close acForm, "frmSearch", acSaveYes
 
FinalHandler:
    Set t = Nothing
    Exit Sub
 
ErrorHandler:
    MsgBox Err.Description, vbExclamation
    Resume FinalHandler
End Sub

Open the module of frmSearch and add:

Option Compare Database
Option Explicit
 
Private Sub Form_Load()
    On Error GoTo ErrorHandler
 
    ShowCustomers ""
    Me!Timer1.Object.Tasks("count").Start
    Me!Timer1.Object.Tasks("count").Trigger   ' count once at once, then every 30 seconds
 
FinalHandler:
    Exit Sub
 
ErrorHandler:
    MsgBox Err.Description, vbExclamation
    Resume FinalHandler
End Sub
 
Private Sub txtSearch_Change()
    On Error GoTo ErrorHandler
 
    ' Every key press starts the 400 ms again; only the last one leads to a search.
    Me!Timer1.Object.Tasks("search").Tag = Me!txtSearch.Text
    Me!Timer1.Object.Tasks("search").Restart
 
FinalHandler:
    Exit Sub
 
ErrorHandler:
    MsgBox Err.Description, vbExclamation
    Resume FinalHandler
End Sub
 
Private Sub Timer1_TaskDue(ByVal Key As String, ByVal RunId As Long, ByVal Tag As Variant, Cancel As Integer)
    On Error GoTo ErrorHandler
 
    Select Case Key
        Case "search": ShowCustomers Nz(Tag, "")
        Case "count": Me!txtOpen = DCount("*", "tblSessions", "IsOpen = True")
    End Select
 
FinalHandler:
    Exit Sub
 
ErrorHandler:
    ' A repeating event: note the error instead of showing a dialog every 30 seconds.
    Me!txtOpen = "?"
    Resume FinalHandler
End Sub
 
Private Sub ShowCustomers(ByVal text As String)
    Me!lstCustomers.RowSource = "SELECT ID, Name FROM tblCustomers WHERE Name LIKE '" & Replace(text, "'", "''") & "*' ORDER BY Name"
End Sub

Open frmSearch in Form view and type a name quickly into txtSearch: the list doesn't change while you type, and it filters once, 400 ms after your last keystroke. Set an open session to Yes in tblSessions and wait: txtOpen shows the new number within 30 seconds.

The list filters once per pause in typing, and the counter refreshes every 30 seconds, even while a message box of the form is open (the next run waits until the procedure has ended; it isn't lost). When the form closes, both tasks end, and no event arrives afterward.

• How to: Send many requests to a web service at its pace
• WklTimerList overview: all rules for delays, intervals, overlaps and retries.