Listbox Search by the second column

[tsluu]

I have a listbox with 2 columns, Surname and FirstName.

I have the listbox ordered by the Surname column. I am able to press the
letter "g" and move to the first customer whose surname began with that
letter. Access listbox does that kind of search for the first column in the
listbox but I wanted to be able to do the same with the FirstName, the second
column.

Any idea how to do that? Is it possible?

 

[Mrs. Ugh]

If you mean you want to enter the entire last name and the start on the first
(smith, j - too many Smiths, so you want to enter the first name too) you can
create a listbox where the list data =[Surname] & ", " & [FirstName]. If you
mean you want to search by first name only (only remember the first name, but
can find the last name in a list) I would create another listbox with the
FirstName in the first column.

 

[Dirk Goldgar]

I have a listbox with 2 columns, Surname and FirstName.

I have the listbox ordered by the Surname column. I am able to press the
letter "g" and move to the first customer whose surname began with that
letter. Access listbox does that kind of search for the first column in
the
listbox but I wanted to be able to do the same with the FirstName, the
second
column.

Any idea how to do that? Is it possible?

It would require some VBA code. Here's a quick routine I just put together
to do this using the Alt+<letter> combination to search the second column of
the combo box in a similar way to the built-in behavior, leaving the
built-in behavior intact. So if you press G, it searches the first column
for the next entry starting with "g", but if you press Alt+G, it searches
the second column instead.

I'm using the list box's KeyUp event to do this:

'----- start of code -----
Private Sub List0_KeyUp(KeyCode As Integer, Shift As Integer)

Dim strFind As String
Dim I As Long
Dim blnFound As Boolean

' We only care about Alt+<letter>
If Shift = acAltMask Then
If KeyCode >= vbKeyA And KeyCode <= vbKeyZ Then

strFind = Chr$(KeyCode) & "*"

With Me.List0

' Search from the current position downward.
For I = .ListIndex + 1 To (.ListCount - 1)
If .Column(1, I) Like strFind Then
blnFound = True
Exit For
End If
Next I

' If we didn't find the letter we wanted,
' search from the top to the current position.
If Not blnFound Then
If .ListIndex > Abs(.ColumnHeads) Then
For I = Abs(.ColumnHeads) To (.ListIndex - 1)
If .Column(1, I) Like strFind Then
blnFound = True
Exit For
End If
Next I
End If
End If

' If we found the letter, go to that list item.
If blnFound Then
.ListIndex = I
End If

End With

End If
End If

End Sub
'----- end of code -----