Search within a ComboBox

kwarden13
kwarden13 used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
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
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
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

Author

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?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2016

Commented:
place the code in the module of the form where the combo box are.

Author

Commented:
I will try that tomorrow Rey and let you know how it goes.

Thank you!

Author

Commented:
Hi Rey-

Which event do I call the sub routine under? The lost focus event?
Top Expert 2016

Commented:
place the codes in the current event of the form

Author

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

Top Expert 2016

Commented:
which line is highlighted?

Author

Commented:
the first one where it says Rowsource
Top Expert 2016

Commented:
this line is the first line that have "rowsource" in it

Private Sub resetrowsource()

is this the highlighted line?

Author

Commented:
Yes the first line
Top Expert 2016

Commented:
where is the Private Sub resetrowsource() located?

Author

Commented:
i just put it in the form code, no specific event
Top Expert 2016

Commented:
change

Private Sub resetrowsource()

with

Private Sub ResetComboRowSource()

do you still get the error?

Author

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
Top Expert 2016

Commented:
upload a copy of your db

Author

Commented:
This is my example db so field names are slightly different
sample_3.241--3-.accdb
Top Expert 2016

Commented:
<field names are slightly different>
sorry, but I can't continue guessing..

Author

Commented:
alright I will close the question....thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial