?
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
?
441 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 51

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 51

Expert Comment

by:Gustav Brock
ID: 13546897
No problem.

/gustav
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 1

Author Comment

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

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 51

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 51

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

777 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