how to update list of values in a combobox

[azolfaghari]

Hi,
I have a combo box which gets its values from sql server using a query which is called "get_query_reason", which works fine. Now I want to update combo box values based on a user selection, st string. Have written the code, but does not work:

Dim qDef As QueryDef
Dim Query As String
Dim st As String
Dim rs As Recordset
st = "SOV"
Set qDef = CurrentDb.QueryDefs("get_query_reason")
Query = "USE [EM_Tools_Ops] SELECT reason FROM dbo.QueryReason WHERE TYPE='" & st & "' ORDER BY reason ASC "
qDef.SQL = Query
Set rs = CurrentDb.OpenRecordset("get_query_reason")


Me.cmbQueryReason.Value = Null

Do While Not rs.EOF
Me.cmbQueryReason.AddItem rs(0)
rs.MoveNext
Loop

any help, will be appreciated.
Arvin

 

[pbaldy]

What does "does not work" mean exactly? Why not just set the query as the combo's row source (and use table/query as the type)? Is this a pass through query? If not, I don't know that you want the "USE [EM_Tools_Ops] " portion.

 

[nickthompson]

To add to Paul's valid response, I'd also suggest another way of doing this. You could set up a linked table to the query/table in question, and set the row source of the combo box to a SQL string that selects the data from that query/table. I think you'll need to requery it before you'll see the data change.