CODEWITHSUNDEEP
sql-servervbacomboboxado
JLudt
JLudt

Reputation: 9

VBA sql query based off combobox

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. 

    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] = '" & ComboBox1.Text & "'"

    'sqlStr = "Select DISTINCT [company] FROM client;"

   ' sqlStr = "SELECT DISTINCT [acctno] FROM client WHERE [company] = " & UserForm1.ComboBox1.Value & ";"

    'sqlStr = "SELECT DISTINCT [acctno] FROM client WHERE [company] = " & UserForm1.ComboBox1.Text & ";"

    'sqlStr = "SELECT DISTINCT [acctno] FROM client WHERE [company] = 'Company XYZ';"

    'sqlStr = "SELECT DISTINCT [acctno] FROM client WHERE company = " & CompField & ""



    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

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.

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.

Upvotes: 0

Views: 1593

Answers (1)

JLudt
JLudt

Reputation: 9

Well I figured it out. I was expecting to be able to MsgBox the variable assigned to the SQL query and see my results. But it doesn't work that way. I had to use ADO GetString method for that and assign another variable. Oh and you were right about escaping, I had to add delimters to properly handle the ComboBox value in the query which ultimately looked like this

sqlStr = "SELECT DISTINCT [acct_no] FROM client WHERE [company] = " & Chr(39) & Me.ComboBox1.Value & Chr(39)

Thanks for your help on this

Upvotes: 0

Related Questions