Combo box - navagating the list

  • Thread starter Thread starter Andy
  • Start date Start date
A

Andy

Using Access 2003...

I have a form with a combo box bound to a lookup table
for USA state values (AK, AL, AR, etc).

I want user to be able press "N" (or any letter) and
jump to the first instance of "N" (NC) on the list & then
to the next (ND), etc. with each "N" keypress until they
reach the one they want & then hit enter to select it.

Currently the first keypress jumps to the first state
beginning with that letter but a 2nd keypress of "N" just
puts "NN" in the field which of course is invalid.

Suppose I'd also need to handle an invalid letter press
("X" for example).

BTW, what is this feature called? I see this behavior
many places, but can't find out how to do it after hours
of google.

Andy
 
Just a thought...there is an option in the combo box
properties where you can limit to the list. At the least
this should prohibit the "NN" possibility from taking
place.
 
You can use the OnChange event to check the first letter which would take care
of someone starting with X. Then you could use the Not In List event to check
the two letters which would take care of someone entering NN.
 
I need to check I've under stood correctly.

1. You're Combo Box contains all the US State Abbreviations.

2. Restricting it to your example of using 'N' your Combo Box will contain

NC, ND, NE, NH, NJ, NM, NV, NY

3. If you press N whilst the Combo Box has focus you want to select NC, if you press N again you want to select ND, if you press N again you want to select NE etc..

4. Presumably if you press N once you've got to NY you want to go back to NC.

If this is what you want then post a reply and I'll get onto it. If it isn't could you provide more detail of what you do want.

Captain747480
 
I think you've understood my request - mostly. No need to
filter the list based on one key press to display just
the items that begin with "N". Always want all 50 state
abbrevs displaying in alpha order (tlkpState already
sorted).

Merely want to go from first "N" to next "N" with each
keypress of "N". So, yes, after reaching NY, user would
be back to NC (first occurance of "N").

Or could press any other letter to go to the first
occurance of a state starting with that letter.

Thanks,
Andy
-----Original Message-----
I need to check I've under stood correctly.

1. You're Combo Box contains all the US State Abbreviations.

2. Restricting it to your example of using 'N' your Combo Box will contain

NC, ND, NE, NH, NJ, NM, NV, NY

3. If you press N whilst the Combo Box has focus you
Click to expand...
want to select NC, if you press N again you want to
select ND, if you press N again you want to select NE
etc..
4. Presumably if you press N once you've got to NY you want to go back to NC.

If this is what you want then post a reply and I'll get
Click to expand...
onto it. If it isn't could you provide more detail of
what you do want.
 
I'm already doing that. This option certainly prevents
bad values from being stored in underlying table. But it
merely provides the warning "not in list" - it doesn't
allow user to cycle down the list based on single letter
keypress which looks at ONLY the first letter of the
state abbrev.

Suppose the On Keypress event is where I want to go, but
not sure of the coding of it.
 
I'm afraid it's stumped me. I've got most of the way but I can't get any further.

I hope someone else is able to help.
 
Andy said:
I think you've understood my request - mostly. No need to
filter the list based on one key press to display just
the items that begin with "N". Always want all 50 state
abbrevs displaying in alpha order (tlkpState already
sorted).

Merely want to go from first "N" to next "N" with each
keypress of "N". So, yes, after reaching NY, user would
be back to NC (first occurance of "N").

Or could press any other letter to go to the first
occurance of a state starting with that letter.
Click to expand...

Andy, I can solve this problem for you. Post a reply to this post if
you still need a solution and I'll get back to you when I'm done. I
don't think it should take too much time.

sparkane
 
hi sparkane. even if Andy doesn't still need a solution, will you post it
for the rest of us? i'd be really interested, and i bet others would too.
thanks! :)
 
tina said:
hi sparkane. even if Andy doesn't still need a solution, will you post it
for the rest of us? i'd be really interested, and i bet others would too.
thanks! :)
Click to expand...

Hey Tina. Below is a routine that does what Andy asked. I've modified
it slightly. Andy's request, taken strictly, would have disallowed
users the typical way of entering data into a combo box, which could
have its inconveniences. For example, a user who knew that he/she
wanted "NN" couldn't type two Ns, but would have to press N as many
times as necessary to navigate through all the N items prior to NN.

Since the goal is as few keystrokes as possible, I changed Andy's
behavior to occur only when the Shift key is depressed (without needing
therapy). Combo box behavior is normal when typing normally, and
adjusted by pressing Shift. (Actually, you'd probably want it a
different key, otherwise users might find it hard to type a capital
letter.)

The code below worked in my testing and works regardless of combo box
row source type. The behavior can be a little strange when the combo
box reaches the end of the list, because the .ListCount property seems
not to return the list's final row, but the row after the final row,
which is Null. Also, a better implementation of this kind of thing
would allow the user to go backwards. If you've passed your target by
accident, you have to go all the way back around.

If you have any questions, follow up to this post.

<CODE>
Option Compare Database
Option Explicit

Private miLastRow As Integer

Private Sub Combo0_KeyDown(KeyCode As Integer, Shift As Integer)

Dim bShift As Boolean
Dim i As Integer

bShift = ((Shift And acShiftMask) > 0)

Select Case KeyCode
Case vbKeyA To vbKeyZ 'User pressed a letter key.
If bShift Then ' Shift key engaged.

With Me.Combo0

' if we're at the end of the list, checking
' the next row will raise an error, so set
' the last row variable to zero.
If miLastRow = .ListCount - 1 Then miLastRow = 0

' check next value to see if it
' starts with this letter. If not,
' search again for the first such value.
If .ItemData(miLastRow + 1) Like Chr(KeyCode) & "*" Then
' we have a match.
miLastRow = miLastRow + 1
.ListIndex = miLastRow

Else
' we've passed the end of the list
' for the current KeyCode letter. Start
' from the beginning.
miLastRow = 0

' commence search for data starting
' with this letter.
For i = 0 To .ListCount
If .ItemData(i) Like Chr(KeyCode) & "*" Then
miLastRow = i
.ListIndex = miLastRow
Exit For

Else
End If

Next

End If

End With

KeyCode = 0 ' stop normal key function.

Else
End If

Case Else
'do the usual
End Select

End Sub
 
thanks sparkane! :)
i'll have to sit down and work with it as soon as i get time. hopefully
you'll keep track of this thread for awhile! <g>
 
tina said:
thanks sparkane! :)
i'll have to sit down and work with it as soon as i get time. hopefully
you'll keep track of this thread for awhile! <g>
Click to expand...

It'll be easiest to get my attention if you put "attn: spark" somewhere
in the subject. If the previous posts expire before you respond, it
makes it harder to pick up.

spark
 
Boy - and I thought this thread was long dead!

Thanks Spark for picking up on it & I will take a look at
your solution.

I used the USA state list in my example. Now it would be
a bit of a pain to hit "N" 8 times to get to "NY" - so
your option on the shift key has merit. But I have a
couple other places where I'd like to use it on some
relatively short (less than a 100 or so rows) lookup
lists too.

I've become accustomed to this behavior on so many drop
down boxes on web pages that I now like the way it works.

Thanks again,
Andy
 
hey, i finally tried it out. works great - thanks!
unfortunately for me, i almost always use combo boxes with a hidden primary
key column. so what i'm typing is not the same as the value returned by the
ItemData property. oh, well, the code will still be useful to a lot of
people, i'm sure; it was cool of you to work it out and post it. :)
 
Back
Top