Database - How can I fill a combobox with values in a database?

[si_the_geek]

The following code assumes that you are already using ADODB code, and have a connection object which is already connected to the database.

If you do not have the above, please see this thread for help.

Dim strSQL as String    'Declare the variables we need 
Dim oRS as ADODB.Recordset
  Set oRS = New ADODB.Recordset

                           'Load the data
'** change this SQL to load the data you want.
  strSQL = "SELECT Colour FROM Colours"

'** change oConn to the name of your Connection object
  oRS.Open strSQL, oConn, adOpenForwardOnly, adLockReadOnly, adCmdText
                           'Fill the combo box (or ListBox)
'** change the name of the combo to the one you want to fill
  With cboColour
    .Clear
    Do While Not oRS.EOF
'** change the name of the field here to the one you want to show
      .AddItem oRS.fields("Colour").value
      oRS.MoveNext
    Loop
  End With

                           'Tidy up
  oRS.Close
  Set oRS = Nothing

You can also include hidden (Integer) data for each item shown, such as an ID field - which is useful if two entries have the same text! You can add this by adding the extra field into your SQL, and inserting the following line immediately after the "AddItem" line:

.ItemData(.NewIndex) = oRS.fields("ColourID").Value

Note that all of the above could easily be written as a sub, so that it can be used from anywhere in your program that needs to fill a combobox with data. eg:

Public Sub FillCombo(objComboBox As ComboBox, _
                     oConn As ADODB.Connection, _
                     strSQL As String, _
                     strFieldToShow As String, _
                     Optional strFieldForItemData As String)
'Fills a combobox with values from a database

'Parameters:
  'objComboBox    = the ComboBox to fill
  'oConn          = the connection to the database
  'strSQL         = the SQL to load the data
  'strFieldToShow = the name of the field to show
  'strFieldForItemData (optional) = the name of the field to put into ItemData (Integer type fields only)

'Example usage (standard):
  'Call FillCombo(Combo1, oConn, "SELECT Colour FROM Colours", "Colour")

'Example usage (with ItemData):
  'Call FillCombo(Combo1, oConn, "SELECT Colour, ColourID FROM Colours", "Colour", "ColourID")


Dim oRS As ADODB.Recordset  'Load the data
  Set oRS = New ADODB.Recordset
  oRS.Open strSQL, oConn, adOpenForwardOnly, adLockReadOnly, adCmdText
                            
  With objComboBox          'Fill the combo box
    .Clear
    If strFieldForItemData = "" Then
      Do While Not oRS.EOF      '(without ItemData)
        .AddItem oRS.fields(strFieldToShow).Value
        oRS.MoveNext
      Loop
    Else
      Do While Not oRS.EOF      '(with ItemData)
        .AddItem oRS.fields(strFieldToShow).Value
        .ItemData(.NewIndex) = oRS.fields(strFieldForItemData).Value
        oRS.MoveNext
      Loop
    End If
  End With

  oRS.Close                 'Tidy up
  Set oRS = Nothing

End Sub

..to do the same for a ListBox, simply use the same code but replace the text "Combo" with "List"
(eg: Public Sub FillList(objListBox As ListBox, _ ).