Search as you type in combobox. (1 Viewer)

Fira_g · Nov 12, 2019

I am trying to implement method from the link below for my needs but it doesn't seem to work. :banghead:
I would appreciate if you could help me find out where i am going wrong.

Form: frmMainForm
Combobox: PickupID
Event: On Change

https://www.accessforums.net/showthread.php?t=48811

theDBguy · Nov 12, 2019

Hi. Not sure if it's the same one, but maybe give this one a try? Hope it helps...

Fira_g · Nov 12, 2019

theDBguy said:
Hi. Not sure if it's the same one, but maybe give this one a try? Hope it helps...

Thank you for the link. It gave me idea where I am going wrong. I am working my way through the code and will will be back if stuck again.

theDBguy · Nov 12, 2019

Fira_g said:
Thank you for the link. It gave me idea where I am going wrong. I am working my way through the code and will will be back if stuck again.

Hi. Good luck! If I get a chance, I might give it a try, but no promises. Cheers!

MajP · Nov 12, 2019

The point is that all the code is in a class module to encapsulate the functionality. You do not need to understand it or touch it. You instantiate it with one line of code and it turns any combobox into a fine as you type. No need to reinvent the wheel.

theDBguy · Nov 12, 2019

Hi MajP. Did you happen to look at the original link? Is it the same thing or something else? Just curious...

MajP · Nov 12, 2019

Yes, the link included the working class module example in the third combox. It also included some quick examples of rolling your own without the class module. The first example combo showed the strange behavior you will get If not setting the autoexpand to false.

Fira_g · Nov 12, 2019

MajP said:
Yes, the link included the working class module example in the third combox. It also included some quick examples of rolling your own without the class module. The first example combo showed the strange behavior you will get If not setting the autoexpand to false.

I hope you don't mind me using the class module from the example you created?

I have two fields on my form, one for pickup and another fro deliver: both use same query to get the address.
I can use your code for pickup with the lines of code below but don't know how include the delivery field so it works too?

Code:

Private Sub Form_Load()
faytProducts.InitalizeFilterCombo Me.PickLocation, "FullAddress", AnywhereInString, True, False
End Sub

Fira_g · Nov 12, 2019

when I use this code:

Code:

Private Sub Form_Load()
faytProducts.InitalizeFilterCombo Me.PickLocation, "FullAddress", AnywhereInString, True, False
faytProducts.InitalizeFilterCombo Me.DeliveryLocation, "FullAddress", AnywhereInString, True, False
End Sub

it only works for DeliveryLocation.
Could you advise the correct syntax please?

vba_php · Nov 12, 2019

dbGuy,

I seem to remember a version of access that did this "smart type" behavior by default for comboboxes. do you remember this?

Fira,

I believe you can also write code behind the KeyPress() event for the combobox, I think.

theDBguy · Nov 12, 2019

vba_php said:
dbGuy,

I seem to remember a version of access that did this "smart type" behavior by default for comboboxes. do you remember this?

Fira,

I believe you can also write code behind the KeyPress() event for the combobox, I think.

Hi. Access still has it, but I think this topic is an improvement to it or a totally different behavior than the standard one. For example, the standard behavior is to navigate to the first/matching row item but keep all the choices unchanged. Although I haven't tried this topic's approach, my guess is it limits the choices down to the ones matching the entered characters. So, as the user enters more characters, the list of row items diminish, which is not a standard behavior in Access.

vba_php · Nov 12, 2019

theDBguy said:
So, as the user enters more characters, the list of row items diminish, which is not a standard behavior in Access.

right. that's what I thought I witnessed in that previous version. that's the same as any other website intellisense really.

theDBguy · Nov 12, 2019

vba_php said:
right. that's what I thought I witnessed in that previous version. that's the same as any other website intellisense really.

Unfortunately, I don't remember seeing this behavior as a standard feature in any Access version I've used. Maybe I am getting too old.

vba_php · Nov 13, 2019

theDBguy said:
Unfortunately, I don't remember seeing this behavior as a standard feature in any Access version I've used. Maybe I am getting too old.

if anyone would remember, I would think it would be an older person. But it's possible that my alzheimers is kicking in at an early age.

MajP · Nov 13, 2019

if anyone would remember, I would think it would be an older person. But it's possible that my alzheimers is kicking in at an early age

No, never existed.

I have two fields on my form, one for pickup and another fro deliver: both use same query to get the address.
I can use your code for pickup with the lines of code below but don't know how include the delivery field so it works too?

Is this a single combo with two fields or two different FAYT combos? I could program a multi column FAYT combo but I do not because multi column comboboxes do not make sense to me. Not something I would use. I do provide this behavior for multi column listboxes. Here are classes to build FAYT forms, listboxes, and comboboxes.

If you want two (or more) different FAYT combos the code would be something like

