browse combobox list with arrow keys without changing listindex

[delikedi]

when we drop-down a combobox, we have two ways of selecting an item:

1-point with mouse and click
2- navigate to the desired item with arrow keys and press enter or tab

in 1, while mouse pointer moves from item to item, the "listindex" property does not change. therefore "text" property does not change either. the mouse pointer only highlights items. listindex only changes when an item is "selected".

in 2, listindex changes everytime we press down arrow key or up arrow key. item is not just highlighted, it is also selected, which causes the .text property to change too.

is there any way to use arrow keys to just highlight, not select, combo items?

because,
this difference is causing me a huge problem: I'm trying to adopt a code that filters the contents of comboboxes as the user types text. The code reads the text and returns the records that are "like *text*". When a user types a few characters and the dropdown list is filtered down to a few items, the user attempts to press down arrow key, which selects the first item in the combo, which in turn changes the .text property, which in turn filters the combo down to just one item, which is that item.

 

[delikedi]

I found a solution. It does not solve the problem I presented, it just prevents it. The idea is that the code to filter the combo row source should only be triggered when certain keys are pressed. If we keep the arrow keys out of the range of those, then unintended filtering will not occur.

The code I came up with, which is a function that I insert into the KeyUp event of lookup combos, is as follows:

Public Function CbxFiltrele(ByRef cbxKaynak As ComboBox, ByRef KeyKodu As Integer) As Byte
'essentially gives self filtering capability to a combobox

'Filters a combobox's recordset down to items that contain the typed
'text, and returns the filtered recordset.
'The function assumes that the combo is a lookup combo, with a two-field recordset
'where the first field is the hidden ID field and the second is the displayed,
'text-based one.
'Similar to the auto-expand capability of standard comboboxes, except here the
'typed text is searched anywhere inside the item as opposed to just the beginning,
'and the result set is a filtered one with just a few items

'Hata tablosu icin
Dim IslemAdi As String
IslemAdi = "Public Function CbxFiltrele"
On Error GoTo Erhan

Dim dbsFinnos As DAO.Database
Dim rstKaynak As DAO.Recordset
Dim rstFiltreli As DAO.Recordset
Dim strRowSourceName As String
Dim strWhere As String
Dim strMetin As String
Dim blnSartlar As Boolean
    
blnSartlar = (KeyKodu = 8) 'backspace
blnSartlar = blnSartlar Or (KeyKodu = 32) 'space
blnSartlar = blnSartlar Or (KeyKodu >= 65 And KeyKodu <= 90) 'a-z
blnSartlar = blnSartlar Or (KeyKodu >= 48 And KeyKodu <= 57) '0-9
blnSartlar = blnSartlar Or (KeyKodu >= 96 And KeyKodu <= 105) '0-9 numpad
'additional non-english key codes are removed here

If blnSartlar = True Then
    Set dbsFinnos = Application.CurrentDb
    If cbxKaynak Is Nothing Then Set cbxKaynak = Screen.ActiveControl
    
    If cbxKaynak.RowSourceType = "Table/Query" Then
        strRowSourceName = cbxKaynak.RowSource
        Set rstKaynak = dbsFinnos.OpenRecordset(strRowSourceName, dbOpenSnapshot, dbReadOnly)
    Else
        MsgBox "Combo source must be of type table/query."
        Exit Function
    End If
    
    strMetin = cbxKaynak.Text
    
    If Not Nz(strMetin, "") = "" Then
        strWhere = Chr(34) & Chr(42) & strMetin & Chr(42) & Chr(34)
         'the field to filter on is almost always fields(1) in my  combos; you may need to modify the following line if that is not the  case with your code.
        rstKaynak.Filter = "[" & rstKaynak.Fields(1).Name & "]" & " like " & strWhere
        Set rstFiltreli = rstKaynak.OpenRecordset(dbOpenSnapshot, dbReadOnly)
        Dim lngKayitAdedi As Long
        lngKayitAdedi = RecordSetKayitAdediniBul(rstFiltreli)
        Select Case lngKayitAdedi
            Case Is >= 1
                Set cbxKaynak.Recordset = rstFiltreli
                cbxKaynak.Dropdown
            Case Else 'no records found, so restore the original recordset
                Set cbxKaynak.Recordset = rstKaynak
        End Select
    End If
