title: Auto-Populating a Combobox with SQL data
body: <p>I'm new to this forum, so apologies if this has already been asked and I haven’t found it or if I’m posting in the wrong place.</p>
  
  <p>I have a routine that I have been using for years which populates a combobox based on data from SQL. I have a spread sheet with two comboboxes in it, the first combobox works fine and give a list of folder names straight from a SQL table. The second combobox is populated with all of the contracts that are associated with folder selected in the first combobox. However I can’t get the code to auto-populate, in order to get the combobox to update I have to select an item from the drop down list first and then the value isn’t held. I have pasted my code below:</p>
  
  <pre><code>Private Sub CB_Company_Change()
  
  With Application
       .Calculation = xlManual
       .EnableEvents = False
       .ScreenUpdating = False
   End With
  
  Dim stSQL As String, sBook As String, rst As ADODB.Recordset, k As Integer, vaData As Variant
  Dim objConn As ADODB.Connection
  Dim ConnectionString As String
  ConnectionString = "Provider=sqloledb.1;data source=sql-server;Initial catalog=sql-db;Integrated Security = SSPI;"
  
  Set objConn = New ADODB.Connection
  
  sBook = CB_Book.Value
  CB_Company.Clear
  
  stSQL = "EXEC('SELECT Name FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.ID = TABLE2.ID WHERE TABLE2.NAME = ''" &amp; sBook &amp; "''  ORDER BY TABLE1.NAME')"
  
  With objConn
      .CursorLocation = adUseClient
      .Open ConnectionString
      Set rst = .Execute(stSQL)
  End With
  
  With rst
      Set .ActiveConnection = Nothing
      k = .Fields.Count
      vaData = .GetRows
  End With
  
  CB_Company.List = Application.Transpose(vaData)
  
  objConn.Close
  
  Set rst = Nothing
  Set objConn = Nothing
  
  bClear = True
  
  With Application
      .Calculation = xlAutomatic
      .EnableEvents = True
      .ScreenUpdating = True
  End With
  
  End Sub
  </code></pre>
  
  <p>Any help on why this might not be auto populating when run would be much appreciated.</p>
  
  <p>As requested here is the code from the first combobox that gives me my book value:</p>
  
  <pre><code>Option Explicit
  Public bClear As Boolean
  
  Private Sub CB_Book_Change()
  
  With Application
      .Calculation = xlManual
      .EnableEvents = False
      .ScreenUpdating = False
  End With
  
  Dim stSQL As String, rst As ADODB.Recordset, k As Integer, vaData As Variant
  Dim objConn As ADODB.Connection
  Dim ConnectionString As String
  ConnectionString = "Provider=sqloledb.1;data source=SQL-SERVER;Initial catalog=SQL-DB;Integrated Security = SSPI;"
  Set objConn = New ADODB.Connection
  
  stSQL = "EXEC('SELECT NAME FROM TABLE2')"
  
  With objConn
      .CursorLocation = adUseClient
      .Open ConnectionString
      Set rst = .Execute(stSQL)
  End With
  
  With rst
      Set .ActiveConnection = Nothing
      k = .Fields.Count
      vaData = .GetRows
  End With
  
  CB_Book.List = Application.Transpose(vaData)
  
  objConn.Close
  
  Set rst = Nothing
  Set objConn = Nothing
  
  bClear = True
  
  With Application
      .Calculation = xlAutomatic
      .EnableEvents = True
      .ScreenUpdating = True
  End With
  
  End Sub
  </code></pre>