Filter Combo As You Type (Prefix) (1 Viewer)

[dgreen]

@MajP
See attached file. I'm using your code for the FAYT and am having issues implementing for my prefix column. I've gotten it to work on the Combo7 (showing the organizations), so I do know that it works.

Issues:
1) When I start typing in Combo5 it doesn't open to display the filtered results.
2) It doesn't filter as you type.

Visual of issue 1

Visual of opened drop down that isn't filtering

The difference between the 2 combo boxes, other than data source, is that for Prefix I don't display the search field and I'm showing more than 2 columns in the combo box.

 

[zeroaccess]

Honestly for a drop down list that small, I wouldn't bother with FAYT. I want my users to see the options available. YMMV.

 

[dgreen]

The actual list is much larger. It's just a test dataset to help me get the code right and working.

Honestly for a drop down list that small, I wouldn't bother with FAYT. I want my users to see the options available.

 

[zeroaccess]

Well you said FAYT isn't working, and it clearly doesn't look like it is in your screen shot, so I assumed that was the full list.

 

[dgreen]

@zeroaccess Do you see anything wrong in the attached demo database (post 1) that might shed light as to why it's not working?

 

[MajP]

Public faytPrefix As New FindAsYouTypeCombo
Public faytOrg As New FindAsYouTypeCombo

Private Sub Form_Open(Cancel As Integer)
    faytPrefix.InitalizeFilterCombo Me.Combo5, "Prefix_Search", AnywhereInString, True, False
    faytOrg.InitalizeFilterCombo Me.Combo7, "ORG_Search", AnywhereInString, True, False
End Sub

What is interesting is this works way better than a cascading combobox. You do not lose visibility in a continuous form.

 

[dgreen]

@MajP - Thanks. Updated file attached for others to reference.
The fix was the update the Public Function on the Form Open to make each fayt unique and then update the Private Function to match.

Public faytPrefix As New FindAsYouTypeCombo
Public faytOrg As New FindAsYouTypeCombo

Private Sub Form_Open(Cancel As Integer)
    faytPrefix.InitalizeFilterCombo Me.Combo5, "Prefix_Search", AnywhereInString, True, False
    faytOrg.InitalizeFilterCombo Me.Combo7, "ORG_Search", AnywhereInString, True, False
End Sub

What is interesting is this works way better than a cascading combobox. You do not lose visibility in a continuous form.

 

[zeroaccess]

Public faytPrefix As New FindAsYouTypeCombo
Public faytOrg As New FindAsYouTypeCombo

Private Sub Form_Open(Cancel As Integer)
    faytPrefix.InitalizeFilterCombo Me.Combo5, "Prefix_Search", AnywhereInString, True, False
    faytOrg.InitalizeFilterCombo Me.Combo7, "ORG_Search", AnywhereInString, True, False
End Sub

What is interesting is this works way better than a cascading combobox. You do not lose visibility in a continuous form.

Can you explain what you mean by "lose visibility"?

 

[MajP]

If you do a cascading combobox in a continous form and filter the second combobox, all previous records with their values filtered out of the rowsource will appear to go blank. Requires a pretty significant workaround.

 

[MajP]

@dgreen
If you look at my FAYT listbox, it allows you to filter on one specified column or any column. Works well in a listbox. In a combobox I found it confusing so did not include the same code to do that. If needed it would be a pretty easy fix.

 

[dgreen]

Also for me, using my other technique, I was passing the search value to the rowsource so every other combo box that I already had a value for would start to filter out visually. Nothing was deleted but you wouldn't see the different values until you left the current combo box.

 

[zeroaccess]

If you do a cascading combobox in a continous form and filter the second combobox, all previous records with their values filtered out of the rowsource will appear to go blank. Requires a pretty significant workaround.

Ahh, yes. My workaround for that was to create an unbound popup form for the second combo. You make your selection there and it passes the value back to the 2nd combo box, which is disabled and locked (a small button next to it opens the popup form). My case is different though - more detail is needed to be displayed in that window, so not just a simple cascading filter like dgreen. Your solution looks like a good one for that. I've never seen anyone come up with a solution like this before.

 

[MajP]

Your solution looks like a good one for that. I've never seen anyone come up with a solution like this before.

This is not intended as a solution for a continuous cascade, it just has that as a benefit. The approach I use for cascading is here
You can do this with a couple of code events, however that solution is problematic. This works better.

 

[dgreen]

Take a look at this approach. It has a requery on the State [Combo15] that updates the City list [Combo13] when changed. The data source for the City combo has a criteria of [Forms]![f_Users]![Combo15].

I'm getting a too many parameters error, not sure what's causing it.... But it's working on my end. Thoughts?

 

[MajP]

Private Sub Combo13_Enter()
  Dim strSql As String
  strSql = "SELECT t_City_State.City, t_City_State.State_Abrv FROM t_City_State WHERE t_City_State.State_Abrv = '" & Nz([Forms]![f_Users]![Combo15], "") & "'"
  Combo13.RowSource = strSql
End Sub

Private Sub Combo13_Exit(Cancel As Integer)
  Dim strSql As String
  strSql = "SELECT t_City_State.City, t_City_State.State_Abrv FROM t_City_State"
  Combo13.RowSource = strSql
End Sub

 

[dgreen]

@MajP
I put your code in and while it's still giving me a error "3061 too few parameters. Expected 1.", it's working.... Can you look around and see if you can figure out what's causing the error?

Private Sub Combo13_Enter()
  Dim strSql As String
  strSql = "SELECT t_City_State.City, t_City_State.State_Abrv FROM t_City_State WHERE t_City_State.State_Abrv = '" & Nz([Forms]![f_Users]![Combo15], "") & "'"
  Combo13.RowSource = strSql
End Sub

Private Sub Combo13_Exit(Cancel As Integer)
  Dim strSql As String
  strSql = "SELECT t_City_State.City, t_City_State.State_Abrv FROM t_City_State"
  Combo13.RowSource = strSql
End Sub

 

[dgreen]

Maybe the quotes aren't quite right in the combo13_Enter code?

 

[MajP]

get rid of the macro.

 

[dgreen]

Macro is gone, still erroring. V5 attached.

 

[MajP]

Remove the form's reference in the rowsource of the city combo. Change to
SELECT t_City_State.City, t_City_State.State_Abrv
FROM t_City_State;