title: VBA sql query based off combobox
body: <p>I've been searching hard the past few days and have come across numerous examples that outline what I'm trying to do.  However, I can't seem to get this working.  I have a combobox that populates data (a company name) from a table when the form initializes.  I then want to take the value chosen in the combo box and run another query to cross reference an id number in that same table. </p>
  
  <pre><code>    Private Sub CommandButton1_Click()
  
  
  
      Dim myCn As MyServer
      Set myCn = New MyServer
  
      Dim rs As ADODB.recordset
      Set rs = New ADODB.recordset
  
      Dim sqlStr As String
      Dim CompField As String
  
      'CompField = ComboBox1.Value
  
  
  
      sqlStr = "SELECT DISTINCT [acctno] FROM client WHERE [company] = '" &amp; ComboBox1.Text &amp; "'"
  
      'sqlStr = "Select DISTINCT [company] FROM client;"
  
     ' sqlStr = "SELECT DISTINCT [acctno] FROM client WHERE [company] = " &amp; UserForm1.ComboBox1.Value &amp; ";"
  
      'sqlStr = "SELECT DISTINCT [acctno] FROM client WHERE [company] = " &amp; UserForm1.ComboBox1.Text &amp; ";"
  
      'sqlStr = "SELECT DISTINCT [acctno] FROM client WHERE [company] = 'Company XYZ';"
  
      'sqlStr = "SELECT DISTINCT [acctno] FROM client WHERE company = " &amp; CompField &amp; ""
  
  
  
      rs.Open sqlStr, myCn.GetConnection, adLockOptimistic, adCmdText
  
       MsgBox sqlStr
      'MsgBox ComboBox1.Value
      'MsgBox rs(0)
  
  
  
      rs.Close
      myCn.Shutdown
  
      Set rs = Nothing
      Set myCn = Nothing
  
  
  End Sub
  </code></pre>
  
  <p>Currently with the combobox value encased in single quotes i get the entire sql string returned.  If I remove the single quotes I get a syntax error referencing part of the combobox value.  All other efforts have resulted in run-time errors that have led me nowhere.  </p>
  
  <p>I know my query works because I've tested it in SQL Studio and if I hard code a text value in this code I also get the Account ID I'm looking for.  Not sure what I'm missing here. </p>