Prevent down arrow or up arrow from updating a combo box in MS Access

Question

Often when a user sees results from search string results in a combobox dropdown, a natural response is to arrow down to a result they pinpoint.

But, if my users click the down arrow, it will update the combobox, thereby not jumping to the record as expected. I'd like to limit the combobox to sense an update ONLY if the user hits "Tab", "Enter" or a Mouse click.

The combo box search that I've written otherwise works perfectly.

Here's my code in the On Key Up event

Private Sub cboGCPC_Search_KeyUp(KeyCode As Integer, Shift As Integer)
  Dim strSQL As String

        strSQL = "SELECT * " _
               & "FROM qryGCPC_Search " _
               & "WHERE [DocumentNumber] Like '*" & Me.cboGCPC_Search.text & "*' OR [Description] Like '*" & Me.cboGCPC_Search.text & "*' OR [Vendor] Like '*" & Me.cboGCPC_Search.text & "*' OR [Receiver] Like '*" & Me.cboGCPC_Search.text & "*';"


 Debug.Print strSQL

        Me.cboGCPC_Search.RowSource = strSQL
        Me.cboGCPC_Search.Dropdown

End Sub

I think i know what you're getting at but it's a little unclear what you mean by 'updating'. "if my users click the down arrow, it will update the combobox," "limit the combobox to sense an update ONLY...". the cbo is updating with every keystroke isn't it? What happens on "Tab", "Enter" or a Mouse click? — wazz
To clarify, I'm referring to the keystroke that would cause "lose focus" of the combo box. As soon as the focus is lost, the combo box is considered updated. This in turn fires the AfterUpdate event. While the user is typing in the "key up event" the combo box is not yet in stone. But when the user clicks the down arrow, (or enter or tab, or clicks the mouse) the combo box IS considered updated because it loses focus of the combo box. There must be a way to trap the down arrow key or the up arrow key from causing the AfterUpdate event to fire as the user scans with the up or down keys. — plateriot

wazz wazz · Accepted Answer · 2017-06-16T03:32:01

I think you're looking for something like this:

Private Sub cboGCPC_Search_KeyUp(KeyCode As Integer, Shift As Integer)
    If KeyCode = 38 Then
        KeyCode = 0
        'Me.Combo0.Dropdown 'try this too, if necessary.
        'Exit Sub           'try this too, if necessary.
    End If
    If KeyCode = 40 Then
        KeyCode = 0
        'Me.Combo0.Dropdown 'try this too, if necessary.
        'Exit Sub           'try this too, if necessary.
    End If
End Sub

Prevent down arrow or up arrow from updating a combo box in MS Access

2 Answers