Groups > comp.databases.ms-access > #11214 > unrolled thread

Filter Combo Box as I Type

Started by	magmike <magmike7@yahoo.com>
First post	2015-10-18 12:44 -0700
Last post	2015-10-25 08:46 -0700
Articles	20 on this page of 22 — 5 participants

Back to article view | Back to comp.databases.ms-access

  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

Page 1 of 2 [1] 2 Next page →

#11214 — Filter Combo Box as I Type

From	magmike <magmike7@yahoo.com>
Date	2015-10-18 12:44 -0700
Subject	Filter Combo Box as I Type
Message-ID	<49667261-5703-42b9-9059-ea7e59fdf2fc@googlegroups.com>

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.

What I would rather happen, anyway, is to live-filter the query that feeds the combo box based on what I am typing into it, which would greatly reduce 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

[toc] | [next] | [standalone]

#11215

From	Larry Linson <larry.linson@verizon.net>
Date	2015-10-18 23:11 -0500
Message-ID	<11r82bln2u6gdnno2jskpe9v7kq914gidq@4ax.com>
In reply to	#11214

On Sun, 18 Oct 2015 12:44:46 -0700 (PDT), magmike <magmike7@yahoo.com>
wrote:

 >Currently I have a ComboBox fed by a query that shows 131,000
records. As I type,...

Forcing a user to scroll through 131,00 records is not user-friendly
interface design, even with tricks as you propose. There are many ways
to let the user select, on categories, or groupings, etc. and you
should consider using those...

