Dropdown and filter as typing in a combo box in MS Access

[mshakeelattari]

I have a bound combo box control named Publisher_ID and Row Source:

SELECT Publishers.Publisher_ID, Publishers.PublisherName FROM Publishers;

(2 columns, widths: 0cm,2cm). I want it to dropdown by itself as soon as it is reached by pressing Tab keys, so I have a code in On Enter event:

Private Sub Publisher_ID_Enter()
    Me.Publisher_ID.Dropdown
End Sub

In the On Change event,

Private Sub Publisher_ID_Change()
    On Error GoTo ErrorHandler
    Dim searchText As String
    Dim sql As String
    If isFiltering Then Exit Sub
    searchText = Nz(Me.Publisher_ID.Text, "")
    If Len(searchText) > 0 Then
        isFiltering = True
        sql = "SELECT Publishers.Publisher_ID, Publishers.PublisherName FROM Publishers WHERE PublisherName LIKE '*" & searchText & "*' ORDER BY PublisherName"
        Me.Publisher_ID.RowSource = sql
        If Me.Publisher_ID.ListCount > 0 Then
            Me.Publisher_ID.Dropdown
        End If
        isFiltering = False
    Else
        Me.Publisher_ID.RowSource = "SELECT Publishers.Publisher_ID, Publishers.PublisherName FROM Publishers"
        Me.Publisher_ID.Dropdown
        isFiltering = False
    End If
    Exit Sub
ErrorHandler:
    isFiltering = False
End Sub

The Problem is that even when I reach the combo by pressing the Tab key, it opens for a second and then is closed. Also, it continues to clicker while typing in the combo. This has been a headache for me for several days. Can you help me with this? AutoExpand has been set to both yes and no, but to no effect.

[twinnyfo]

Mshakeelattari,

Rather than using the OnEnter event, you should probably use the OnFocus event, so that whenever the combo box control receives the focus it drops down.

Additionally, there should be no need for your code on the OnChange event. Unless you have literally hundreds of thousands of publishers, there is no need to continually search as you type. This function is automatically built into the ComboBox. Once you start typing, the ComboBox will find the first match and display that result. If, for example, you have 10 entries, all of which begin with the word, “World” once the user types “World” they can tap the drop down arrow and see those entires in order.

This could be one of those cases in which you are “over thinking the problem” unless I am unaware of something completely different that you are trying to do.

Hope that hepps!

[NeoPa]

All good advice I would say :-)

There is a slight difference between what you seem to be doing & the natural behaviour of of the ComboBox Control anyway. You're doing a search where the typed content can appear anywhere, while the default behaviour merely matches that text at the start of an entry. Maybe important, but maybe not. Only you can know.

You can already see though, that every time you set the properties of the ComboBox between letters that you type, it resets itself (as essentially you've told it to do) which can make using it somewhat awkward. Not really how it's designed to work & I wouldn't want that in one of my systems, but, again, only you can know what's important for your system.

[mshakeelattari]

It means I should only rely on matches that start with the same name in the combo box, and cannot search for words in between! This code is working all the best, but the only problem is that the combo box closes immediately after dropping down. Is there any solution for this problem? This issue was not there previously for many days and has started appearing a few days ago.