ComboBoxes and Recordsets

I found myself writing a lot of code to populate a combo box from a database, and later retrieve the value from the combo box. Here are some simple functions, so that I won't have to write the same code again. Note that this code assumes that your are using the combobox as a dropdown list.

The code in this article was written by me, but I'm releasing it to the public domain, as it really is quite simple.

First we need a way to populate the combobox. This sub takes a recordset, a combobox, a string value that is the name of the recordset field with the ID value, and a string value that is the name of the recordset field containing the data to be displayed in the combobox. If you need to skip one particular ID, you pass that in the optional SkipID parameter.

Public Sub PopulateComboFromRST(rst As ADODB.Recordset, cbo As ComboBox, _
                                    IDField As String, TextField As String, _
                                    Optional ByVal SkipID As Long = -1)

' Populates a combo box from a recordset
' Sets ItemData to the field IDField
' TextField specifies the fieldname to populate the list itself

    Dim b As Boolean
    Dim i As Long

    ' clear the combo box
    cbo.Clear

    ' loop through the recordset
    Do While Not rst.EOF

        ' add each item
        i = rst.Fields.Item(IDField).Value
        If i <> SkipID Then
            cbo.AddItem rst.Fields.Item(TextField).Value
            cbo.ItemData(cbo.NewIndex) = i
        End If

        ' move to the next record
        rst.MoveNext

        ' set the flag if we've added any items
        b = True

    Loop

    ' set the combo position to the first item only if we've added items
    If b Then
        cbo.ListIndex = 0
    End If

End Sub

Now you need a way to retrieve the ID of the selected item from the combobox. This is quite straightforward. Supply a reference to the combobox, and this function will return the ID.

Public Function GetIDFromCombo(ByRef cbo As ComboBox) As Long

' Returns the ID stored in the ItemData property in a combobox
' returns 0 if combo is empty

    Dim r As Long

    If cbo.ListCount > 0 Then
        r = cbo.ItemData(cbo.ListIndex)
    End If

    GetIDFromCombo = r

End Function

Now you may need to select an item in the combobox by ID from code. This sub will do that.

Public Sub SelectComboItem(cbo As ComboBox, ByVal ID As Long)

' Sets the listindex property of the combobox to match the
' item with specified ID

    Dim i As Long

    If cbo.ListCount > 0 Then
        For i = 0 To cbo.ListCount - 1
            If cbo.ItemData(i) = ID Then

                ' we found the item so select it
                cbo.ListIndex = i

                ' and exit the loop
                Exit For

            End If
        Next
    End If

End Sub