Typing in a Combobox to filter dropdown as you type (1 Viewer)

[abenitez77]

I have a form in ms access 2016 with a subform in datagrid view. In that datagrid, I have a column that is a combobox and I am filtering the values as you type. It works for the first selection, but when I make my selection and I want to either go to the next record and make another selection or change the selection I made in my current row, all the values disappear and are blank. This is my code:

Option Compare Database
Private Const RecordSQL As String = "SELECT ConID, ItemNo, Description FROM Consignment"

Public Sub FilterComboAsYouType(combo As ComboBox, defaultSQL As String, lookupField As String)
Dim strSQL As String
If Len(combo.Text) > 0 Then
strSQL = defaultSQL & " WHERE " & lookupField & " LIKE '*" & combo.Text & "*'"
Else
strSQL = defaultSQL 'This is the default row source of combo box
End If
combo.RowSource = strSQL
combo.Dropdown
End Sub


Private Sub Select_Item_AfterUpdate()
'reset dropdown list

Select_Item.RowSource = RecordSQL
Select_Item.Requery
Select_Item.Dropdown
Select_Item.SetFocus


End Sub

Private Sub Select_Item_Change()

FilterComboAsYouType Me.Select_Item, RecordSQL, "Description"


End Sub

 

[theDBguy]

Hi. This is normal behavior if you're using a Continuous Form.

 

[MajP]

See discussion here.

enabling cascading comboboxes in datasheet or continuous form

Having been asked about this recently, I reinvented some code I developed to work with datasheets and which also works with continuous and single forms. The attachment was developed in 2010 but saved as 2003. The db demonstrated two techniques for using cascading comboboxes. Technique 1 is...

www.access-programmers.co.uk

Semi Unique Combox Record Source on Continuous Form

Take a look at the demo on the right. This uses the trick of overlaying a textbox on the combobox. This gets rid of fields going blank. In the current format if you key down it goes blank as well as the scroll

www.access-programmers.co.uk

 

[abenitez77]

See discussion here.

enabling cascading comboboxes in datasheet or continuous form

Having been asked about this recently, I reinvented some code I developed to work with datasheets and which also works with continuous and single forms. The attachment was developed in 2010 but saved as 2003. The db demonstrated two techniques for using cascading comboboxes. Technique 1 is...

www.access-programmers.co.uk

Semi Unique Combox Record Source on Continuous Form

Take a look at the demo on the right. This uses the trick of overlaying a textbox on the combobox. This gets rid of fields going blank. In the current format if you key down it goes blank as well as the scroll

www.access-programmers.co.uk

Thanks! this worked and helped me a lot.

 

[abenitez77]

See discussion here.

enabling cascading comboboxes in datasheet or continuous form

Having been asked about this recently, I reinvented some code I developed to work with datasheets and which also works with continuous and single forms. The attachment was developed in 2010 but saved as 2003. The db demonstrated two techniques for using cascading comboboxes. Technique 1 is...

www.access-programmers.co.uk

Semi Unique Combox Record Source on Continuous Form

Take a look at the demo on the right. This uses the trick of overlaying a textbox on the combobox. This gets rid of fields going blank. In the current format if you key down it goes blank as well as the scroll

www.access-programmers.co.uk

It is working for me with 1 issue. The first time I type a value it works fine. On the second attempt on a new row, I type a value and nothing comes up (it is blank) but when i scroll up and down with my mouse it refreshes the dropdown and I can see the values. How can i get it to refresh? I tried a requery but it says i have to save the value first.

Public Sub FilterComboAsYouType(Filter As Boolean)
    Dim strSQL As String
    
    strSQL = RecordSQL    'This is the default row source of combo box
    Me.Select_Item.RowSource = strSQL
    
    DoEvents
    If Filter Then
        DoEvents
        strSQL = RecordSQL & " WHERE Description LIKE '*" & Me.Select_Item.Text & "*'"
        Me.Select_Item.RowSource = strSQL
        'Me.Select_Item.Requery
        Me.Select_Item.Dropdown
        
    End If
End Sub

 

[MajP]

This kind of behavior is why I do the trick with the overlying text box. See the example in second link.