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

 

[Andrzejek]

Jana,
I think columns in your combo box start with 0, this text confuses Index and 'order' of the columns, so:

'Column Count 3 (Why? as far as I see it there are only 2 columns the nameKey and the Name columns)
- 3 columns: column(0), column(1), and column(2)
'Keyed on Column 1 (record primary key) (is that the Bound column?)
- Column 1 is column(0) I would guess
'Showing column 2 (user-readable data) column 2 width >0 (not sure what this means.)
- I take it as: [tt]column(2).width > 0[/tt]
'First and Second Column width=0 (does this conflict with the last request to set column 2 >0 ?)
- [tt]column(0).width = 0[/tt] and [tt]column(1).width = 0[/tt]

From ComboBox.Column property (Access):
Index - A long integer that can range from 0 to the setting of the ColumnCount property minus one.

Welcome to Tek-Tips

---- Andy

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

 

[JanaLNelson]

Hi Andy-
Thanks for your quick reply!

My customer's table has a text value ("Stock") as the record unique value and it needs to show in the combobox as well as ("Description") and a third field.
Description is the field I want to search on with the "filter as I type". I tried:
Stock (Column0).width>0
Description (Column1).width >0
QOH (Column2).width>0

If I can get the combobox to filter itself with this code (as I type), how do I filter the records on the form that this combobox is on as I type? Is that even possible?

The customer's existing program (an old, unknown program with inaccessible source code) does this perfectly but I cannot recreate it and they insist on it.

Thanks for any help!
Jana

 

[Andrzejek]

Looks to me the code you presented is using the last column to search on.
So, I would try:

