Move cursor to the value in the listbox 1

[Viktoria2011]

I have a listbox with the names of the employees and their trades. The client wants the cursor move to the value in the control based on several characters entered, ie. when looking for employee name Robert ..., type in "rob" and the first name with with "rob" at the beginning of the name is selected. Currently, when you type "rob", you get first everybody with the names starting with "r" then everybody with their names starting with "o" etc. What needs to be done similiar to how the combobox acts when entered multiple characters. The client does not want to enter sought for text in a separate text box or use combobox. Help.

 

[MajP]

Use a MsForms listbox instead of the Access listbox. It has a property to match entire selection.
I tried to fake this with an Access listbox, and I could not do it. I could get it to move to the correct choice but then it would move off of the choice. Example if I type BO it would move to Bob, but then it would move to the first word with an O. I do not think you can turn that feature off.

 

[TheAceMan1]

How are ya Viktoria2011 . . .

This was a tough one. At 1st I was getting the results [blue]MajP[/blue] mentioned. Using the [blue]On Key Press[/blue] event of the listbox I kept getting the right index but the wrong value selected. Drove me banannas. Everything is fine until I set the [blue]Selected[/blue] property. Just not gonna work in the [blue]On Key Press[/blue] event.

I decided that perhaps the event just needed to finish and the [blue]Selected[/blue] property set elsewhere. With use of the forms [blue]On Timer[/blue] event I was able to get it to work. The code uses a recordset so the [blue]rowsource[/blue] of the listbox needs to be a [blue]query name[/blue] or an [blue]SQL statement[/blue]. Now the code:
[ol][li]In the declaration section of the form add the following line:

[blue]Private idxLBx As Long [green]'Hold index for selection.[/green][/blue]

[/li]
[li]In the [blue]On Timer[/blue] event of the form, copy/paste the following:

[blue]   If idxLBx <> -10 Then
      Me.[purple][B][I]List36[/I][/B][/purple].Selected(idxLBx) = True [green]'Set Selected[/green]
   Else
      Me.[purple][B][I]List36[/I][/B][/purple] = Null [green]'Clear Listbox[/green]
   End If[/blue]

[/li]
[li]Finally ... in the [blue]On Key Press[/blue] event of the form, copy/paste the following:

[blue]   Dim db As DAO.Database, rst As DAO.Recordset
   Dim Cri As String, LBx As ListBox
   Static Keys As String 'Keys held here
   
   Set db = CurrentDb
   Set rst = db.OpenRecordset(Me.List36.RowSource, dbOpenDynaset)
   Set LBx = Me.[purple][B][I]List36[/I][/B][/purple]
   rst.MoveFirst
   LBx = Null
   
   If KeyAscii <> 27 Then
      Keys = Keys & Chr(KeyAscii)
      Cri = "[ContactName] LIKE '" & Keys & "*'"
      rst.FindFirst Cri
      
      If Not rst.NoMatch Then
         idxLBx = rst.AbsolutePosition
         Me.TimerInterval = 10
      End If
   Else
      idxLBx = -10
      Keys = ""
      Me.TimerInterval = 10
   End If
   
   Set LBx = Nothing
   Set rst = Nothing[/blue]

[/li]
[li][purple]Be sure to remove, disable or remout any prior code you may have for this to prevent interaction![/purple][/li]
[li]Special Note:All you have to do is set focus to the listbox and start typing. The escape key (ESC) is programmed to clear any selections so you can start over.[/li]
[li]Also ... in any of the code replace [purple]List36[/purple] with your listbox name.[/li]
[li]And incase I havn't said it directy ... were talking a standard access listbox here![/li][/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

Nice idea with the timer.

 

[TheAceMan1]

Thanks [blue]MajP[/blue] ...

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

Viktoria201 . . .

[blue]Change:
   Set db = CurrentDb
   Set rst = db.OpenRecordset(Me.[purple][B][I]List36[/I][/B][/purple].RowSource, dbOpenDynaset)
   Set LBx = Me.Me.List36

To:
   Set db = CurrentDb
   Set LBx = Me.Me.[purple][B][I]List36[/I][/B][/purple]
   Set rst = db.OpenRecordset([b]LBx[/b].RowSource, dbOpenDynaset)[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

AViktoria2011 . . .

And for the timer add the following line in purple:

[blue]   If idxLBx <> -10 Then
      Me.List36.Selected(idxLBx) = True 'Set Selected
   Else
      Me.List36 = Null 'Clear Listbox
   End If
   
   [purple][b]Me.TimerInterval = 0[/b][/purple][/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]