End If

Cikis:
Set dbsFinnos = Nothing
Set rstKaynak = Nothing
Set rstFiltreli = Nothing
Exit Function


Erhan: 
Call HataDegerlendirme(IslemAdi, Err.Number, Err.Source, Err.Description)
Resume Cikis
End Function

complementary code that finds the exact number of records in a recordset:

Public Function RecordSetKayitAdediniBul(ByRef rstRecordset As DAO.Recordset) As Long

'Hata tablosu icin
Dim IslemAdi As String
IslemAdi = "Public Function RecordSetKayitAdediniBul"
On Error GoTo Erhan

Dim rstKayitlar As DAO.Recordset
Set rstKayitlar = rstRecordset.Clone

   rstKayitlar.MoveLast 'so all records are accessed
   RecordSetKayitAdediniBul = rstKayitlar.RecordCount
Set rstKayitlar = Nothing

Cikis:
Exit Function

Erhan:

If Err.Number = 3021 Then
    Resume Next
Else
    Call HataDegerlendirme(IslemAdi, Err.Number, Err.Source, Err.Description)
    Resume Cikis:
End If

End Function

 

[raziel3]

I need some help understanding your code and how to use it. I have a similar problem using combo boxes. I want my combo box to filter its rowsource from partial matches. The problem I am encountering is when I use the arrow keys, it fills the combo box with the first item selected and removes all the other items from the list.

 

[arnelgp]

what functionality do you need?
"filter your data" as you type on the combo?
this is but another example, mr.majp has another version.

 

[MajP]

Find as we typed

Part of my form is to enter customer's info based on Combo Box. My combo is working if we type the beginning part of the customer name. How can we make it even 3 characters in the middle of the name, can auto find the closest names?. If any sample of database, could you uploaded it here please...

www.access-programmers.co.uk

This has the arrow key functionality.

 

[raziel3]

Using a combo box, I want to be able to filter the items in the drop-down list. I've gotten this far using this:

Const conSuburbMin = 3
        If Len(Me.txtUPC.Text) < conSuburbMin Then     ' If first n chars are the same as previously, do nothing.
            Me.txtUPC.RowSource = ""    'Remove the RowSource
        Else
            Me.txtUPC.RowSource = "SELECT UPC,PNAME FROM Product WHERE PNAME LIKE '*" & Me.txtUPC.Text & "*'" & _    'New RowSource
                " ORDER BY PNAME"
            Me.txtUPC.Dropdown
        End If

in the OnChange event of my combo box. The problem I am having is whenever I use the arrow keys to cycle the list items the first item selected fills the combo box causing the rest of the items to be removed.

I was looking at the samples that @MajP did. Looks exactly like what I need. Will go through it and see if I can make it work with my combo box.

 

[MajP]

My code uses a class module that works on any combo that uses a rowsource.

 

[raziel3]

My code uses a class module that works on any combo that uses a rowsource.

I read on the other post that on a continuous form it will blank out the previous record. How do you get around that.

I inserted the class module
Placed this the at the top of the form's vba

Public faytProducts As New FindAsYouTypeCombo

I only want to use the cmbProducts combo box format
Placed this on the form's OnLoad Event

faytProducts.InitalizeFilterCombo Me.cmbProducts, "ProductName", anywhereinstring, True

and placed this in the Combo Box AfterUpdate event

GoToRecord cmbProducts

I'm getting an error with this Me.cmbProducts method or member data not found. Do I have to change this "Me.cmbProducts" to my form's combo box reference?

 

[MajP]

Yes you need to reference your combo

 

[MajP]

Find as you typed

In my form here attached I have 2 combo box, to fill the bound fields. Right now the source of the combo is table. This is the control source of the field: SELECT DISTINCT Inventory.ItemCode, Inventory.[IUnitPrice], Inventory.Description FROM Inventory ORDER BY Inventory.Description; How can I...

www.access-programmers.co.uk

Example for continuous form.

 

[raziel3]

@MajP this code is amazing!! I'm still trying to understand it but it works great.