Find as you type in a listbox

[Alisa]

I have a single select list box (Access 2003) that is not behaving the way I want it to.

This is what my list box does now:
User types "Do"
List box goes to the first item in the list begining with "D"
Then it goes to the first item in the list begining with "O"

This is what I want my list box to do:
User types "Do"
List box goes to the first item in the list begining with "Do"

Calling all Access World geniuses, Is this possible?

Further rambling explanation for anyone who is not bored yet:

I have seen the samples where you put a text box on your form, and type what you are looking for in there, and then it filters the list based on what you type. But I don't want a separate box and I don't want to filter the list, I just want to highlight the item as the user types while the focus is on the list box.

The reason I don't want to do it the other way is because the user needs to be able to scroll up and down the list box using the arrow keys, and to pick their final selection by hitting the enter or tab key (captured in the key down event of the list box). If the focus is instead on a separate text box, then the user will have to go to the mouse to get between the separate text box and the list box, which defeats the whole purpose.

 

[RuralGuy]

I don't know where you type on a list box but if it is a ComboBox then setting the AutoExpand property to YES will make it act as you describe.

 

[Alisa]

I know but list boxes don't have an autoexpand property. I can't use a combo box for this because the whole list needs to be visible. By typing on the list box I mean typing while the focus is on the list box.

 

[boblarson]

I can't use a combo box for this because the whole list needs to be visible.

Why does the whole list need to be visible? Are you using multiselect?

If you need to use a list box, you can put a text box to type in above it and filter in the on change of the text box.

 

[RuralGuy]

I know but list boxes don't have an autoexpand property. I can't use a combo box for this because the whole list needs to be visible. By typing on the list box I mean typing while the focus is on the list box.

AFAIK there is nothing on a ListBox to accept your keyboard input. How about Bob's suggestion?

 

[Alisa]

So I gather the answer is no, what I want can't be done. We are using a list box because there are anywhere from 300 to 600 items in the list, and scrolling is just more manageable with a nice long list box than an itty bitty combo box. I can't put a text box above it and filter because the focus needs to remain on the listbox so they can hit enter when the item they want is selected and so they can use the arrow keys to move the list up and down. If there is really no way to do it, I guess I will just have to get them compromise on one thing or another . . .

 

[boblarson]

So I gather the answer is no, what I want can't be done. We are using a list box because there are anywhere from 300 to 600 items in the list, and scrolling is just more manageable with a nice long list box than an itty bitty combo box. I can't put a text box above it and filter because the focus needs to remain on the listbox so they can hit enter when the item they want is selected and so they can use the arrow keys to move the list up and down. If there is really no way to do it, I guess I will just have to get them compromise on one thing or another . . .

A combo doesn't have to be itty bitty. In fact, you can set the

List Rows = 75
List Width = 3.5"

with that you will see when you drop down that you get 75 (or however many rows will fit in your screen) rows displaying in the drop down. You can play with that number to display as many as will fit on your screen. You do not need to stay with the default of 8. In fact, I use that frequently to give the users a big list to choose from. And the list width property which is set to Auto by default can be set to wider than the actual combo box so if you select from it, it will show the drop down portion as wider than the actual control.

 

[Alisa]

wow I never knew about that property! I am going to go convert to combo right now and see if it works.
Thanks Bob

 

[GAB]

Why does the whole list need to be visible? Are you using multiselect?

If you need to use a list box, you can put a text box to type in above it and filter in the on change of the text box.

Bob, Could please explain a little more about multiselect?

Thanks

Gary

 

[boblarson]

Bob, Could please explain a little more about multiselect?

Thanks

Gary

For list boxes you can set the multi-select property to Simple or Extended. However, you can't bind a multi-select listbox to a field, because once you set it to multi-select it no longer has a value. If set to multi-select you need to iterate through the selected items via code to use the results.

 

[Alisa]

Well despite that wonderful revelation about combo boxes, I hit another dead end, its a very very long story which I will not even attempt to explain.

