Jump to a Value in a Listbox

[Mark Wolven]

I'm not even sure what specifically to ask for, so - I'll describe it
as fully as I can:

I have a listbox in a form that has a lot of records in it (~18000)
that users need to select. When you type a letter - "A", the list
jumps down to the first record that begins with the letter that you
type.

With so many records, there can be a large number of records between
Aa___ and Aw___ - is there a way to get access to wait for a second
character? Or at least read the user input as 2 characters of they
type it fast enough?

As it is now, if the user types "AW" quickly, Access jumps to the
first W___ record.

Any thoughts?

 

[Anisah]

Funny you should ask... I just found the code today (was
looking to do the same). The code is posted at the
following link...

http://www.vbcity.com/forums/faq.asp?
fid=38&cat=ListBox/ComboBox

Good luck!
- anisah

 

[John Vinson]

I'm not even sure what specifically to ask for, so - I'll describe it
as fully as I can:

I have a listbox in a form that has a lot of records in it (~18000)
that users need to select. When you type a letter - "A", the list
jumps down to the first record that begins with the letter that you
type.

With so many records, there can be a large number of records between
Aa___ and Aw___ - is there a way to get access to wait for a second
character? Or at least read the user input as 2 characters of they
type it fast enough?

As it is now, if the user types "AW" quickly, Access jumps to the
first W___ record.

Could you consider using a Combo Box instead of a Listbox? The
autocomplete feature of a combo works in exactly the way you describe.
And with 18000 records, you lose the main benefit of listboxes over
combos (being able to see all the values onscreen at once).

 

[Graham Mandeno]

Hi Mark

Autocomplete has never been available for listboxes, so I too would ask
John's question about whether a combo would do the job.

However, being that I enjoy a challenge, I have been having a play and have
come up with the following possible code:

======== start code ============
Dim rsListbox As DAO.Recordset

Private Sub Form_Unload(Cancel As Integer)
If Not rsListbox Is Nothing Then
rsListbox.Close
Set rsListbox = Nothing
End If
End Sub

Private Sub List0_KeyDown(KeyCode As Integer, Shift As Integer)
Dim fSearch As Boolean
Select Case KeyCode
Case vbKeyA To vbKeyZ
txtSearch = txtSearch & Chr(KeyCode)
fSearch = True
KeyCode = 0
Case vbKeyBack
If Len(txtSearch) > 0 Then
txtSearch = Left(txtSearch, Len(txtSearch) - 1)
fSearch = Len(txtSearch) > 0
End If
KeyCode = 0
Case vbKeyDelete, vbKeyEscape
txtSearch = ""
KeyCode = 0
End Select
If fSearch Then
If rsListbox Is Nothing Then
Set rsListbox = DBEngine(0)(0).OpenRecordset(List0.RowSource)
End If
With rsListbox
.FindFirst "[Your Field] like """ & txtSearch & "*"""
If .NoMatch Then
Beep
If Len(txtSearch) > 0 Then
txtSearch = Left(txtSearch, Len(txtSearch) - 1)
End If
Else
List0.ListIndex = .AbsolutePosition
End If
End With
End If
End Sub
========== end code ============

It seems to work OK, but could do with some polishing. I used a textbox,
txtSearch, to hold the current search string, so that I could see what was
happening, but you could declare a module-level string variable instead.

You also probably want to clear the search string on certain events - for
example, Listbox_Enter and Listbox_Click.

Otherwise, have fun!