Question about some ComboBox code 1

[nakedbamboo]

I have a combo box where the user chooses the state. I would like to have it act like the combo box everyone is familiar with where if you hit the same letter, it cycles through the choices in that letter. For example, if I hit "O" twice I want it to cycle to Oklahoma instead of typing "OO" in the box, or if I hit it one more time, I want it to cycle to Oregon. I was told I would have to code this as Access does not have this function. I think I have a pretty good start on it, but have run into a snag with setting the value of the combobox. Below is the code so far:

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

Dim Letter As String
Dim FirstLetter As String
Dim Index As Integer

Index = State.ListIndex
Letter = UCase(Chr(KeyCode))
If Letter <= &quot;Z&quot; And Letter >= &quot;A&quot; Then
    If (Letter = Left(State.ItemData(Index), 1)) Then
        Index = Index + 1
    Else
        For Index = 0 To 50
            FirstLetter = Left(State.ItemData(Index), 1)
            If (Letter = FirstLetter) Then
                Exit For
            Else
            End If
        Next Index
    End If
    State = State.ItemData(Index)
Else
End If
MsgBox (State.ListIndex)

End Sub

The problem comes at the end when I try to set the value of the combobox equal to the current indexed value. It is not doing this. I have tried using the .Value, .Text, among other things to set this value equal to (State.ItemData(Index)). The msgbox at the end displays the next Index number as hoped for and while the msgbox is showing, the correct state is even shown in the combobox, but as soon as I close the msgbox, the combobox resets to the first state of that letter. By the way, the States are stored as a value list in the combobox to which it is limited. Thanks for any help.

 

[rdodge]

You will need to use the ListIndex one step further along with using the Column Property on the combo-box.

Let's say you have the listbox with the following properties set:

ColumnCount = 2
BoundColumn = 1
ColumnWidth = 0;2

Make sure that the AutoExpand is set to No (False or 0)

The columns are setup as State ID, State Name.

Dim CurItem as Long, LastItem as Long, I as Long

CurItem = State.ListIndex
LastItem = State.ListCount - 1
If CurItem < LastItem Then
For I = CurItem + 1 To LastItem Step 1
If Left(State.Column(2,I),1) = UCase(Chr(KeyCode))
State.ListIndex = I
State.Value = State.Column(2,I)
End If
Next I
End If
If State.ListIndex = CurItem Then
For I = 0 to CurItem
If Left(State.Column(2,I),1) = UCase(Chr(KeyCode))
State.ListIndex = I
State.Value = State.Column(2,I)
End If
Next I
End If
If State.ListIndex = CurItem Then
'Opps, we didn't find a match.
End If

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[nakedbamboo]

Is this code for a listbox or a combobox? In the listbox there is no setting for autoexpand. Also, I was looking at the help on ListCount, and it says it works with a CommandBarComboBox. I looked that up and couldn't quite understand exactly what that was and if that is what I have. It is just a regular combobox as far as I know. Thanks for the help.

 

[rdodge]

You are right about the AutoExpand only being on a combo box, but isn't that what you originally asked for, how to manipulate a combo-box like how things are done with a combo box on a web site (rather written in ASP, Java, or HTML)? Therefore, the code that I wrote above is written for a combo-box.

The ListCount property is on both, a listbox and a combo-box (listbox portion of it). This property returns the number of items (Rows) that's within the listbox (or listbox portion of a combo-box).

 

[nakedbamboo]

Ok, I am a little confused about the terminology I think. In the first portion you mention:

&quot;Let's say you have the listbox with the following properties set:&quot;

Then you just said:

&quot;...listbox portion of a combo-box...&quot;

I guess I do not know what the listbox portion of the combobox is. I tried creating a combobox with the two colums entered directly as the row source, and also tried it with a two column table as the row source. Both times, the code does not appear to work right. It will jump to the first State of the pressed letter, but pressing it again just types the letter again in the text portion which is the problem I have been dealing with.
Using some message boxes, I was able to determine that no matter what key is pressed it always goes to the &quot;oops we didnt find a match.&quot; I am also getting some weird values for CurItem at the beginning of the function.

 

[rdodge]

When I mentioned listbox, I should have mentioned Combo-box, but it is still the listbox portion of a combo box. Basically speaking, a combo box is just that, it has taken all of the properties of a textbox, and all of the properties of a listbox, with just a few minor modifications to it.

I did notice a couple of minor mistakes:

Dim CurItem as Long, LastItem as Long, I as Long

CurItem = Me.State.ListIndex
LastItem = Me.State.ListCount - 1
If CurItem < LastItem Then
For I = CurItem + 1 To LastItem Step 1
If UCase(Left(State.Column(2,I),1)) = UCase(Chr(KeyCode))
Me.State.ListIndex = I
Exit For
End If
Next I
End If
If Me.State.ListIndex = CurItem Then
For I = 0 to CurItem
If Left(Me.State.Column(2,I),1) = UCase(Chr(KeyCode))
Me.State.ListIndex = I
Exit For
End If
Next I
End If
If State.ListIndex = CurItem Then
'Opps, we didn't find a match.
End If
'This will force the entry from not entering the letter in the textbox of the combobox.
KeyCode = 0

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[nakedbamboo]

Yes! This works perfect! Thanks a lot for the help. The only thing that was amiss was that you left a few THENs off and the COLUMN property starts at 0 so it was 1 instead of 2.... Again, thanks for the help.

One more minor question; how do I get it to tab out of the control now? The code kills all keypresses except for certain letters. Is there anyway to ignore the tab key?

 

[rdodge]

Yes, there is. The Tab key has the KeyCode of 9, so you can use the following:

If KeyCode <> 9 Then
KeyCode = 0
End If

The Enter KeyCode in VBA for both, the numpad Enter key and the Qwerty Keyboard is the value of 13. Supposedly, the keycode of 108 should work on the numpad, but it doesn't work like it suppose to as it returns 13 instead of 108 for the numpad Enter Key.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[nakedbamboo]

Thanks for the help.

 

[rdodge]

You welcome. Glad to be of help.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000