Database - How can I fill a combobox with values in a database?-VBForums
Results 1 to 1 of 1

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

Threaded View

  1. #1

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,030

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

    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:
    1. .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, _ ).
    Last edited by si_the_geek; Jan 19th, 2009 at 12:58 PM. Reason: updated link

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.