Use the ListIndex Property to Store Primary Keys From a Recordset

Use the ListIndex Property to Store Primary Keys
From a Recordset
Here’s an easy way to fill a listbox or combobox with names, then
retrieve the UserID of that name. This example loads names into a
listbox from a SQL Server stored procedure. When you click on a
name in the listbox, the Key value is stored in the lngUserID
variable. Then you can use the lngUserID variable in other parts of
the program to retrieve related information for the selected name.
The names are set up as character fields with the UserID being an
AutoNumber field and also the primary key. This tip is valid only
if you can translate the field value to a number:
Private Sub Form_Load()
    Call LoadData(List1)
End Sub
Private Sub List1_Click()
     If List1.ListIndex>=0 Then
     lngUserID = _
         List1.ItemData(List1.ListIndex)
End If
End Sub
Private Sub LoadData(ByRef obj As Object)
' Assumes the Object is either a ListBox or ComboBox
    Dim com as ADODB.Command
    Dim rs as ADODB.Recordset
    Set com=CreateObject("ADODB.Command")
    Set rs=CreateObject("ADODB.Recordset")
    com.CommandText = "procGetData"
    com.CommandType = adCmdStoredProc
    com.ActiveConnection = strConnect
    Set rs=com.Execute
    obj.Clear
    Do While Not rs.EOF
        obj.AddItem rs!Name
        obj.ItemData(obj.NewIndex) = rs!UserID
        rs.MoveNext
    Loop
    rs.Close
    set rs=Nothing
    set com=Nothing
End Sub