Populate ComboBox using PassThrough Query

[RunTime91]

I can do this all day long using Linked Tables - But I want to populate a combobox by pulling the data directly from SSMS.

Below is one of about 20 different configurations - with a few different variations

Private Sub CmboOpnCls_AfterUpdate()

Dim StrSQL As String
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim Rs As ADODB.Recordset

  Set conn = New ADODB.Connection
  conn.ConnectionString = strConnection
  conn.Open

If Me.CmboOpnCls.Value = "Open" Then


 StrSQL = "SELECT ATCCallDescription, ATCCategory " & _
           "FROM Tri.ATCCallResults " & _
           "WHERE ATCCategory = '" & Me!CmboOpnCls & "'"


End If

Debug.Print StrSQL

Set Rs = conn.Execute(StrSQL)

Me.CmboClsdRsn = Rs.Fields("ATCCallDescription")
'Me.CmboClsdRsn.RowSource = Rs.Fields("ATCCallDescription")
'Me.CmboClsdRsn.RowSource = StrSQL
'Me.CmboClsdRsn.RowSourceType = StrSQL
 
End If

End Sub

Shown within the code above are just a few examples of the Me.CmboClsdRsn... code attempts
The top one currently being used pulls only the first record
I've also tried to tie the Rs and the RowSource directly to the SELECT Statement
My various attempts have pulled from nothing, to just the first record of the Rs, to an assortment of errors
According to Google - I have to do this in a loop??? Using .Items.Add - which I can't get to work either
Lastly - when I throw the DeBug.Print into SSMS - Of course it pulls the correct dataset



Help

Thanks All

[June7]

In Access VBA it would be .AddItem. And combobox RowSourceType property would have to be set for ValueList.

[RunTime91]

Hey June ~ Thank you for chiming in

The Combo is set for ValueList and I did try:
Me.CMboClsdRsn.AddItem = Rs
Me.CMboClsdRsn.AddItem = StrSQL

I'm not sure what to put after AddItem - I just know the AddItem is looking for an object so I thought the = Rs would work
But regardless of what I put I keep getting an Argument Not Optional error on the .AddItem

The control is unbound - Not sure what I'm doing wrong

[kd2017]

I went through something similar the other day, this might help https://youtu.be/0Qi_EGitNhk

Also, head's up, I was having issues trying to call a table valued user define function from vba until I figured out the dbSQLPassThrough option for DAO OpenRecordset.

[June7]

Loop through recordset. Add each record to combobox with .AddItem

You said Google source indicated must use loop, was there example code?

I have found code that sets combobox Recordset property with recordset object but cannot get to work.

Examples of both in https://stackoverflow.com/questions/...dset-using-vba

[Gicu]

Have you tried to save the pass-through as a saved query that you set as the rowsource of the CmboClsdRsn combo like any other query and in the AfterUpdate event of the CmboOpnCls combo you just edit the SQL of the querydef to match the new selection?

Private Sub CmboOpnCls_AfterUpdate()
Dim qdf  As DAO.QueryDef
Set qdf  = CurrentDb.QueryDefs("qryYourPassThrough")

StrSQL = "SELECT ATCCallDescription, ATCCategory " & _
           "FROM Tri.ATCCallResults " & _
           "WHERE ATCCategory = '" & Me!CmboOpnCls & "'"

qdf.SQL=StrSQL 

End Sub

Private Sub CmboClsdRsn Enter()
     me.CmboClsdRsn.Requery
End Sub

Cheers,
Vlad