Find as you type in a listbox

[Isskint]

can you post the SQL/RowSource for the combobox please?

 

[John Big Booty]

Late to this thread and only having read the title Perhaps this sample is doing the sort of thing you are after.

 

[wornways]

Late to this thread and only having read the title Perhaps this sample is doing the sort of thing you are after.

From what I can tell, the search utility you're sample creates wouldn't work with a multi-select ListBox. Or would it? Would it let me type in the a portion of the text in a given search field so that it would show up in the listbox as a multiple-select item with a checkbox next to it, let me fill the checkbox, and then type in something else so I could get another entry from the table and fill another checkbox?

 

[wornways]

can you post the SQL/RowSource for the combobox please?

It's a ListBox in its present configuration, made to hopefully work with the code I've shared. Here it is:

SELECT [Poems].[ID], [Poems].[Title], [Poems].[Year Completed], [Poems].[Blog Location] FROM Poems ORDER BY [Title];

I'm creating this database largely as an exercise to help me get to know Access, but I also plan to use this database actively since the spreadsheet I've been maintaining over the years to track my submissions has gotten way out of hand and has become cumbersome to use. Besides, I want to keep track of the places I submit to, how many submissions I've made to them, the poems themselves, and to be able to select poems for submission based on certain properties. For instance, some places won't accept poems over 40 lines long, so it's handy to be able to find all poems 40 lines or less in length. I've actually already completed the Poems table, and it's a beauty. I've completed the Publication Venues table, and it's a beauty. Now I want to get this Submissions table together with the history of all past submissions and ready to track future submissions.

So this gives you a little background as to how I'll use the table. Actually I think the database will become increasingly useful as I learn more.

 

[Isskint]

OK, thank you for that.
I think you will find the problem is related to which column you are looking at. In your code you have this line Left(Me.Submission.Column(2), iLength) = Me.TxtFind Then
This is looking at [Poems].[Year Completed] but i suspect it is supposed to be looking at [Poems].[Title] which is column(1). The reason? Columns start at 0. Columns.Count = 1 column less than number of fields in the list/combo box.

 

[wornways]

OK, thank you for that.
I think you will find the problem is related to which column you are looking at. In your code you have this line Left(Me.Submission.Column(2), iLength) = Me.TxtFind Then
This is looking at [Poems].[Year Completed] but i suspect it is supposed to be looking at [Poems].[Title] which is column(1). The reason? Columns start at 0. Columns.Count = 1 column less than number of fields in the list/combo box.

They all return Null. 0, 1, 2, 3, 4, 5, 6, 7, 10, 1000. Always Null. I think "2" was just the last one I tried. I stepped through the For loop about 30 cycles, too, changing the column index to see if I could find the one that wouldn't return Null. :-/ No luck. :banghead:

I've been feeling like I'm in the Twilight Zone ever since I encountered this problem. I rebuilt the table, query, and form from scratch, too, just in case something somehow got munged the first time.

 

[boblarson]

They all return Null. 0, 1, 2, 3, 4, 5, 6, 7, 10, 1000. Always Null. I think "2" was just the last one I tried. I stepped through the For loop about 30 cycles, too, changing the column index to see if I could find the one that wouldn't return Null. :-/ No luck. :banghead:

If you are using a multi-select list box (list box's Multi-Select property is set to either Simple or Extended) then you can't refer to it for the value. You have to iterate through the ItemsSelected collection.

 

[wornways]

If you are using a multi-select list box (list box's Multi-Select property is set to either Simple or Extended) then you can't refer to it for the value. You have to iterate through the ItemsSelected collection.

Alright, good to know. However, if the idea is to be able to jump around in the list box by typing in a few characters to get to the next selection, this kind of defeats the purposes if all you can look at are already selected items. :-/ It seems like MS has created a lot of unnecessary restrictions to what you can and can't do while not providing some extremely basic functionality like typing to jump focus to selections of interest, which to my brain seems like the natural thing to implement.

 

[wornways]

Maybe I can create a non-multi-select field that's invisible and use it in a way similar to the way the invisible text box is used. I'll explore this idea later, actually. Somehow I think this idea has merit. Yeah iterate through the non-multi-select ListBox while setting the index for the visible multi-select listbox. Might work.

 

[wornways]

Maybe I can create a non-multi-select field that's invisible and use it in a way similar to the way the invisible text box is used. I'll explore this idea later, actually. Somehow I think this idea has merit. Yeah iterate through the non-multi-select ListBox while setting the index for the visible multi-select listbox. Might work.

Okay, it worked, but because of MS's tendency to create random and unpredictable restrictions, I can't actually iterate through the SubmissionLookup Listbox when it's invisible. It has to be in focus. So it's kind of weird, slow, and flickery.

But, I'm supposing I could create an items collection in the code and bind to the Titles column of the Poems table and then iterate through that instead--If I knew how to bind that items collection thus. First, is this possible? Second, what's the basic syntax involved?

 

[wornways]

I'm beginning to see light at the end of this long dark tunnel:

Public Function MoveTo()
   Dim TitleData As ADODB.Recordset
   Dim SubLookup As Variant
   
   Set TitleData = CurrentProject.Connection.Execute("SELECT [Poems].[ID], [Poems].[Title] FROM Poems ORDER BY [Title];")
   SubLookup = TitleData.GetRows
   
   Dim iLength As Integer
   Dim irow As Integer
   iLength = Len(Me.TxtFind)
   For irow = 0 To Me.Submissions.ListCount - 1
      Me.Submissions.ListIndex = irow
      If Left(SubLookup(1, irow), iLength) = Me.TxtFind Then
         Exit For
      End If
   Next
End Function

This is actually working. Just gotta hash out a few more details and I think I'll be underway.

Actually, the discovery of CurrentProject.Connection.Execute("SELECT ...") I believe will let me do an incredible amount of crap I never dreamed possible. I have endless ideas forming in my brain already.

 

[wornways]

Okay, so this is for anyone else who ever must face this headache for any reason and who actually wants to accomplish something viable in doing so. Note that the name of my multiple-select Listbox is "Submissions". The name of the table I get the titles from is [Poems], a query for which is setup in its "Row Source" property.

Arrow keys continue to work as they're supposed to. Tab and Shift+Tab move onto the next or previous field as should happen without triggering all the code. Spaces can be included in the search, with the Enter key now serving the function of selecting/deselecting the current checkbox. So, as you type, the Submissions.ListIndex focus moves to where I want it to be. I have to quickly SetFocus to another control, TxtFind--which I'm not actually hiding so I can use it as a visual reference as I search the ListBox--and back to the Listbox in order for the dotted line highlight to show up inside the checkbox of the current ListItem as a visual aid (which corresponds to the search of the listbox). Once an item has been selected or deselected by pressing Enter, the Esc key can be used to reset the field so that another search of the listbox may be performed, and more entries selected in the same manner. Cntrl, Alt, Shift, and a few other keys will not trigger the code so as to prevent confusing flicker as you work in the Listbox. Search is case insensitive.

Here's what I've got thus far, which will probably end up with further refinements later, but is a very good basic model for anyone wanting to accomplish the same. This allows one to deal with very large numbers of entries in a multiple select Listbox, and I can't quite understand why this isn't the default behavior programmed into the control itself. Note that it is necessary to add "MicroSoft ActiveX Data Objects x.x Library" in Tools->References for the "ADODB.Recordset" reference to work at the head of MoveTo().

Option Compare Database

Public Function GetLetter(KeyCode As Integer, ByRef Shift As Integer) As String
   Dim isLetter As String
   
   Select Case KeyCode
   Case 32
      isLetter = " "
      KeyCode = 0
   Case 48: isLetter = "0"
   Case 49: isLetter = "1"
   Case 47: isLetter = "2"
   Case 48: isLetter = "3"
   Case 49: isLetter = "4"
   Case 50: isLetter = "5"
   Case 51: isLetter = "6"
   Case 52: isLetter = "7"
   Case 53: isLetter = "8"
   Case 54: isLetter = "9"
   Case 65: isLetter = "a"
   Case 66: isLetter = "b"
   Case 67: isLetter = "c"
   Case 68: isLetter = "d"
   Case 69: isLetter = "e"
   Case 70: isLetter = "f"
   Case 71: isLetter = "g"
   Case 72: isLetter = "h"
   Case 73: isLetter = "i"
   Case 74: isLetter = "j"
   Case 75: isLetter = "k"
   Case 76: isLetter = "l"
   Case 77: isLetter = "m"
   Case 78: isLetter = "n"
   Case 79: isLetter = "o"
   Case 80: isLetter = "p"
   Case 81: isLetter = "q"
   Case 82: isLetter = "r"
   Case 83: isLetter = "s"
   Case 84: isLetter = "t"
   Case 85: isLetter = "u"
   Case 86: isLetter = "v"
   Case 87: isLetter = "w"
   Case 88: isLetter = "x"
   Case 89: isLetter = "y"
   Case 90: isLetter = "z"
   Case 189: isLetter = "-"
   End Select

   GetLetter = isLetter
End Function

Public Function MoveTo()
   Dim TitleData As ADODB.Recordset
   Dim SubLookup As Variant
   
   Set TitleData = CurrentProject.Connection.Execute("SELECT [Poems].[ID], [Poems].[Title] FROM Poems ORDER BY [Title];")
   SubLookup = TitleData.GetRows
   
   Dim iLength As Integer
   Dim irow As Integer
   iLength = Len(Me.TxtFind)
   For irow = 0 To Me.Submissions.ListCount - 1
      Me.Submissions.ListIndex = irow
      If Left(SubLookup(1, irow), iLength) = Me.TxtFind Then
         Exit For
      End If
   Next
End Function

Private Sub Submissions_Enter()
   'Need this here to override default behavior for Enter key
End Sub

Private Sub Submissions_KeyDown(KeyCode As Integer, Shift As Integer)
   Me.TxtFind.BorderStyle = 1
   Me.TxtFind.BorderWidth = 0
   Select Case KeyCode
   Case vbKeyUp, vbKeyDown, vbKeyRight, vbKeyLeft, vbKeyShift, _
        vbKeyTab, vbKeyControl, 18: Exit Sub
   Case vbKeyEscape, vbKeyTab
      If KeyCode = vbKeyEscape Then KeyCode = 0
      Me.TxtFind = ""
      Me.TxtFind.BorderStyle = 0
   Case vbKeyBack
      If Me.TxtFind = "" Then Exit Sub
      Me.TxtFind = Left(Me.TxtFind, Len(Me.TxtFind) - 1)
      MoveTo
   Case vbKeyReturn
      If Me.Submissions.Selected(Me.Submissions.ListIndex) = True Then
         Me.Submissions.Selected(Me.Submissions.ListIndex) = False
      Else
         Me.Submissions.Selected(Me.Submissions.ListIndex) = True
      End If
      KeyCode = 0
Case Else
      If Me.TxtFind = "" Then
         Me.TxtFind = GetLetter(KeyCode, Shift)
      Else
         Me.TxtFind = Me.TxtFind & GetLetter(KeyCode, Shift)
      End If
      MoveTo
   End Select
   Me.TxtFind.SetFocus
   Me.Submissions.SetFocus
End Sub

This will not work on a multiple select ComboBox, however, for some reason that only an obscure, timid, balding, little man deep within the bowels of Microsoft could know.