In any case, I did figure out a way to sort of do what I wanted, so I am posting back with my solution in case anyone else has the same question in the future. I put an invisible text box next to my list box. Then, in the onkeyup event of my list box, I evaluated the keycode to get what the user typed, i.e., keycode=67 then they typed A, etc. Then I took that text and put it in my invisible text box. Then I did a for . . .each loop to go through every item in my list box, stopping at the first one where the first however many characters matched the text in my invisible text box. It works pretty well, the only annoying thing is that access insists on trying to find the item first. So if I type RE, the list box jumps back up to the first item starting with E for a nanosecond before jumping back down to the first item starting with RE.

 

[peter.fruzynski@worl]

Alisa, I have the same problem, do you have any sample for invisible text box. I want to see if I can implement something similar

 

[Alisa]

Alisa, I have the same problem, do you have any sample for invisible text box. I want to see if I can implement something similar

I don't have time to pick apart my app and make a sample, but this is how you do it:

Put a text box on your form (mine is named txtfind). My list was named lstOptions. Then you need some code in your form like this:

Private Sub lstOptions_KeyUp(KeyCode As Integer, Shift As Integer)
Select Case KeyCode
Case vbKeyBack
    Me.txtfind = Left(Me.txtfind, Len(Me.txtfind) - 1)
    MoveTo
Case Else
If Me.txtfind = "" Then
    Me.txtfind = GetLetter(KeyCode)
Else
    Me.txtfind = Me.txtfind & GetLetter(KeyCode)
    MoveTo
End If
    Exit Sub
End Select
End Sub

GetLetter is a function I wrote to translate the keycode into the letter - you can look up those constants in the help and write code for whichever ones you need.
Here is the function that finds what you want on the list:

Public Function MoveTo()
      'start where we are in the list and go forward
      Dim iLength As Integer
      Dim irow As Integer
     iLength = Len(Me.txtfind)

     For irow = 0 To Me.lstOptions.ListCount - 1
     Me.lstOptions.ListIndex = irow
         If Left(Me.lstOptions.Column(1), iLength) = Me.txtfind Then
             Exit Function
         End If
    Next
End Function

I hope that helps you out . . .

Once you get it working, then you set the text box to invisible, and your users will never know it is there.

 

[peter.fruzynski@worl]

Alisa,

Thanks for your reply.

I tried to write the Function for GetLetter, but have not had any success, here is what I wrote

Public Function GetLetter(KeyCode As Integer)
Dim varFruz As Variant
If KeyCode = 49 Then GetLetter = 1
If KeyCode = 59 Then GetLetter = 9
If KeyCode = 65 Then GetLetter = A
If KeyCode = 80 Then GetLetter = P
varFruz = GetLetter
MsgBox ("varFruz = " & varFruz)
Exit Function

End Function

The GetLetter seams to be empty. Can you please see where I went wrong and how should I fix it.

Peter

 

[Alisa]

Try something like this:

Public Function GetLetter(KeyCode As Integer)
Dim varFruz As string
select case keycode
case 49
varfruz = 1
case 59
varfruz=9
etc.
end select

MsgBox ("varFruz = " & varFruz)
GetLetter=varfruz
Exit Function

End Function

Peter

 

[peter.fruzynski@worl]

Alisa,

Many thanks for your help. It worked and your help was greatly appreicated. Some additional tips as follows:

1. For alphabetic letter ensure the letter is in quotation marks example

Case 65
varFruz = "A"

2. I emptied txtFind by using the lost focus event. Not sure it lost focus event is the best, but I will try it for awhile.

Private Sub lstTagAvailable_LostFocus()
Me.txtFind.Value = ""
End Sub

3. In the Public Function MoveTo(), I added the additional If Then Else statement , so the the listbox scrolls to the middle, where 9 is about half the number of rows in the list box.


Public Function MoveTo()
'start where we are in the list and go forward
Dim iLength As Integer
Dim irow As Integer



