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

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

Re: Filter Combo Box as I Type

Show all headers | View raw

On 19/10/2015 17:22:42, Patrick Finucane wrote:
> On Monday, October 19, 2015 at 10:43:07 AM UTC-4, Phil wrote:
>> 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
> 
> Phil, I can't imagine submitting any of my end users to the torture of
> 131,000, 64k, or 32K, or 16K or even 8K records in a combobox. I know you
> are being nice but I would not attempt to provide a terrible solution to
> an end user.
> 
> The only thing that makes sense is presenting a form to filter.
> 

Basically you can do the same thing with a SubForm by setting the
Sub Form.Filter to the SQLStg. I certainly have a version that progressively
filters the Sub form as you type each letter in.

It's up to magmike what he wants.

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