update values in combo dropdown from vb

[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

 

[namliam]

Query = "USE [EM_Tools_Ops] SELECT reason FROM dbo.QueryReason WHERE TYPE=""" & st & """ ORDER BY reason ASC "

or

Query = "USE [EM_Tools_Ops] SELECT reason FROM dbo.QueryReason WHERE TYPE='" & st & "' ORDER BY reason ASC "

 

[azolfaghari]

that part is working, forgotten to place ' in my posted text. when it tries to load data using rs, that does not work.

 

[namliam]

What "does not work"? What are you expecting to happen and what isnt happening?

A loooooot more details are required here.