Search within a ComboBox

I have some code on a datasheet form on a combobox field. This code is below. It does not run as seamless and does not reset. So when I am in form if I try to make changes to another box the combobox is still filtered. How do I get it to reset when moving to a new record. If the user starts typing it updates.

Private Sub Ultimate_ID_Change()

' Function Description:
' Filter a combo box list as the user types.
' This is accomplished by grabbing the text typed by the user in the
' combo box's edit field, creating an SQL SELECT statement from it,
' and finally applying that SQL statement to the combo box's
' .RowSource property.

Dim strText, strFind
' Get the text that the user has typed into the combo box editable field.
strText = Me.[Ultimate ID].Text
' If the user has typed something in, then filter the combobox
' list to limit the visible records to those that contain the
' typed letters.
' Otherwise (if the field is blank), the user has deleted whatever
' text they typed, so show the entire (unfiltered) list
If Len(Trim(strText)) > 0 Then
    ' Show the list with only those items containing the typed
    ' letters.
    ' Create an SQL query string for the WHERE clause of the SQL
    ' SELECT statement.
    strFind = "tbl_ultimate.[Ultimate Parent Name] Like '"
    For i = 1 To Len(Trim(strText))
        If (Right(strFind, 1) = "*") Then
            ' When adding another character, remove the
            ' previous "*," otherwise you end up with
            ' "*g**w*" instead of "*g*w*."
            ' This has no apparent impact on the user, but
            ' ensures that the SQL looks as intended.
            strFind = Left(strFind, Len(strFind) - 1)
        End If
        strFind = strFind & "*" & Mid(strText, i, 1) & "*"
    Next
    strFind = strFind & "'"
    
    ' Create the full SQL SELECt string for the combo box's
    ' .RowSource property.
    
    strSQL = "SELECT tbl_ultimate.[Ultimate ID], tbl_ultimate.[Ultimate Parent Name] FROM tbl_ultimate Where " & _
    strFind & " ORDER by tbl_ultimate.[Ultimate Parent Name] ;"
    
    '' NOTE: to remove the order requirement, such that typing "wg"
    '' and "gw" return the same results, the SQL WHERE clause needs
    '' to look like "Name Like '*w* AND *g*'."
    '' The code above should be changed as follows:
    ''For i = 1 To Len(Trim(strText))
    ''   strFind = strFind & "Name Like '*" & Mid(strText, i, 1) & "*' And "
    ''Next
    ''
    ''strSQL = "SELECT tName.nameKey, tName.Name, SortOrder from tblApps Where " & _
    ''Left(strFind, Len(strFind) - 5) & " Order By SortOrder"
    
    ' Filter the combo list records using the new SQL statement.
    Me.[Ultimate ID].RowSource = strSQL
    
Else
    ' Show the entire list.
    
    strSQL = "SELECT tbl_ultimate.[Ultimate ID], tbl_ultimate.[Ultimate Parent Name] FROM tbl_ultimate ORDER BY tbl_ultimate.[Ultimate Parent Name]; "
    Me.[Ultimate ID].RowSource = strSQL
End If

' Make sure the combobox is open so the user
' can see the items available on list.
Me.[Ultimate ID].Dropdown

End Sub

Open in new window

kwarden13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
create a sub routine that sets all combo box to their original row source, and call the sub routine when you move to a new record

private sub resetrowsource()
me.combo1.rowsource="select blahblah...
me.combo2.rowsource="select blahblah...
end sub

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
kwarden13

Your code is on the "Change" event of the combobox.
Filtering a combobox while it is being typed into, ...is a bit odd because the full list is not even visible (dropped down) while you are typing into it.

The change event is a difficult event to manage, as it fires each time a key is presses (in the combobox)
So you are running *all* that code, ...each time a person hits a key.

You could simplify this by typing in your filter string all at once, then hitting a button to apply the filter.
This way the filter is built only once. instead of rebuilding itself each time a key is pressed.

Also note that you have a fair amount of code there, ...so it may be possible to simplify it...

JeffCoachman
kwarden13Author Commented:
I am already doing a search to get a subset of records, however, then  the user may need to change this box, so all options need to be available in the dropdown.

Maybe I can put the code in the after update event? or have it trigger after a 1 second?

Rey - Where would the sub routine go? Or just in a general module?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Download eBook now!
Rey Obrero (Capricorn1)Commented:
place the code in the module of the form where the combo box are.
kwarden13Author Commented:
I will try that tomorrow Rey and let you know how it goes.

Thank you!
kwarden13Author Commented:
Hi Rey-

Which event do I call the sub routine under? The lost focus event?
Rey Obrero (Capricorn1)Commented:
place the codes in the current event of the form
kwarden13Author Commented:
I have this code and when called get a method error.

Private Sub resetrowsource()
Me.[New SPS Supplier ID].RowSource = "SELECT tbl_sps_supplier.[New SPS Supplier ID], tbl_sps_supplier.[SPS Supplier Name] FROM tbl_sps_supplier;"
Me.[Normalized Vendor ID].RowSource = "SELECT tbl_normalized.[Normalized Vendor ID], tbl_normalized.[Normalized Vendor Name] FROM tbl_normalized;"
Me.[Immediate Parent ID].RowSource = "SELECT tbl_immediate.[Immediate Parent ID], tbl_immediate.[Immediate Parent Name] FROM tbl_immediate;"
Me.[Ultimate ID].RowSource = "SELECT tbl_ultimate.[Ultimate ID], tbl_ultimate.[Ultimate Parent Name] FROM tbl_ultimate;"
End Sub

Open in new window

Rey Obrero (Capricorn1)Commented:
which line is highlighted?
kwarden13Author Commented:
the first one where it says Rowsource
Rey Obrero (Capricorn1)Commented:
this line is the first line that have "rowsource" in it

Private Sub resetrowsource()

is this the highlighted line?
kwarden13Author Commented:
Yes the first line
Rey Obrero (Capricorn1)Commented:
where is the Private Sub resetrowsource() located?
kwarden13Author Commented:
i just put it in the form code, no specific event
Rey Obrero (Capricorn1)Commented:
change

Private Sub resetrowsource()

with

Private Sub ResetComboRowSource()

do you still get the error?
kwarden13Author Commented:
Check out the attached screenshot,

I should mention I put the code in the subform not the main form. That is where my combo boxes are.
screenshot_row.docx
Rey Obrero (Capricorn1)Commented:
upload a copy of your db
kwarden13Author Commented:
This is my example db so field names are slightly different
sample_3.241--3-.accdb
Rey Obrero (Capricorn1)Commented:
<field names are slightly different>
sorry, but I can't continue guessing..
kwarden13Author Commented:
alright I will close the question....thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.