CODEWITHSUNDEEP
ms-accessvba
Jap
Jap

Reputation: 671

Prevent combo box selection to change text

I am building a search as you type combo box. The search and update uses the change event. 

Private Sub ItemName_Change()

Dim strText, strFind, strSQL, strSelect, strWhere, strOrderBy As String
strText = Me.ItemName.Text

strSelect = "SELECT DISTINCT [ItemName] FROM ItemsMaster "
strWhere = "WHERE type = 'Sold Goods' "
strOrderBy = " ORDER BY [ItemName];"
If (Len(Trim(strText)) > 0) Then
    'Show the list with only those items containing the types letters
    strFind = "ItemsMaster.ItemName Like '"
    For i = 1 To Len(Trim(strText))
        If (Right(strFind, 1) = "*") Then
            strFind = Left(strFind, Len(strFind) - 1)
        End If
        strFind = strFind & "*" & Mid(strText, i, 1) & "*"
    Next

    strFind = strFind & "'"
    strSQL = strSelect & strWhere & "AND " & strFind & strOrderBy

    Me.ItemName.RowSource = strSQL

Else
    strSQL = strSelect & strWhere & strOrderBy
    Me.ItemName.RowSource = strSQL
End If

Me.ItemName.Dropdown


End Sub

But When I try to select the items from the drop-down list, it updates the text and fires the change event. Is there any way the user can scroll through the list without combo box changing the text in the text box?

Upvotes: 2

Views: 262

Answers (1)

John Bingham
John Bingham

Reputation: 2006

You could probably overcome this problem by testing whether the listindex is greater than -1 at the start of your event handler, and exiting if it is, eg:

Private Sub ItemName_Change()
    If ItemName.ListIndex > -1 Then
        Exit sub
    End if

    ...
End Sub

I think though that there is a good chance that you would be better off not using a combobox at all, but mimicking it's functionality with (a) a textbox (into which the text is entered, and which fires the change event you've written) & (b) a listbox, which displays the results of the search - then when the user selects an item, this action is disassociated from the entry control.

Upvotes: 1

Related Questions