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