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.
Code:
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:
VB Code:
.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:
Code:
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, _ ).