Stock (Column0).width>0
QOH (Column[highlight #FCE94F]1[/highlight]).width>0
Description (Column[highlight #FCE94F]2[/highlight]).width >0

But that's just a guess...

---- Andy

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

 

[JanaLNelson]

Oh right!
I'm continuing to work on this.
Thanks and I will let you know if I get it going

 

[JanaLNelson]

Seems to work now.

Had to go with this to get it to search on Description:

Description (Column2).width >0 Column0
Stock (Column0).width>0 Column1
QOH (Column1).width>0 Column2
Price width >0 Column3

And then I added an AfterUpdate event to change the recordsource of the form to match the sql of the combobox and show just that record in order to add it to an order detail.

thanks for your help.
Jana

 

[Andrzejek]

Great!

Dim strText, strFind Not sure what the strText is?

Well, if you Search for [tt]strText[/tt] in your code, you will find that it is a variable used to keep the text from your Combo:
[tt]strText = Me.Combo0.Text[/tt]
and to retrieve single letters from it to create your [tt]strFind[/tt] to be used in your WHERE clause.

Just FYI, if you do not specify the TYPE of your variables, what you actually do is:
[tt]Dim strText [red]As Variant[/red], strFind [red]As Variant[/red][/tt]
so I would suggest to add:
[tt]Dim strText [blue]As String[/blue], strFind [blue]As String[/blue][/tt]

---- Andy

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

 

[JanaLNelson]

Thanks, OK, done. Combo box works to filter itself - no errors.
Then the event to filter the form and another to add to detail, etc.

This is a clumsy workaround that I am hoping the customer will tolerate in case I cannot find what they really want: a textbox on the form that filters the records on the form as I type (instead of filtering a combo box.)

Can you help?
I appreciate your time!!
Jana [Smile]

 

[Andrzejek]

what they [users] really want: a textbox on the form that filters the records on the form as I type (instead of filtering a combo box.)

I would try to place a text box ([tt]txtSearch[/tt] ?) on the form (next to your combo box) and move all logic (code) from
[tt]Private Sub Combo0_Change()[/tt]
to
[tt]Private Sub txtSearch_Change()[/tt]


---- Andy

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

 

[JanaLNelson]

Roger that! I'm on it tomorrow!

 

[Andrzejek]

I've just looked over the original code you posted, and I have some 'observations'.

The comments and logic states:
[tt]' When adding another character, remove the
' previous "*," otherwise you end up with
' "*g**w*" instead of "*g*w*."[/tt]

This means, if you have a long list of US States, and want to find Arizona, you may just type "r" and "z" and you will find your State, along with all other States that have "r" OR "z" in their name (Arizona Arkansas California Colorado Delaware Florida Georgia Maryland Missouri Nebraska New Hampshire New Jersey New York North Carolina North Dakota Oregon Rhode Island South Carolina Vermont Virginia West Virginia)

I may be wrong, but if I would have a list of US States, and want to find 'Arizona', I would just type '[tt]ari[/tt]' and I would expect to show just Arizona, and nothing else.

If I type 'new', I want to see New Hampshire, New Jersey, New Mexico, and New York. And not all States that have letters 'n', 'e', OR 'w' in their names.

[tt]' 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*'."[/tt]

This logic is even worse :-(

---- Andy

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

 

[strongm]

>to change the recordsource of the form
>move all logic (code) from
>Private Sub Combo0_Change()
>to
>Private Sub txtSearch_Change()

I think we can completely avoid the combobox (and get better performance than repeatedly changing the query).

Assuming the form's datasource is already set to the complete recordset that you are interested in, just set the form's filter to the contents of the textbox. E.g.

[COLOR=blue]Private Sub Text3_Change()
    Me.Filter = "SomeText like '" & Me.Text3.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[/color]

You'll probably need to tweak the Change event code to manage focus and correct SelStart and SelLength in the textbox.

 

[Andrzejek]

[blue][tt]Me.Filter = "SomeText like '[highlight #FCE94F]*[/highlight]" & Me.Text3.Text & "*'"[/tt][/blue]


---- Andy

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

 

[strongm]

Sure, if you like

 

[JanaLNelson]

Hi Y'all!

Well, I'm lost.
I was unable to transfer this code to the textbox, txtSearchDesc, (changing the references to textbox instead of combo box) and get it to work.
The filter as you type part is just what I want (if I wanted it to filter the combo box. Form is what needs filtered.)
Here is the code for the combo box which works.
I hope I am headed in the right direction. Thanks again for help!!!

[Private Sub cbo_SearchDesc_Change()

Dim strText, strFind
Dim i As Integer
Dim strSQL As String

' Get the text that the user has typed into the combo box editable field.
strText = Me.cbo_SearchDesc.Text      'CHANGED THIS TO Me.txtSearchDesc

' 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 = "strDescription Like '"
For i = 1 To Len(Trim(strText))
If (Right(strFind, 1) = "*") Then

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 dbo_IN_Master.strDescription AS Description, dbo_IN_Master.strStock AS Stock, dbo_IN_Master.decQuantityOnHand AS QOH, dbo_IN_Master.curPrice1 AS Price FROM dbo_IN_Master Where " & _
strFind & " ORDER BY strDescription;"

' Filter the combo list records using the new SQL statement. 'CHANGED THIS TO Me.RecordSource
Me.cbo_SearchDesc.RowSource = strSQL

Else
' Show the entire list.
strSQL = "SELECT dbo_IN_Master.strDescription, dbo_IN_Master.strStock, dbo_IN_Master.decQuantityOnHand, dbo_IN_Master.curPrice1 FROM dbo_IN_Master; "
Me.cbo_SearchDesc.RowSource = strSQL
End If

' Make sure the combobox is open so the user can see the items available on list.   I REMOVED THIS FOR THE TEXT BOX
Me.cbo_SearchDesc.Dropdown

End Sub ]

[/code]

 

[strongm]

Did you try my code at all?

 

[JanaLNelson]

strongm, yes, absolutely!

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

I got #Name? error on all the fields as soon as I key the first letter in txtSearchDesc
If I keep on typing the first word nothing else happens.
Also, I don't know about SelStart and SelLength in the textbox.

 

[strongm]

Ok, let's take this one step at a time:

The #Name error is related to the query being used, and my code doesn't touch the query. Typically you will see the #Name? error if the name of the control is also the name of a field in the record source (and the Control Source begins with "=")

So you may want to check your form design.

We'll look at step 2 when you've fixed that.

 

[JanaLNelson]

OK, let me look at that again.
The query of the form definitely contains strDescription and the other 4 fields as you can see.
As I typed Washer, the form did not filter at "w" or "wa" etc. or at all.
When I key "w", form should filter to all descriptions that begin with "w", when I key wa, should continue to filter to all the "wa" and etc.
I'm sorry that I am not getting this. Appreciate your patience.

 

[JanaLNelson]

OK strongm - you got me.
Forget the #Name? thing - Idiot mistake on my part.
However, the filter as you type doesn't work.
Only look at first picture (ha).
Jana