Yes, Combo Boxes, by design, limit the number of records (probably
that limit is far too high for an effective user interface, but better
something than nothing may have been the designers' thought).

 Larry Linson
 Access MVP 2003 -2014

[toc] | [prev] | [next] | [standalone]

#11216

From	"Ron Paii" <None@none.com>
Date	2015-10-19 07:04 -0500
Message-ID	<n02m52$emb$1@dont-email.me>
In reply to	#11215


"Larry Linson" <larry.linson@verizon.net> wrote in message 
news:11r82bln2u6gdnno2jskpe9v7kq914gidq@4ax.com...
> On Sun, 18 Oct 2015 12:44:46 -0700 (PDT), magmike <magmike7@yahoo.com>
> wrote:
>
> >Currently I have a ComboBox fed by a query that shows 131,000
> records. As I type,...
>
> Forcing a user to scroll through 131,00 records is not user-friendly
> interface design, even with tricks as you propose. There are many ways
> to let the user select, on categories, or groupings, etc. and you
> should consider using those...
>
> Yes, Combo Boxes, by design, limit the number of records (probably
> that limit is far too high for an effective user interface, but better
> something than nothing may have been the designers' thought).
>
> Larry Linson
> Access MVP 2003 -2014

Allen brown has a function to filter a form as you type into a text control. 
A search form would be easer on your users and load far faster then a 
131,000 record ComboBox.

http://allenbrowne.com/AppFindAsUType.html

[toc] | [prev] | [next] | [standalone]

#11217

From	Patrick Finucane <patrickfinucanetx@gmail.com>
Date	2015-10-19 06:52 -0700
Message-ID	<f5457e89-2ef5-43f9-936d-0eaf9c2b8353@googlegroups.com>
In reply to	#11214

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.
> 
> What I would rather happen, anyway, is to live-filter the query that feeds the combo box based on what I am typing into it, which would greatly reduce 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.

[toc] | [prev] | [next] | [standalone]

#11219

From	"Phil" <phil@stantonfamily.co.uk>
Date	2015-10-19 15:42 +0100
Message-ID	<n02vhb$8ts$1@speranza.aioe.org>
In reply to	#11217

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

[toc] | [prev] | [next] | [standalone]

#11220

From	Patrick Finucane <patrickfinucanetx@gmail.com>
Date	2015-10-19 09:22 -0700
Message-ID	<3ca4d38d-3160-4157-9e95-6445f0e85765@googlegroups.com>
In reply to	#11219

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.

[toc] | [prev] | [next] | [standalone]

#11221

From	"Phil" <phil@stantonfamily.co.uk>
Date	2015-10-19 22:22 +0100
Message-ID	<n03mvm$2ql$1@speranza.aioe.org>
In reply to	#11220

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

[toc] | [prev] | [next] | [standalone]

#11222

From	Patrick Finucane <patrickfinucanetx@gmail.com>
Date	2015-10-20 05:55 -0700
Message-ID	<352f8b5e-682e-4dae-ae6f-468461d01ae1@googlegroups.com>
In reply to	#11221

On Monday, October 19, 2015 at 5:23:13 PM UTC-4, Phil wrote:
> 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

That is what I'd do.  Typically, I have a Main Form and Subform.  The subform is presented as a datasheet.  The main form has some fields to filter on.  They call an event called SetFilter. SetFilter filters the datasheet. Works like a champ.

[toc] | [prev] | [next] | [standalone]

#11231

From	magmike <magmike7@yahoo.com>
Date	2015-10-22 20:38 -0700
Message-ID	<6bc35a57-a874-4b06-845e-0b31247ed82f@googlegroups.com>
In reply to	#11221

On Monday, October 19, 2015 at 4:23:13 PM UTC-5, Phil wrote:
> 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

Phil, could I see the filter as you type version? Or can that be accomplished by using the OnDirty  event instead of afterupdate?

Patrick - Can I see your SetFilter event?

Thanks!

[toc] | [prev] | [next] | [standalone]

#11232

From	"Phil" <phil@stantonfamily.co.uk>
Date	2015-10-23 08:33 +0100
Message-ID	<n0cnsc$fp5$1@speranza.aioe.org>
In reply to	#11231

> Phil, could I see the filter as you type version? Or can that be
> accomplished by using the OnDirty event instead of afterupdate?
> 
> Patrick - Can I see your SetFilter event?
> 
> Thanks!
> 

No problem but what have you in mind?
I suppose if you have Skype, we could share screens.
Might be best if you emailed me direct

Phil

[toc] | [prev] | [next] | [standalone]

#11239

From	magmike <magmike7@yahoo.com>
Date	2015-10-23 17:14 -0700
Message-ID	<3bc29499-540b-40a8-974e-9ff1a5495af8@googlegroups.com>
In reply to	#11232

On Friday, October 23, 2015 at 2:33:40 AM UTC-5, Phil wrote:
> > Phil, could I see the filter as you type version? Or can that be
> > accomplished by using the OnDirty event instead of afterupdate?
> > 
> > Patrick - Can I see your SetFilter event?
> > 
> > Thanks!
> > 
> 
> No problem but what have you in mind?
> I suppose if you have Skype, we could share screens.
> Might be best if you emailed me direct
> 
> Phil

Phil - I meant the code for it. Not seeing it in action.

[toc] | [prev] | [next] | [standalone]

#11240

From	"Phil" <phil@stantonfamily.co.uk>
Date	2015-10-24 09:26 +0100
Message-ID	<n0ffco$il$1@speranza.aioe.org>
In reply to	#11239

On 24/10/2015 01:14:00, magmike wrote:
> On Friday, October 23, 2015 at 2:33:40 AM UTC-5, Phil wrote:
>> > Phil, could I see the filter as you type version? Or can that be
>> > accomplished by using the OnDirty event instead of afterupdate?
>> >
>> > Patrick - Can I see your SetFilter event?
>> >
>> > Thanks!
>> >
>>
>> No problem but what have you in mind?
>> I suppose if you have Skype, we could share screens.
>> Might be best if you emailed me direct
>>
>> Phil
> 
> Phil - I meant the code for it. Not seeing it in action.
> 

Didn't you see my post dated 19th October (first of 2 posts on that date)?

Phil

[toc] | [prev] | [next] | [standalone]

#11233

From	Patrick Finucane <patrickfinucanetx@gmail.com>
Date	2015-10-23 06:00 -0700
Message-ID	<2e1f2c41-98c3-4ef1-9bb5-e8c7e5758a4c@googlegroups.com>
In reply to	#11231

On Thursday, October 22, 2015 at 11:38:49 PM UTC-4, magmike wrote:
> On Monday, October 19, 2015 at 4:23:13 PM UTC-5, Phil wrote:
> > 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
> 
> Phil, could I see the filter as you type version? Or can that be accomplished by using the OnDirty  event instead of afterupdate?
> 
> Patrick - Can I see your SetFilter event?
> 
> Thanks!

Let's say I have 2 fields in the main form header; customer name and active.  In the AfterUpdate event I have something like
  Private CustomerNameFilter_AfterUpdate
    SetFilter
  EndSub

  Private Sub SetFilter()
    Dim strF As String
    If NZ(Me.CustomerName,"") > "" then
       strF = "CusterName Like *" & Me.CustomerName & "*"
    Endif
    'continue with multiple fields to assign to filter
    If strF > "" then
        Forms!ThisForm!ThisFormSubform.Form.Filter = strF
        Forms!ThisForm!ThisFormSubform.Form.FilterOn = True
    Endif
  End sub

I can string multiple filter fields to strF.  I also have a command button to clear the filter as well.
  
Works great.

[toc] | [prev] | [next] | [standalone]

#11238

From	magmike <magmike7@yahoo.com>
Date	2015-10-23 17:13 -0700
Message-ID	<4ca8ffd8-0c9e-411c-bf7c-43b152d78255@googlegroups.com>
In reply to	#11233

On Friday, October 23, 2015 at 8:00:24 AM UTC-5, Patrick Finucane wrote:
> On Thursday, October 22, 2015 at 11:38:49 PM UTC-4, magmike wrote:
> > On Monday, October 19, 2015 at 4:23:13 PM UTC-5, Phil wrote:
> > > 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
> > 
> > Phil, could I see the filter as you type version? Or can that be accomplished by using the OnDirty  event instead of afterupdate?
> > 
> > Patrick - Can I see your SetFilter event?
> > 
> > Thanks!
> 
> Let's say I have 2 fields in the main form header; customer name and active.  In the AfterUpdate event I have something like
>   Private CustomerNameFilter_AfterUpdate
>     SetFilter
>   EndSub
> 
>   Private Sub SetFilter()
>     Dim strF As String
>     If NZ(Me.CustomerName,"") > "" then
>        strF = "CusterName Like *" & Me.CustomerName & "*"
>     Endif
>     'continue with multiple fields to assign to filter
>     If strF > "" then
>         Forms!ThisForm!ThisFormSubform.Form.Filter = strF
>         Forms!ThisForm!ThisFormSubform.Form.FilterOn = True
>     Endif
>   End sub
> 
> I can string multiple filter fields to strF.  I also have a command button to clear the filter as well.
>   
> Works great.

Patrick said:
 'continue with multiple fields to assign to filter

Would that look like:
     If NZ(Me.CustomerName,"") > "" then
        strF = "CustomerName Like *" & Me.CustomerName & "*"
        strF = "Notes Like *" & Me.Notes & "*"
        strF = "City Like *" & Me.City & "*"
     Endif

Or:
     If NZ(Me.CustomerName,"") > "" then
        strF = "CustomerName Like *" & Me.CustomerName & "*"
     Endif
     If NZ(Me.CustomerName,"") > "" then
        strF = "Notes Like *" & Me.Notes & "*"
     Endif
     If NZ(Me.CustomerName,"") > "" then
        strF = "City Like *" & Me.City & "*"
     Endif

[toc] | [prev] | [next] | [standalone]

#11254

From	Patrick Finucane <patrickfinucanetx@gmail.com>
Date	2015-10-26 06:29 -0700
Message-ID	<82e8b97a-bd1a-47b3-8af4-0b63ce703596@googlegroups.com>
In reply to	#11238

On Friday, October 23, 2015 at 8:13:20 PM UTC-4, magmike wrote:
> On Friday, October 23, 2015 at 8:00:24 AM UTC-5, Patrick Finucane wrote:
> > On Thursday, October 22, 2015 at 11:38:49 PM UTC-4, magmike wrote:
> > > On Monday, October 19, 2015 at 4:23:13 PM UTC-5, Phil wrote:
> > > > 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
> > > 
> > > Phil, could I see the filter as you type version? Or can that be accomplished by using the OnDirty  event instead of afterupdate?
> > > 
> > > Patrick - Can I see your SetFilter event?
> > > 
> > > Thanks!
> > 
> > Let's say I have 2 fields in the main form header; customer name and active.  In the AfterUpdate event I have something like
> >   Private CustomerNameFilter_AfterUpdate
> >     SetFilter
> >   EndSub
> > 
> >   Private Sub SetFilter()
> >     Dim strF As String
> >     If NZ(Me.CustomerName,"") > "" then
> >        strF = "CusterName Like *" & Me.CustomerName & "*"
> >     Endif
> >     'continue with multiple fields to assign to filter
> >     If strF > "" then
> >         Forms!ThisForm!ThisFormSubform.Form.Filter = strF
> >         Forms!ThisForm!ThisFormSubform.Form.FilterOn = True
> >     Endif
> >   End sub
> > 
> > I can string multiple filter fields to strF.  I also have a command button to clear the filter as well.
> >   
> > Works great.
> 
> Patrick said:
>  'continue with multiple fields to assign to filter
> 
> Would that look like:
>      If NZ(Me.CustomerName,"") > "" then
>         strF = "CustomerName Like *" & Me.CustomerName & "*"
>         strF = "Notes Like *" & Me.Notes & "*"
>         strF = "City Like *" & Me.City & "*"
>      Endif
> 
> Or:
>      If NZ(Me.CustomerName,"") > "" then
>         strF = "CustomerName Like *" & Me.CustomerName & "*"
>      Endif
>      If NZ(Me.CustomerName,"") > "" then
>         strF = "Notes Like *" & Me.Notes & "*"
>      Endif
>      If NZ(Me.CustomerName,"") > "" then
>         strF = "City Like *" & Me.City & "*"
>      Endif

Mine would look more like
  If NZ(Me.CustomerName,"") > "" then
     'note the syntax change below.  Strings need dbl quotes
     strF = "? "Customer Like ""*" & Me.CustomerName & "*"""
 & "* And "
  Endif
  If NZ(Me.Notes,"") > "" then
     strF = strF & "Notes Like ""*" & Me.Notes & "* And "
  Endif
  If NZ(Me.City,"") > "" then
      strF = strF & "City Like ""*" & Me.City & "* And "
  Endif

  'remove trailing And
  If strF > "" then strF = Left(strF,len(strF)-5)
  Me.Filter = strF
  Me.FilterOn = True

[toc] | [prev] | [next] | [standalone]

#11241

From	magmike <magmike7@yahoo.com>
Date	2015-10-24 16:19 -0700
Message-ID	<98d910bd-c635-46cb-ba89-1aa69c945dc9@googlegroups.com>
In reply to	#11233

On Friday, October 23, 2015 at 8:00:24 AM UTC-5, Patrick Finucane wrote:
> On Thursday, October 22, 2015 at 11:38:49 PM UTC-4, magmike wrote:
> > On Monday, October 19, 2015 at 4:23:13 PM UTC-5, Phil wrote:
> > > 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
> > 
> > Phil, could I see the filter as you type version? Or can that be accomplished by using the OnDirty  event instead of afterupdate?
> > 
> > Patrick - Can I see your SetFilter event?
> > 
> > Thanks!
> 
> Let's say I have 2 fields in the main form header; customer name and active.  In the AfterUpdate event I have something like
>   Private CustomerNameFilter_AfterUpdate
>     SetFilter
>   EndSub
> 
>   Private Sub SetFilter()
>     Dim strF As String
>     If NZ(Me.CustomerName,"") > "" then
>        strF = "CusterName Like *" & Me.CustomerName & "*"
>     Endif
>     'continue with multiple fields to assign to filter
>     If strF > "" then
>         Forms!ThisForm!ThisFormSubform.Form.Filter = strF
>         Forms!ThisForm!ThisFormSubform.Form.FilterOn = True
>     Endif
>   End sub
> 
> I can string multiple filter fields to strF.  I also have a command button to clear the filter as well.
>   
> Works great.

Patrick - When I type something in my CustomerFilter field, I get this error: "Syntax error (missing operator) in query expression 'Customer Like *lending*'. 

Any thoughts?

My Code:

Private Sub CustomerFilter_AfterUpdate()
    SetFilter
End Sub

Private Sub SetFilter()
    Dim strF As String
    If Nz(Me.Customer, "") > "" Then
       strF = "Customer Like *" & Me.CustomerFilter & "*"
    End If
    'continue with multiple fields to assign to filter
    If strF > "" Then
        Forms!sfStreetSheetsQ.Filter = strF
        Forms!sfStreetSheetsQ.FilterOn = True
    End If
  End Sub

[toc] | [prev] | [next] | [standalone]

#11242

From	magmike <magmike7@yahoo.com>
Date	2015-10-24 16:32 -0700
Message-ID	<1477edb7-0480-40a1-ac3b-b9f342ffd884@googlegroups.com>
In reply to	#11214

On Sunday, October 18, 2015 at 2:44:53 PM UTC-5, 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.
> 
> What I would rather happen, anyway, is to live-filter the query that feeds the combo box based on what I am typing into it, which would greatly reduce 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

Patrick - When I type something in my CustomerFilter field, I get this error: "Syntax error (missing operator) in query expression 'Customer Like *lending*'. 

Any thoughts? 

My Code: 

Private Sub CustomerFilter_AfterUpdate() 
    SetFilter 
End Sub 

Private Sub SetFilter() 
    Dim strF As String 
    If Nz(Me.CustomerFilter, "") > "" Then 
       strF = "Customer Like *" & Me.CustomerFilter & "*" 
    End If 
    'continue with multiple fields to assign to filter 
    If strF > "" Then 
        Forms!sfStreetSheetsQ.Filter = strF 
        Forms!sfStreetSheetsQ.FilterOn = True 
    End If 
  End Sub

[toc] | [prev] | [next] | [standalone]

#11243

From	"Phil" <phil@stantonfamily.co.uk>
Date	2015-10-25 01:50 +0000
Message-ID	<n0hch9$4om$1@speranza.aioe.org>
In reply to	#11242

On 24/10/2015 23:32:03, magmike wrote:
> 
> If Nz(Me.CustomerFilter, "") > "" Then
> strF = "Customer Like " & Me.CustomerFilter & ""
> End If

The above should read


    If Nz(Me.CustomerFilter) > "" Then 
       strF = "Customer Like " & Chr$(34) & Me.CustomerFilter & Chr$(34)
    End If 

Basicall you missed of one set of quotes.

In my opinion, 
The Chr$(34) is much clearer to read than a complex string of single & doube
quotes

[toc] | [prev] | [next] | [standalone]

#11244

From	magmike <magmike7@yahoo.com>
Date	2015-10-24 19:18 -0700
Message-ID	<b09380d1-2d20-46fd-918a-470a615dea34@googlegroups.com>
In reply to	#11214

On Sunday, October 18, 2015 at 2:44:53 PM UTC-5, 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.
> 
> What I would rather happen, anyway, is to live-filter the query that feeds the combo box based on what I am typing into it, which would greatly reduce 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

That works great for exact matches. How do I get the wildcards back in there?

[toc] | [prev] | [next] | [standalone]

#11245

From	magmike <magmike7@yahoo.com>
Date	2015-10-24 19:25 -0700
Message-ID	<eb5311cd-22ef-435f-b445-d9c2ce5a707c@googlegroups.com>
In reply to	#11244

On Saturday, October 24, 2015 at 9:18:27 PM UTC-5, magmike wrote:
> On Sunday, October 18, 2015 at 2:44:53 PM UTC-5, 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.
> > 
> > What I would rather happen, anyway, is to live-filter the query that feeds the combo box based on what I am typing into it, which would greatly reduce 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
> 
> That works great for exact matches. How do I get the wildcards back in there?

I got it:
       strF = "Customer Like " & Chr$(34) & Chr$(42) & Me.CustomerFilter & Chr$(42) & Chr$(34)

Thanks for your help!

[toc] | [prev] | [next] | [standalone]

Page 1 of 2 [1] 2 Next page →

csiph-web

Filter Combo Box as I Type

Contents

#11214 — Filter Combo Box as I Type

#11215

#11216

#11217

#11219

#11220

#11221

#11222

#11231

#11232

#11239

#11240

#11233

#11238

#11254

#11241

#11242

#11243

#11244

#11245