thread702-1770989 I know this is 1

[SmartMan123]

thread702-1770989

I know this is an addition to a very old thread. but just in case, it might help someone to unstuck.

ms access vba combobox filter as you type , ms access vba combobox search as you type

Thanks BenSacheri for the nice thread and posted solution. It was actually very enlightening.
thread702-1756368: Auto-complete combo box...continuation of thread "Combobox as Find As You Type?"

However, in fact it opened my mind to a possible simpler approach. I believe I tested the new approach and it worked fine , even after the fourth trial.

the problem:
is to achieve a modern google-like search within a combobox, so as you type the combobox searches for results within its list items that are (like "*searching text 1*" AND like "*searching text 2*" AND "like "*searching text 3*") ... etc. Usually the combobox list items are pulled up from a lookup table (or look up query) and as you type the SQL statement that pulls the data gets updated with new ANDed "searching text(s)" to narrow down the results. Usually the combobox pulls two columns , the first contains the ID to be saved later on and the second contains the text to be displayed (and searched).

the normal approach of changing the ccombobox.rowsource should work. However, the problem is that it do not refresh the combo list items automatically. you have to re-query (which would get refused if it is a bound combobox with other mandatory fields yet to be filled). The first combobox.dropdown actually populates the list but that is it. after that you need a requery / refresh method/triger/acction to update the list items. other approaches might include showing a flash message (using API to display auto hiding message box after certain time - usually in milliseconds - collapses, or changing the focus forth and back then dropdown, or other methods that would result in a flash in the screen. or as suggested in the original post to use a lengthy approach to create a mirror combobox class to simulate the operation and pull out some tricks to the combobox class to be able to refresh the list items without any screen flickering.

it was the (tricks part) in your posted combined effort solution that gives me heads up that there is some faulty behavior in the combobox response to changing the rowsource from one SQL statement to another (same type however "Table/query"), just like other faulty behavior of decoupling events (application start, application display active window, form load, open, active .... that result in the well known bug behavior of not being able to hide the ms access window in your application with the needed API code added unless you add a delay timer of at least 900 milliseconds so the ms access window would actually forcibly display - despite your nose, free well, and efforts - first then the code in the start up form can be executed to hide it later and leave only your modal application main form, the start up form).

Anyways, the situation I was facing was a little bit more complex as I work with datasheet view and my client is looking for filter as you type per every row or item he enters to minimize the data entry typos to the least or zero.


the referenced thread solution:
I have not tested the referenced thread solution but as i said before it opened my mind to further similar more simpler techniques. I am sure the original post author have tested it multiple times to its best. even to make sure it works fine even after the fourth run. (a strange bug as it changes behavior after the fourth run! as mentioned in the thread!).

My new solution:
The new solution simply executed an internal combobox refresh without the need to reflect that neither on screen (flickering, msgboxes) nor other mirror combobox classes.

I simply changed the rowsourcetype to "Value List", deleted item 0, added 2 items , then back t normal changed the combobox rowsourcetype to "Table/Query". and finally resumed my logical flow of adding the new (strSQl) SQL statement with added (where like *variable* AND Like *variable*) amended. setfocus, dropdown and .... That's it!

For the problem part of being stuck with datasheet view, I simply added an unbound column for the search to act as the search box for each record (row). the new field has only a SQL lookup value, no bound field, limit to list = Yes, allow list item edit = NO, Auto Expand = NO, and - upon successful selection , and lost focus - it reflects the right value on a nearby NOT enabled bound text box.

I have tested the solution with more than 10 rows and it works fine. the only drawback (cosmetic) is that the (search combo) for the previous records always mirror the current active (search combo). It can be handled but no need, as as I said it is merely a cosmetic effect since I have the form for additions only and data entry = false.


I had to experiment several times with the contents of the combobox list after changing the rowsource type doing some loops until i reached this optimal solution steps.

combo.RowSourceType = "Value List"
combo.RemoveItem 0
combo.AddItem ("Item 1")
combo.AddItem ("Item 2")

combo.RowSourceType = "Table/Query"
combo.RowSource = SQLStr
combo.LimitToList = True

combo.Dropdown
combo.SetFocus
combo.Dropdown

Now the final complete code would be something like this :

Public Sub GoogleSearch(combo As ComboBox, OriginalSQL As String, LookupField As String)
' - OriginalSQL is not the recursive one
' - Use queries to build up your sql statement, then copy/paste thier sql text in the combo row source
'   do not just point the row source to the query name (keep the query for reference if you like.
'   to avoid the quotation agony of VBA built-in editor to create your OriginalSQL
' - Always store your OriginalSQL in global module variable,
' - Initiate it on (form) load, restore it on cancel AND after_update
' Created by Walid Zohair, not to be used without the exact comments


'DoEvents



If Trim(combo.Text) = "" Or IsNull(combo.Text) Then
    combo.RowSource = OriginalSQL
    combo.Dropdown
    combo.SetFocus
    Exit Sub
End If



Dim SQLStr As String
SQLStr = Replace(OriginalSQL, ";", "") ' make sure a bar end sql is used

' make sure order_by, group_by, Having will not be after where clause (gives error)
' This also can be used to give clearer names in outer SQL to be used in Where clause later
SQLStr = "SELECT * FROM ( " & SQLStr & " ) WHERE "



Dim StrArray() As String
Dim i As Long
StrArray = Split(Trim(combo.Text)) ' for saftey could be limited to up to 100 records only = Split(combo.text, " ", 100)



For i = 0 To UBound(StrArray)
    SQLStr = SQLStr & LookupField & " LIKE '*" & StrArray(i) & "*'"

    If UBound(StrArray) - i > 0 Then
        SQLStr = SQLStr & " AND " ' Add AND to to allow next phrase
    End If
Next i



' This is the code to "Refresh" the combobox list items "internallly"
combo.RowSourceType = "Value List"
combo.RemoveItem 0
combo.AddItem ("Item 1")
combo.AddItem ("Item 2")


' Resume updating the rowsource as usual , no flickerin effect and no extra processing, complexity or delay in large datasets
combo.RowSourceType = "Table/Query"
combo.RowSource = SQLStr
combo.LimitToList = True
'combo.Requery
'i = combo.ListCount
combo.Dropdown
combo.SetFocus
combo.Dropdown
End Sub

Thank you again all guys as I found the original thread and thanks again BenSacheri , it really helped me to unstuck and complete my efforts in this program.