Private faytPickup as New FindAsYouTypeCombo
Private faytDelivery as New FindAsYouTypeCombo
Private Sub Form_Load()
faytPickup.InitalizeFilterCombo Me.PickLocation, "FullAddress", AnywhereInString, True, False
faytDelivery.InitalizeFilterCombo Me.DeliveryLocation, "FullAddress", AnywhereInString, True, False
End Sub

isladogs · Nov 13, 2019

vba_php said:
I seem to remember a version of access that did this "smart type" behavior by default for comboboxes. do you remember this?

As MajP stated, this has never been a standard feature in Access. However, as well as MajP's own excellent example, there have been other similar examples available for many years.
For example, back in 2006 Allen Browne created a FAYT form for multiple fields. http://allenbrowne.com/AppFindAsUType.html

P.S. Does knowing that qualify me as

an older person

vba_php · Nov 13, 2019

isladogs said:
For example, back in 2006 Allen Browne created a FAYT form for multiple fields. http://allenbrowne.com/AppFindAsUType.html

I took a look at this zip and it seems to be un necessarily complex. for instance, in the LOAD event of the form Allen is dynamically creating AfterUpdate() and OnChange() events for the text box in the form with this code:

Code:

        frm!cboFindAsUTypeField.AfterUpdate = "=FindAsUTypeChange([Form])"
        frm.txtFindAsUTypeValue.OnChange = "=FindAsUTypeChange([Form])"

but allen is filtering an entire form, and that's not what the OP said he wants to do. In the link he originally provided, the solution was very simple, like:

Code:

Private Sub cboFindAsUTypeField_Change()

  Dim strSQL As String
  If Len(Me.cboFindAsUTypeField.Text) > 0 Then
  strSQL = "SELECT customers.companyname FROM customers WHERE (((customers.companyname) Like '*'" & " & " & """" & Me.cboFindAsUTypeField.Text & """" & " & " & "'*')) ORDER BY customers.companyname;"
  Else
  strSQL = "SELECT customers.companyname FROM customers ORDER BY customers.companyname;"    'This is the default row source of combo box
  End If
  Me.cboFindAsUTypeField.RowSource = strSQL
  Me.cboFindAsUTypeField.Dropdown
End Sub

in allen's database, you have to add "*" symbols on both sides of the combo value separated by spaces cuz allen's data has spaces in it. in the link by the OP, the data has no spaces in it so "*" on both sides of the data works fine.

Fira_g · Nov 13, 2019

MajP said:
No, never existed.

Is this a single combo with two fields or two different FAYT combos? I could program a multi column FAYT combo but I do not because multi column comboboxes do not make sense to me. Not something I would use. I do provide this behavior for multi column listboxes. Here are classes to build FAYT forms, listboxes, and comboboxes.

If you want two (or more) different FAYT combos the code would be something like

Thank you for the help, it works great.

Learning a lot from this thread

Fira_g · Nov 13, 2019

This is the code I originally used and it works fine. Only problem is that I could not use arrow keys to select from the filleted list.
The solution by MajP is the one I am using right now for that reason.

but allen is filtering an entire form said:
Private Sub cboFindAsUTypeField_Change()

Dim strSQL As String
If Len(Me.cboFindAsUTypeField.Text) > 0 Then
strSQL = "SELECT customers.companyname FROM customers WHERE (((customers.companyname) Like '*'" & " & " & """" & Me.cboFindAsUTypeField.Text & """" & " & " & "'*')) ORDER BY customers.companyname;"
Else
strSQL = "SELECT customers.companyname FROM customers ORDER BY customers.companyname;" 'This is the default row source of combo box
End If
Me.cboFindAsUTypeField.RowSource = strSQL
Me.cboFindAsUTypeField.Dropdown
End Sub
[/code]

in allen's database, you have to add "*" symbols on both sides of the combo value separated by spaces cuz allen's data has spaces in it. in the link by the OP, the data has no spaces in it so "*" on both sides of the data works fine.

Fira_g · Nov 19, 2019

MajP said:
No, never existed.

Is this a single combo with two fields or two different FAYT combos? I could program a multi column FAYT combo but I do not because multi column comboboxes do not make sense to me. Not something I would use. I do provide this behavior for multi column listboxes. Here are classes to build FAYT forms, listboxes, and comboboxes.

If you want two (or more) different FAYT combos the code would be something like

As I mentioned on my previous post it works great, the only issue now is with List Items Edit Form which which I explained in this thread: https://www.access-programmers.co.uk/forums/showthread.php?t=308033

Any suggestions will be greatly appreciated.

Search as you type in combobox. (1 Viewer)

Registered User.

Attachments

I’m here to help

Registered User.

I’m here to help

You've got your good things, and you've got mine.

I’m here to help

You've got your good things, and you've got mine.

Registered User.

Registered User.

Forum Troll

I’m here to help

Forum Troll

I’m here to help

Forum Troll

You've got your good things, and you've got mine.

Attachments

MVP / VIP

Forum Troll

Attachments

Registered User.

Registered User.

Registered User.

Similar threads

Users who are viewing this thread