Combobox search while you type

[DavidAustin]

Hi all,

In February I had a similar query on the following thread: https://bytes.com/topic/access/answe...-selected-item

The answer given works fine but is a little clunky - e.g. when the user types multiple keys in a row it executes the change event multiple times so the typing is behind on screen. Is there anyway to make it so that it only queries once when the user has stopped typing rather than every time? I would guess it would be some timer event but I've never used them before and I'm not sure how to get it to work!

[zmbd]

Open your form in design view.
Properties
Form
Events
[On Timer] event
This is where you will place your code
This will trigger every time the timer counts down from the time the form opens; thus you need a flag - tempvars collection comes to mind. I have in the past use the form Tag property that I set to 0 on load and 1 in the other events. In the timer event itself I reset the tag to zero once the action has taken place.

Private Sub Form_Timer()
    If Me.Tag Then
        MsgBox "timer event tag is" & Me.Tag
        Me.Tag = 0
    End If
End Sub

[Timer Interval] == 30seconds ==> 30000
thus 5seconds would be 5000

Now in the [On Change] events you need to have for say a 10 second delay:

Me.TimerInterval = 10000
me.tag = 1

Now every time the event fires, it resets the interval and sets the flag. If the interval expires before the next event resets then [on timer] event fires, runs its course, and clears the flag.

Move all of your other query code into either the [On Timer] event or as a private procedure(s) within the form or as a standard module and then call the procedure from the event, either way.

Hope that's clear - I've only glanced at jforbes code so you'll have to play with things a bit to get the timing and key/mouse-trapping just right.


This is along the same idea as the idle out timer found here:
home > topics > microsoft access / vba > insights > inactivity detection in access

[jforbes]

This seemed like a really bad idea, but I gave it a whirl anyway. I reworked my test version of this and the following is what I came up with. I was pleasantly surprised as it seems to work pretty well:

Option Compare Database
Option Explicit

Private nDelay As Long

Private Sub Form_Load()
    ' Setup KeyPress Delay
    nDelay = 1000
End Sub


' Set and Reset the Form Timer as needed
Private Sub setTimer()
    Me.TimerInterval = nDelay
End Sub
Private Sub resetTimer()
    Me.TimerInterval = 0
End Sub

  
' ComboBox Events
Private Sub cboPeople_GotFocus()
    ' Reset the Dropdown List
    Dim sSQL As String
    sSQL = sSQL & "SELECT FullName "
    sSQL = sSQL & " FROM People "
    If Me.cboPeople.RowSource <> sSQL Then Me.cboPeople.RowSource = sSQL
End Sub
Private Sub cboPeople_LostFocus()
    Call resetTimer
End Sub
Private Sub cboPeople_Change()
    Call setTimer
End Sub
Private Sub cboPeople_KeyDown(KeyCode As Integer, Shift As Integer)
    Select Case KeyCode
        Case vbKeyDown, vbKeyUp
            Call setTimer
        Case Else
            Call resetTimer
    End Select
End Sub


Private Sub Form_Timer()
    Dim sSQL As String
    Dim sNewLookup As String
    
    ' Refresh the Dropdown List for the Current Dropdown Text
    sNewLookup = Nz(Me.cboPeople.Text, "")
    sSQL = sSQL & "SELECT FullName "
    sSQL = sSQL & " FROM People "
    If Len(sNewLookup) <> 0 Then
        sSQL = sSQL & " WHERE FullName LIKE '*" & sNewLookup & "*'"
    Else
        SendKeys "{F4}"
    End If
    Me.cboPeople.RowSource = sSQL
    Me.cboPeople.Dropdown
    Call resetTimer    
End Sub

[zmbd]

:)

I use it as a simple idle check for one of my most often left open databases that I've never bothered to split into b/f-ends. I set the event interval to about 15 minutes and the event reset is in both the form's on_current and after_update events as these are one-off entries that happen once or twice a month. On trigger, I force save the data and close the database.