stephenlecomptejr
asked on
List box that performs very similiar to a combo box's feature that as you type it pulls the closest match
I want to make a text box tied directly to a list box.
As you type a character entry into this text box - the list box will move down its list with the closest match of information.
Let's say you had the following list
01032
01033
01044
01047
01048
As you type 010 in the text box obviously it would first highlight 01032 in the list
But as you type 0104 it would move down to highlight 01044
Then as you type 01048 it would move down again to highlight it.
This would have to be done at every key press but <ENTER> or <TAB> or <SHIFT>-<TAB>
As you type a character entry into this text box - the list box will move down its list with the closest match of information.
Let's say you had the following list
01032
01033
01044
01047
01048
As you type 010 in the text box obviously it would first highlight 01032 in the list
But as you type 0104 it would move down to highlight 01044
Then as you type 01048 it would move down again to highlight it.
This would have to be done at every key press but <ENTER> or <TAB> or <SHIFT>-<TAB>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
No problem.
/gustav
/gustav
ASKER
works great
Thank you.
Thank you.
Very good. You are welcome!
/gustav
/gustav
ASKER
Please forgive me, cactus data.
I tried you're code awhile back and it worked.
But I can't find the example form that I used the above in.
And now I need some further clarification because its been too long.
OK so bear with me.
I don't think I need a type-ahead listbox as the example above.
What I need is that at the text box's keypress level - to adjust my listbox to the closest data matched up.
I think the above example only handles the keypress for a list box.
I didn't see any coding for a textbox.
And I swear that I pulled some code that made it work exactly as a so....
And I thought it was you. I had this hanging for so long.
Anyway please forgive me.
I tried you're code awhile back and it worked.
But I can't find the example form that I used the above in.
And now I need some further clarification because its been too long.
OK so bear with me.
I don't think I need a type-ahead listbox as the example above.
What I need is that at the text box's keypress level - to adjust my listbox to the closest data matched up.
I think the above example only handles the keypress for a list box.
I didn't see any coding for a textbox.
And I swear that I pulled some code that made it work exactly as a so....
And I thought it was you. I had this hanging for so long.
Anyway please forgive me.
I see. But you should be able to tweak the code to feed the keystrokes from the textbox into the listbox ...
/gustav
/gustav
ASKER
I've got txtCode as the text box and lstDemo as the listbox but changed to the following:
But I keep getting an error when I try to change the Me.Activecontrol to the listbox:
The expression On Key Down you entered as the event property setting produced the following error: Event procedure declaration does not match description of event having the same name.
I don't have anything highlighted.
Private Sub txtCode_KeyDown(KeyCode As Integer, KeyAscII As Integer, Shift As Integer)
' Search listbox with type-ahead like a combobox.
' 2001-12-14. Cactus Data ApS. CPH.
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lst As ListBox
Dim intKeyLen As Integer
Dim intColumnSearch As Integer
Set dbs = DBEngine(0)(0)
Set lst = lstDemo.ActiveControl
Select Case KeyAscII
Case vbKeyEscape
' Reset.
strLstSearch = vbNullString
Case vbKeyReturn, vbKeyTab
' Ignore.
Case vbKeyBack
' Cancel last key press.
intKeyLen = Len(strLstSearch)
If intKeyLen > 0 Then
strLstSearch = Left(strLstSearch, intKeyLen - 1)
End If
Case Else
strLstSearch = strLstSearch & Chr(KeyAscII)
' Inhibit normal stepping in listbox caused by key press.
KeyAscII = 0
End Select
' Parse lst.ColumnWidths to locate column to search or
' make a fixed choice (any column can be searched).
intColumnSearch = 1
intKeyLen = Len(strLstSearch)
If intKeyLen > 0 Then
' Search listbox.
Set rst = dbs.OpenRecordset(lst.RowS ource)
With rst
If .RecordCount > 0 Then
.FindFirst "" & .Fields(intColumnSearch - 1).Name & " Like '" & strLstSearch & "*'"
If .NoMatch Then
' Skip key entry.
strLstSearch = Left(strLstSearch, intKeyLen - 1)
Else
lst.Value = .Fields(lst.BoundColumn - 1).Value
End If
End If
.Close
End With
End If
Set lst = Nothing
Set rst = Nothing
Set dbs = Nothing
End Sub
But I keep getting an error when I try to change the Me.Activecontrol to the listbox:
The expression On Key Down you entered as the event property setting produced the following error: Event procedure declaration does not match description of event having the same name.
I don't have anything highlighted.
Private Sub txtCode_KeyDown(KeyCode As Integer, KeyAscII As Integer, Shift As Integer)
' Search listbox with type-ahead like a combobox.
' 2001-12-14. Cactus Data ApS. CPH.
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lst As ListBox
Dim intKeyLen As Integer
Dim intColumnSearch As Integer
Set dbs = DBEngine(0)(0)
Set lst = lstDemo.ActiveControl
Select Case KeyAscII
Case vbKeyEscape
' Reset.
strLstSearch = vbNullString
Case vbKeyReturn, vbKeyTab
' Ignore.
Case vbKeyBack
' Cancel last key press.
intKeyLen = Len(strLstSearch)
If intKeyLen > 0 Then
strLstSearch = Left(strLstSearch, intKeyLen - 1)
End If
Case Else
strLstSearch = strLstSearch & Chr(KeyAscII)
' Inhibit normal stepping in listbox caused by key press.
KeyAscII = 0
End Select
' Parse lst.ColumnWidths to locate column to search or
' make a fixed choice (any column can be searched).
intColumnSearch = 1
intKeyLen = Len(strLstSearch)
If intKeyLen > 0 Then
' Search listbox.
Set rst = dbs.OpenRecordset(lst.RowS
With rst
If .RecordCount > 0 Then
.FindFirst "" & .Fields(intColumnSearch - 1).Name & " Like '" & strLstSearch & "*'"
If .NoMatch Then
' Skip key entry.
strLstSearch = Left(strLstSearch, intKeyLen - 1)
Else
lst.Value = .Fields(lst.BoundColumn - 1).Value
End If
End If
.Close
End With
End If
Set lst = Nothing
Set rst = Nothing
Set dbs = Nothing
End Sub
ASKER
It might even be simple as doing it this way... but I keep stumbling on how to loop through the list box's data.
Private Sub txtCode_Change()
Dim VarItem As Variant
Dim myItem As String
For Each VarItem In Me.list1.ItemData
'do whatever here in the loop
If Left(Me.list1.Column(1, VarItem), Len(Trim(txtCode))) = Trim(txtCode) Then list1.ListIndex = VarItem
Next VarItem
End Sub
Private Sub txtCode_Change()
Dim VarItem As Variant
Dim myItem As String
For Each VarItem In Me.list1.ItemData
'do whatever here in the loop
If Left(Me.list1.Column(1, VarItem), Len(Trim(txtCode))) = Trim(txtCode) Then list1.ListIndex = VarItem
Next VarItem
End Sub
ASKER
I've got the code from originally but
if list1.listcount > 0 then
for i = 0 to list1.listcount - 1
if left(list1.list(i),len(tri m(text1.te xt))) = trim(text1.text) then
list1.listindex = i
exit for
endif
next 'i
endif
list1.list doesn't happen to be a property used in Access 97.
if list1.listcount > 0 then
for i = 0 to list1.listcount - 1
if left(list1.list(i),len(tri
list1.listindex = i
exit for
endif
next 'i
endif
list1.list doesn't happen to be a property used in Access 97.
It looks like it is much easier to use the OnChange event of the input textbox:
Private Sub txtInput_Change()
' Search listbox from textbox with type-ahead like a combobox.
' 2005-03-30. Cactus Data ApS. CPH.
Dim dbs As Database
Dim rst As Recordset
Dim lst As ListBox
Dim txt As TextBox
Dim intColumnSearch As Integer
Dim strSearch As String
Dim lngText As Long
Set dbs = DBEngine(0)(0)
Set lst = Me!lstDemo
Set txt = Me.ActiveControl
' Parse lst.ColumnWidths to locate column to search or
' make a fixed choice (any column can be searched).
intColumnSearch = 1
Set rst = dbs.OpenRecordset(lst.RowS ource)
With rst
If .RecordCount > 0 Then
strSearch = txt.Text
.FindFirst "" & .Fields(intColumnSearch - 1).Name & " Like '" & strSearch & "*'"
If .NoMatch Then
' Skip key entry and notify user.
lngText = Len(strSearch)
txt.Text = Left(strSearch, lngText - 1)
txt.SelStart = lngText
DoCmd.Beep
Else
' Set listbox to located match.
lst.Value = .Fields(lst.BoundColumn - 1).Value
End If
End If
.Close
End With
Me!txtSearch.Value = txt.Text
Set txt = Nothing
Set lst = Nothing
Set rst = Nothing
Set dbs = Nothing
End Sub
/gustav
Private Sub txtInput_Change()
' Search listbox from textbox with type-ahead like a combobox.
' 2005-03-30. Cactus Data ApS. CPH.
Dim dbs As Database
Dim rst As Recordset
Dim lst As ListBox
Dim txt As TextBox
Dim intColumnSearch As Integer
Dim strSearch As String
Dim lngText As Long
Set dbs = DBEngine(0)(0)
Set lst = Me!lstDemo
Set txt = Me.ActiveControl
' Parse lst.ColumnWidths to locate column to search or
' make a fixed choice (any column can be searched).
intColumnSearch = 1
Set rst = dbs.OpenRecordset(lst.RowS
With rst
If .RecordCount > 0 Then
strSearch = txt.Text
.FindFirst "" & .Fields(intColumnSearch - 1).Name & " Like '" & strSearch & "*'"
If .NoMatch Then
' Skip key entry and notify user.
lngText = Len(strSearch)
txt.Text = Left(strSearch, lngText - 1)
txt.SelStart = lngText
DoCmd.Beep
Else
' Set listbox to located match.
lst.Value = .Fields(lst.BoundColumn - 1).Value
End If
End If
.Close
End With
Me!txtSearch.Value = txt.Text
Set txt = Nothing
Set lst = Nothing
Set rst = Nothing
Set dbs = Nothing
End Sub
/gustav
ASKER
I'm going to try it on the weekend.
Thanks again.