Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

List box that performs very similiar to a combo box's feature that as you type it pulls the closest match

Posted on 2005-03-11
11
Medium Priority
?
449 Views
Last Modified: 2008-03-17
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>
0
Comment
Question by:stephenlecomptejr
  • 6
  • 5
11 Comments
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 13519651
That is a type-ahead listbox.
This is not that difficult. You may be able to optimize the code slightly as to not open and close the recordset repeatedly, but for my purpose it was so fast that there was no need for it.

' * Declarations section:
' Search string for listbox.
Private strLstSearch As String
---

Private Sub lstDemo_KeyPress(KeyAscii 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 = Me.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.RowSource)
    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

---

Private Sub lstDemo_GotFocus()
  strLstSearch = vbNullString
End Sub

Private Sub lstDemo_LostFocus()
  strLstSearch = vbNullString
End Sub


/gustav
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 13544054
Please be patient on my reply with this one.
I'm going to try it on the weekend.
Thanks again.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 13546897
No problem.

/gustav
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 13644078
works great
Thank you.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 13644326
Very good. You are welcome!

/gustav
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 13655876
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.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 13655944
I see. But you should be able to tweak the code to feed the keystrokes from the textbox into the listbox ...

/gustav
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 13656138
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.RowSource)
    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
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 13656288
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
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 13656387
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(trim(text1.text))) = 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.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 13660151
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.RowSource)
  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
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question