Filter as you type combo box in Access 2

[JanaLNelson]

Pretty much the ONLY FilterAsUType I can find that can almost understand!
This looks like almost the code I need but I'm having a few problems.

'Form design settings
'Set AutoExpand to No (Ok I managed this)
'Column Count 3 (Why? as far as I see it there are only 2 columns the nameKey and the Name columns)
'Keyed on Column 1 (record primary key) (is that the Bound column?)
'Showing column 2 (user-readable data) column 2 width >0 (not sure what this means.)
'First and Second Column width=0 (does this conflict with the last request to set column 2 >0 ?)

Also when I type into combo0 I get an error "Enter Parameter Value" SortOrder

I have pasted the whole of the text from faq 702-6295 below

Private Sub Combo0_Change()
' Function Description:
' Filter a combo box list as the user types, similarly to how application
' launchers like Colibri, AppRocket and LaunchBar opperate.
' e.g. if the list contains the names of U.S. Presidents, and
' the user types "gw," then the resulting SQL WHERE clause will
' look like "Name Like '*g*w*'" and the resulting list
' will include George Washington, George H. W. Bush and
' George W. Bush, among others.
' The order is preserved, so that typing "wg" creates an SQL WHERE
' clause like "Name Like '*w*g*'" and the resulting list would
' include George Washington but not the Bushes.

' 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.

' Form design settings:
' Set AutoExpand to No
' Column Count 3
' Keyed on column 1 (record primary key)
' Showing column 2 (user-readable data) column 2 width > 0
' First and Second column width=0

Dim strText, strFind Not sure what the strText is?

' Get the text that the user has typed into the combo box editable field. Which field is the editable field?
strText = Me.Combo0.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 = "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 tName.nameKey, tName.Name, SortOrder FROM tName Where " & _
strFind & " ORDER BY SortOrder;"

' 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.Combo0.RowSource = strSQL

Else
' Show the entire list.
strSQL = "SELECT tName.nameKey, tName.Name, tName.SortOrder FROM tName ORDER BY tName.SortOrder; "
Me.Combo0.RowSource = strSQL
End If

' Make sure the combobox is open so the user
' can see the items available on list.
Me.Combo0.Dropdown

End Sub

If I want to change the Recordsource of my form to match the results of Me.Combo0.Rowsource = SQL, how??

Thank you for allowing me to drag this old thread up.
It is a good one.
Thanks, Jana

 

[JanaLNelson]

sxschech
Thanks for that tip!
Jana

 

[Andrzejek]

>However, the filtering I am trying to reproduce will let her key in "Screw (pulls up all the screws) Grade (from the middle of the field) and Flat" (also from the middle somewhere).

So, your filter should end up as:[tt]
"strDescription like '*Screw*' OR strDescription like '*Grade*' OR strDescription like '*Flat*'[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[JanaLNelson]

How does that fit into here?
I don't know how to make those variables.

Private Sub txtSearchDesc_Change()
    Me.sfrm_Search.Form.Filter = "strDescription like '" & Replace(Me.txtSearchDesc.Text, "#", "[#]") & "*'" ' where SomeText is the fieldname you want to filter on, and noting that # is a special character in SQL
    Me.sfrm_Search.Form.FilterOn = True ' make sure filter is activated
End Sub

 

[Andrzejek]

It doesn't really...
The approach I have shown you is for a text box and a command button. This way you will know when user is done typing their 'search' criteria, and then you can do your filtering.

With you current
[tt]txtSearchDesc_Change()[/tt]
approach, you do the filtering/searching after every character is typed into a text box.

It would be very beneficial, especially to you, to establish how your search should work, before you do any actual coding. And you need to be very specific.
You can see, you have several options how to approach this issue. But, if you do not know where you are going, you may run around in the circles without reaching any working solution – since you do not know yet how your search needs to work.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

With [tt]txtSearchDesc[/tt] and [tt]cmdSearch[/tt], you may do this:

Option Explicit

Private Sub txtSearchDesc_Change()
cmdSearch.Enabled = Len(Trim(txtSearchDesc.Text))
End Sub

Private Sub cmdSearch_Click()
Dim ary() As String
Dim strF As String
Dim X As Integer

strF = Replace(Me.txtSearchDesc.Text, "#", "[#]")
strF = Replace(Me.txtSearchDesc.Text, "'", "''")

ary = Split(UCase(strF), " ") [green]'Looks your data is all UPPER CASE[/green]
strF = ""

For X = LBound(ary) To UBound(ary)
    If Len(strF) > 0 Then
        strF = strF & " OR "
    End If
    strF = strF & "strDescription LIKE '*" & ary(X) & "*'"
Next X
[green]
'Debug.Print strF[/green]

Me.sfrm_Search.Form.Filter = strF
Me.sfrm_Search.Form.FilterOn = True

End Sub

If you type in the text box: [blue][tt]Screw Grade Flat[/tt][/blue]
and click [tt]cmdSearch[/tt], you get your Filter as:
[tt]strDescription LIKE '*SCREW*' OR strDescription LIKE '*GRADE*' OR strDescription LIKE '*FLAT*'[/tt]


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[strongm]

[COLOR=blue]Private Sub txtSearchDesc_Change()
    Dim strCondition As String
    strCondition = Replace(Me.txtSearchDesc.Text, "#", "[#]") [COLOR=green]' deal with fact that # is a special character in SQL[/color]
    strCondition = "strDescription like '*" & Join(Split(strCondition, " "), "*' and strDescription like '*") & "*'" [COLOR=green]' create required filter[/color]
    Me.Subform.Form.Filter = strCondition
    Me.Subform.Form.FilterOn = True [COLOR=green]' make sure filter is activated[/color]
End Sub[/color]

 

[Andrzejek]

strongm,
Shouldn't your [tt]AND[/tt] be an [tt]OR[/tt] in strCondition?
Also, since every key stroke / character is evaluated, as soon as type a SPACE, you get all records (until you keep typing) since the Filter will be something like:
[tt]strDescription like '*abc*' and OR strDescription like '[highlight #FCE94F]**[/highlight]'[/tt]

BTW - nice use of Split and Join in one line

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[strongm]

Nope. 'And' is what I meant. Check your logic and the results the OP wants.

 

[Andrzejek]

Ooops! Sorry. You were right

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[strongm]

Now just need the OP to try it out ...

 

[JanaLNelson]

Well, I'm going to jump in the middle of this.


You said:
With your current
txtSearchDesc_Change()approach, you do the filtering/searching after every character is typed into a text box.

YOU GOT IT-EXACTLY what I want and she doesn't care how they filter-as-she-types as long as the description she's looking for eventually shows up when she is through typing:
Begins with SCREW contains GRADE and contains FLAT.

Would it be forward of me to upload my table and form and the code I have for y'all to look at?

 

[strongm]

Er ... hello ... I've provided code above, a pretty minor modification to the txtSearchDesc_Change code I provided in the example database, that should do exactly this. Have you actually tried it?

 

[JanaLNelson]

you know, if you type "screw #10*pan" in the example db you sent me, it filters acceptably.
But then it never filters anything else again - even after close and reopen.
Weird huh?

 

[strongm]

>Weird huh?

yes. And I can't replicate that here.

 

[JanaLNelson]

Yes, that is perfect! My apologies, I guess I got a couple of steps behind.
Appreciate you!!