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

 

[strongm]

You appear to be using a subform. So you need to apply the filter to that subform, not the main form (which is what my illustrative code does)

So, instead of

Private Sub txtSearchDesc_Change()
    Me.Filter = "strDescription like '" & Me.txtSearchDesc.Text & "*'" [COLOR=green]' where SomeText is the fieldname you want to filter on[/color]
    Me.FilterOn = True [COLOR=green]' make sure filter is activated[/color]
End Sub

try

Private Sub txtSearchDesc_Change()
    Me.[b]WhatEverTheSubFormIs.Form[/b].Filter = "strDescription like '" & Me.txtSearchDesc.Text & "*'" [COLOR=green]' where SomeText is the fieldname you want to filter on[/color]
    Me.[b]WhatEverTheSubFormIs.Form[/b].FilterOn = True [COLOR=green]' make sure filter is activated[/color]
End Sub

 

[Andrzejek]

The query of the form definitely contains strDescription and the other 4 fields as you can see.

That's not what you show in your picture (5 fields, not 4):
Stock#, Cust Stock#, Description, QOH, and Price

It looks to me that the name of your field in your table is not [tt][highlight #FCE94F]str[/highlight]Description[/tt], it is [tt]Description[/tt], so correct your code.

You were rigth, strongm. Jana doesn't search anywhere with the LIKE statement, she wants to search from the beginning of the [tt]Description[/tt] field.

---- Andy

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

 

[JanaLNelson]

Hi Helpers!

I have a continuous form (no subforms) with search textboxes on the header.
The table fieldname is strDescription and its label on the form is "Description" and the textbox is txtSearchDesc
The fields other than strDescription are not involved in the searches (because I am not using the combo box where they were needed).

OK I used the code above. Say I am looking for "Face Shield Covers" It would work except after I type "F" and all the "F"s are there, the field selects itself and if I go on to type an "a" next, it deletes the "F" and filters for "a" and so on...

My goal is to have both Stock and Description search boxes.
Thank you both so much for helping me with this project!!
Jana

 

[JanaLNelson]

New issue: how to remove problematic characters in strDescription like quotation marks, single quotation marks and hash marks in the searching?
They have 38k inventory items that they are not going to fix for me. Many, many with those characters.

 

[strongm]

>Say I am looking for "Face Shield Covers" It would work except after I type "F" and all the "F"s are there, the field selects itself and if I go on to type an "a" next, it deletes the "F" and filters for "a" and so on...

So, let's just check. My code is now working, except for the above odd behaviour? If so, then we have arrived at point 2 ... and is why we need to track selstart (interestingly - or not - if you had been using a subform, this issue would not arise!)

So, the barebones solution to the issue is:

[COLOR=blue]Private myStart As Long [COLOR=green]' holds SelStart[/color]

Private Sub txtSearchDesc_Change()
    Me.Filter = "strDescription like '" & Me.txtSearchDesc.Text & "*'" [COLOR=green]' where SomeText is the fieldname you want to filter on[/color]
    Me.FilterOn = True [COLOR=green]' make sure filter is activated
    ' Set SelStart and SelLength[/color]
    txtSearchDesc.SelStart = myStart
    txtSearchDesc.SelLength = 0
End Sub

[COLOR=green]' Capture corrected selstart for the textbox[/color]
Private Sub txtSearchDesc_KeyDown(KeyCode As Integer, Shift As Integer)
    Select Case KeyCode
        Case vbKeyBack  
            myStart = txtSearchDesc.SelStart - 1
        Case vbKeyDelete
            myStart = txtSearchDesc.SelStart
        Case Else
            myStart = txtSearchDesc.SelStart + 1
    End Select
End Sub[/color]

 

[JanaLNelson]

Alright. Works!
Until after I type the first word and then hit the spacebar.
If I keep typing with no space, I get error 2185.
debugs to this line: txtSearchDesc.SelStart = myStart

I'm not sure where to put this line: Is it a sub or does it need Dim or what?:

Private myStart As Long ' holds SelStart

 

[strongm]

Add the linein bold below

[COLOR=blue]Private Sub txtSearchDesc_Change()
    Me.Filter = "strDescription like '" & Me.txtSearchDesc.Text & "*'" [COLOR=green]' where SomeText is the fieldname you want to filter on[/color]
    Me.FilterOn = True [COLOR=green]' make sure filter is activated
    ' Set SelStart and SelLength[/color]
    [b]txtSearchDesc.SetFocus[/b]
    txtSearchDesc.SelStart = myStart
    txtSearchDesc.SelLength = 0
End Sub[/color]

 

[JanaLNelson]

No change
I type the first word and then hit the spacebar and it just sits there.
If I keep typing, same error on this line: txtSearchDesc.SelStart = myStart

 

[strongm]

Sadly. that's a quirk of Access - trimming fields (i,e removing trailing spaces) when they are refreshed. And I'm not aware of a configuration setting that stops this from happening. You can avoid the issue if you use a subform ...

Otherwise there's a bunch of fiddly coding that needs to be introduced

 

[Andrzejek]

Another way - and a LOT simpler - would be to use your [tt]txtSearchDesc[/tt] and a command button ([tt]cmdSearch[/tt]?). Allow user to type whatever they want, and when they are done typing, they would click [tt]cmdSearch[/tt] button to do their search.

It is not 'Filter-as-you-type', but your users may like this approach even better. Who knows...? Especially if you sell them this idea as a 'new and improved' way of searching.

---- Andy

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

 

[sxschech]

If you do add the button, consider setting up the tab order so that a user would have the option of getting to the button via the tab key and pressing enter rather than having to click the mouse.

 

[strongm]

Here's an example of the method with a subform, The only code is pretty much my original two-liner (there's a minor variation to handle SQL special characters that I've noticed are in the data, eg #)

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

Search as type subform example

 

[JanaLNelson]

OK, I'm going down the subform route. It'll take me a minute.

 

[JanaLNelson]

I have a form frm_Search and a subform sfrm_Search
On the form is txtSearchDesc which should filter strDescription on the subform as you type.
The form is unbound, the subform table is dbo_IN_Master.

May we start from the beginning,please as I am totally confused about the code.

 

[JanaLNelson]

Seems to be working

 

[strongm]

Hurrah

 

[JanaLNelson]

HURRAH INDEED!!
Thanks for the example database - very helpful!

Well, (forgive me), it works great if I key in the description exactly, starting with first character and filtering merrily along in sequence.

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).
There 1024 screws in inventory so you see how she would want this feature.
OK, I'm ready.

 

[strongm]

Yep, fairly straightforward, but unfortunately I am now otherwise occupied, so sadly there'll be a delay in providing any ideas.

 

[JanaLNelson]

Andy! I forgot to say that I am seriously considering your added cmd button idea if I can't make this thing do what I want.
That's a good workaround; I never would have thought of it.
I am awfully stubborn but sometimes I have to compromise.
If I can get these people's Sales Order/Purchase Order database finished, I can retire.
I never thought this one issue was going to be a deal breaker.
Do you hire out?
Jana

 

[JanaLNelson]

strongm!
Thank you for all your time and attention.
I am very grateful - you have given me much to go on.
Cheers and carry on!
Jana