iLength = Len(Me.txtFind)
For irow = 0 To Me.lstTagAvailable.ListCount - 1
If irow + 9 < Me.lstTagAvailable.ListCount - 1 Then
Me.lstTagAvailable.ListIndex = irow + 9
Else
Me.lstTagAvailable.ListIndex = Me.lstTagAvailable.ListCount - 1
End If
Me.lstTagAvailable.ListIndex = irow
If Left(Me.lstTagAvailable.Column(0), iLength) = Me.txtFind Then
Exit Function
End If
Next


End Function

 

[wornways]

This looks like a great solution, which I've tried to implement. But for me, in Access 2007, Me.ListboxName.Column(1) always returns Null. Actually I want .Column(2), but any column returns Null. I can't figure out why. :banghead:

 

[Isskint]

You are changing ListboxName to the name of your listbox??

Your listbox does have more than 1 column declared in the listboxes properties?

 

[wornways]

You are changing ListboxName to the name of your listbox??

Your listbox does have more than 1 column declared in the listboxes properties?

Well I just used "ListBoxName" to indicate the name of the control. But, it's actually called "Submission". Not sure if this makes a difference though. Here's the code I've got:

Public Function GetLetter(KeyCode As Integer) As String
   Dim isLetter As String
   'If shift = 0 Then KeyCode = KeyCode + 32
   
   Select Case KeyCode
   Case 48
      isLetter = "0"
   Case 49
      isLetter = "1"
   Case 47
      isLetter = "2"
   'etc.
   Case 65
      isLetter = "A"
   Case 66
      isLetter = "B"
   Case 67
      isLetter = "C"
   'etc.
   End Select

   GetLetter = isLetter
Exit Function

End Function

Public Function MoveTo()
   'start where we are in the list and go forward
   Dim iLength As Integer
   Dim irow As Integer
   iLength = Len(Me.TxtFind)
   For irow = 0 To Me.Submission.ListCount - 1
      Me.Submission.ListIndex = irow
      If Left(Me.Submission.Column(2), iLength) = Me.TxtFind Then
         Exit Function
      End If
   Next
End Function

Private Sub Submission_KeyUp(KeyCode As Integer, Shift As Integer)
   Select Case KeyCode
   Case vbKeyUp, vbKeyDown, vbKeyRight, vbKeyLeft
      Exit Sub
   Case vbKeyEscape
      Me.TxtFind = ""
   Case vbKeyBack
      Me.TxtFind = Left(Me.TxtFind, Len(Me.TxtFind) - 1)
      MoveTo
   Case Else
      If Me.TxtFind = "" Then
         Me.TxtFind = GetLetter(KeyCode)
      Else
         Me.TxtFind = Me.TxtFind & GetLetter(KeyCode)
      End If
      MoveTo
   End Select
End Sub

(continued in next post due to text restriction???)

 

[wornways]

Needless to say, I'm merely in the testing phase right now. I added a case for the arrow keys to let me navigate up and down the list entries without triggering the rest of the code. I added a case for the Esc key to use for resetting the field quickly and easily. This code actually works. The existing case for the Backspace button also works. Note that in MoveTo the correct number of rows is returned by Me.Submission.ListCount. But, Submission.Column(N) returns Null every time. The Submission field is populated with the 550+ entries already, so I don't know why Null would be returned when I can see with my two eyes that the Submission field is populated.

So, near as I can tell, it all seems to be working, except for the fact that Null is being returned instead of the item in Submission.Column(N).

Further note. When I tried this same code with Submission as a ComboBox (my preference), it also didn't work, but this was because it was not possible to assign a value to Submission.ListIndex. However, amazingly, when I stepped through the code I saw that Submission.Column(N) actually returned the value of the current ListIndex, even if its value was ostensibly fixed at -1. But, now that I'm using ListBox instead of ComboBox, I get Null, Null and only Null. :banghead:

Any help or ideas would be vastly, vastly appreciated.

(I had this nicely formatted but I was told there was a "link" that had to be removed despite there not being a link, so I removed all formatting so I could post.)