Visual Basic Programming Code Examples

Visual Basic > Database SQL Stuff Code Examples

Load ComboBox, ListBox from Database Table



Load ComboBox, ListBox from Database Table

'Generic Sub to fill  comboboxes, listboxes etc...
'usage: loadbox cboBoxName, "dbTableName", "dbIDField", 
'"dbDescriptionField" 
'Where: cboBoxName is the combo/listbox you want to populate, 
'dbTableName is the database table where you want to populate 
'from, dbIDField is the unique Id number for the fields (ie: 
'Customer_ID) and dbDescriptionField is a text field (ie: Customer_Name)


Public Sub LoadBox(cboBox As Object, strTB, IDField, descField As
String)

   On Error GoTo Errors
   
   Dim rs As Recordset 'Declare a recordset
   Dim sql As String   'Declare a string to hold the SQL statement

   cboBox.Clear 
   'Clear the combobox in question, in case it isn't 
   sql = "" 
   'Clear SQL in case there is another var named SQL somewhere
   'Setup SQL to select fields based on the values passed to the function
   sql = "SELECT " & IDField & ", " & descField & " FROM " & strTB
   'Open the recordset with the data returned by the SQL statement
   Set rs = db.OpenRecordset(sql, dbOpenForwardOnly)
   
   With rs
      
      Do Until .EOF 'Loop until the End of the recordset
         
         'Add the items, and thier corresponding ID's
         cboBox.AddItem rs(descField)
         cboBox.ItemData(cboBox.NewIndex) = rs(IDField)
         .MoveNext
      
      Loop
      
      .Close
      
      Debug.Print cboBox.Name & " was populated"
      
   End With '(rs)
   
   Set rs = Nothing 'Release the variable

   Exit Sub

Errors: 'Error handler
  
If Err.Number <> 0 Then

   MsgBox ("Error #: " & str(Err.Number) & Err.Description)
   Exit Sub

End If

End Sub