Why does Access VBA dropdown method not work?

Question

I'm using a text box to filter a combo box list in Access 2013. I put the filter code in the Form_Timer sub so as to give users time to type the entire filter string before applying the filter, and I invoke the timer from the text box Change sub. It works great except for one thing: I want the combo box list to drop down and display results, and it just won't work. However I put the exact same line of code in the GotFocus sub for the combo box, and that line works perfectly.

I also tried executing the filter code within the Change sub, just in case there was some weirdness regarding Form_Timer execution. Same result. Here is the code:

Private Sub cboCENamesMain_GotFocus()

    Me.cboCENamesMain.Dropdown '<---This line works perfectly.

End Sub

Private Sub Form_Timer()

    Dim strSQL As String

    Me.TimerInterval = 0
    Me.txtFilter.Value = Me.txtFilter.Text
    Me.cboCENamesMain.SetFocus

    strSQL = ""
    strSQL = strSQL & "Select DISTINCT [CE ID] "
    strSQL = strSQL & "From [tblMyTable] "
    If Len(Me.txtFilter) > 0 Then
        strSQL = strSQL & "Where [CE ID] Like ""*" & Me.txtFilter & "*"" "
    End If
    strSQL = strSQL & "Order By [CE ID];"

    Me.cboCENamesMain.RowSource = strSQL

    Me.cboCENamesMain.Dropdown '<---This line doesn't do what it's supposed to.
    Me.txtFilter.SetFocus
    Me.txtFilter.SelStart = Len(Me.txtFilter.Text)
    Me.txtFilter.SelLength = 0

End Sub

Private Sub txtFilter_Change()

    If Len(Me.txtFilter.Text) = 0 _
    Or Len(Me.txtFilter.Text) > 2 Then
        Me.TimerInterval = 500
    End If

End Sub

I could use a list box instead of a combo box to allow users to see the results of their filter typing, but that would seriously detract from my form design. I have searched on Google and on StackOverflow, and have not found anybody else discussing this issue. Any ideas?

Your code makes no sense to me. You can't have a combo box drop down and make a selection in a text field at the same time, because both actions require focus on the control. The line probably works fine, but the line immediately after it collapses the dropdown menu again. — Erik A
Thanks Erik - you're right, of course, and that should have occurred to me. I removed the code following the dropdown line, and then it works. It's just that this requires users to type their filter text pretty quickly - I'm afraid two finger typists might find this frustrating. I know I pulled off this sort of thing successfully in the past, but don't remember exactly how I did it - I'll think about it some more. — RichardCoeurNoir
Okay, now I feel pretty silly. I ran the timer from the combo box Change sub instead of the text box, and it works just the way I wanted it to. No text box required - that's only needed for filtering a list box. I just needed to change the combo box auto expand property to No to avoid having Access fill in with a matching item. — RichardCoeurNoir
With the Me.cboCENamesMain.Dropdown call, are you looking to force MS Access to populate the list portion of the combobox? Perhaps Me.cboCENamesMain.Requery is a better option? — GraemeR
Maybe dropdown isn't working because you've moved the focus. Move the me.txtFilter stuff to the AfterUpdate of the cboCeNamesMain. (And then you won't need that gotfocus call) — geeFlo

RichardCoeurNoir RichardCoeurNoir · Accepted Answer · 2018-12-11T21:22:47

Here is my final code. This works just the way I want. The user types a few characters, and the timer automatically filters the combo box list down to items containing the typed string.

StackOverflow is a great place to file stuff like this - going from job to job it will save some time reinventing the wheel.

The extensive comments in the code are for the analysts who will need to maintain the applications after my contract ends. Neither of them has ever written VBA.

Private Sub cboCENamesMain_Change()

    ' Ordinarily the AfterUpdate event would automatically fire off the Change event.
    ' We set a flag to avoid that - otherwise the list would be filtered to only the selected record.
    If booCancelChange = False Then
        ' Don't bother filtering the combo box list unless the filter text is null or longer than
        ' 1 character.
        If Len(Me.cboCENamesMain.Text) <> 1 Then
            ' Set the Form_Timer event to fire off in 0.3 second to give them time to type a few characters.
            Me.TimerInterval = 300
        End If
    Else
        ' Reset the flag, otherwise the Change code would stop working after the first record selection.
        booCancelChange = False
    End If

End Sub

Private Sub Form_Timer()

    Dim strSQL As String

    ' Reset the timer to not fire off, so that it won't keep running without a change
    ' in the combo box.
    Me.TimerInterval = 0

    ' If they have tabbed out of the combo box after selecting an item, we don't want to
    ' do this.  It's unnecessary and it throws errors from references to the control's
    ' properties when focus is no longer on the control.
    If Screen.ActiveControl.Name = Me.cboCENamesMain.Name Then
        ' Create a SQL filter for the combo box using the entered text.
        strSQL = ""
        strSQL = strSQL & "Select DISTINCT [CE ID] "
        strSQL = strSQL & "From [Covered Entities] "
        If Len(Me.cboCENamesMain.Text) > 0 Then
            strSQL = strSQL & "Where [CE ID] Like ""*" & Me.cboCENamesMain.Text & "*"" "
        End If
        strSQL = strSQL & "Order By [CE ID];"

        ' Apply the filter.
        Me.cboCENamesMain.RowSource = strSQL
        Me.txtRowCount = Me.cboCENamesMain.ListCount
        ' Drop down the combo list so they can see the results of their filter text.
        Me.cboCENamesMain.Dropdown
    End If

End Sub

Why does Access VBA dropdown method not work?

1 Answers