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!
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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="selec
me.combo2.rowsource="selec
end sub
private sub resetrowsource()
me.combo1.rowsource="selec
me.combo2.rowsource="selec
end sub
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
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
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?
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?
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
this line is the first line that have "rowsource" in it
Private Sub resetrowsource()
is this the highlighted line?
Private Sub resetrowsource()
is this the highlighted line?
change
Private Sub resetrowsource()
with
Private Sub ResetComboRowSource()
do you still get the error?
Private Sub resetrowsource()
with
Private Sub ResetComboRowSource()
do you still get the error?
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
I should mention I put the code in the subform not the main form. That is where my combo boxes are.
screenshot_row.docx
Premium Content
You need an Expert Office subscription to comment.Start Free Trial