Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #11219

Re: Filter Combo Box as I Type

Show all headers | View raw

On 19/10/2015 14:52:04, Patrick Finucane wrote:
> On Sunday, October 18, 2015 at 3:44:53 PM UTC-4, magmike wrote:
>> Currently I have a ComboBox fed by a query that shows 131,000 records. As
> I type, it skips to the matching record in the ComboBox, but it also seems
> limited to the first 65000 records and will not display more than this in
> > the combo box.
>>
> > the combo box.
>> What I would rather happen, anyway, is to live-filter the query that feed
> the combo box. s the combo box based on what I am typing into it, which
> would greatly redu ce the number of records shown in the combo box, the
> more I type. Any ideas how to do this, including a different approach,
> altogether, is welcome.
> 
>>
>> Once an option is selected, an AfterUpdate event populates the form with
> the chosen record.
>>
>> TIA
>> magmike
> 
> 131,000 combo box rows?  Torture.  Pure torture for the end user.
> 

It's not perfect, but if you are prepared to accept a list box this isn't too
bad.

I have a List box List1 and a Text Box TxtFilter which sits on the top line
of the List box. There is some sort of table with MemSurname & MemberID as a
partial source for the list box.

Here is the code

Option Compare Database
Option Explicit


Private Sub Form_Open(Cancel As Integer)

    TxtFilter = ""                  ' Clear the Filter
    List1.RowSource = ""            ' Clear the List Box
    TxtFilter.SetFocus
    
End Sub

Private Sub List1_AfterUpdate()

    TxtFilter.SetFocus
    
End Sub

Private Sub TxtFilter_Change()

    Dim SQLStg As String

    Me.TxtFilter.SelStart = Len(Me.TxtFilter.Text)
    
    SQLStg = "SELECT Member.MemSurName, Member.MemberID From Member "
SQLStg LStg = SQLStg & "WHERE MemSurName like " & Chr$(34) & TxtFilter.Text &
"*" & Chr$(34)     SQLStg = SQLStg & " ORDER BY Member.MemSurName;"

    List1.RowSource = SQLStg
    List1.Requery
    TxtFilter.SetFocus              ' Go back to the list box
    
End Sub

I have no way of knowing whether this will work with 131,000 records,
but theoretically the list box should only have records starting with the
letters in the (invisible) TxtFilter Text Box

Phil

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Filter Combo Box as I Type magmike <magmike7@yahoo.com> - 2015-10-18 12:44 -0700
  Re: Filter Combo Box as I Type Larry Linson <larry.linson@verizon.net> - 2015-10-18 23:11 -0500
    Re: Filter Combo Box as I Type "Ron Paii" <None@none.com> - 2015-10-19 07:04 -0500
  Re: Filter Combo Box as I Type Patrick Finucane <patrickfinucanetx@gmail.com> - 2015-10-19 06:52 -0700
    Re: Filter Combo Box as I Type "Phil" <phil@stantonfamily.co.uk> - 2015-10-19 15:42 +0100
      Re: Filter Combo Box as I Type Patrick Finucane <patrickfinucanetx@gmail.com> - 2015-10-19 09:22 -0700
        Re: Filter Combo Box as I Type "Phil" <phil@stantonfamily.co.uk> - 2015-10-19 22:22 +0100
          Re: Filter Combo Box as I Type Patrick Finucane <patrickfinucanetx@gmail.com> - 2015-10-20 05:55 -0700
          Re: Filter Combo Box as I Type magmike <magmike7@yahoo.com> - 2015-10-22 20:38 -0700
            Re: Filter Combo Box as I Type "Phil" <phil@stantonfamily.co.uk> - 2015-10-23 08:33 +0100
              Re: Filter Combo Box as I Type magmike <magmike7@yahoo.com> - 2015-10-23 17:14 -0700
                Re: Filter Combo Box as I Type "Phil" <phil@stantonfamily.co.uk> - 2015-10-24 09:26 +0100
            Re: Filter Combo Box as I Type Patrick Finucane <patrickfinucanetx@gmail.com> - 2015-10-23 06:00 -0700
              Re: Filter Combo Box as I Type magmike <magmike7@yahoo.com> - 2015-10-23 17:13 -0700
                Re: Filter Combo Box as I Type Patrick Finucane <patrickfinucanetx@gmail.com> - 2015-10-26 06:29 -0700
              Re: Filter Combo Box as I Type magmike <magmike7@yahoo.com> - 2015-10-24 16:19 -0700
  Re: Filter Combo Box as I Type magmike <magmike7@yahoo.com> - 2015-10-24 16:32 -0700
    Re: Filter Combo Box as I Type "Phil" <phil@stantonfamily.co.uk> - 2015-10-25 01:50 +0000
  Re: Filter Combo Box as I Type magmike <magmike7@yahoo.com> - 2015-10-24 19:18 -0700
    Re: Filter Combo Box as I Type magmike <magmike7@yahoo.com> - 2015-10-24 19:25 -0700
      Re: Filter Combo Box as I Type "Phil" <phil@stantonfamily.co.uk> - 2015-10-25 09:50 +0000
        Re: Filter Combo Box as I Type magmike <magmike7@yahoo.com> - 2015-10-25 08:46 -0700

csiph-web