VBA in Access 2010 - Run-time Error 430

I’m getting a Run-time error '430': Class does not support Automation or does not support expected interface" on this line of code Set Me.lstResults.Recordset = rs or this Set Me![frmM_SearchForDocumentsSubForm].Form.Recordset = rs. I am trying to get the ADO Recordset based on a SQL stored procedure to appear in an unbound Listbox or Subform of an Access form. I’m on Win 7 Machine using Access 2010 connecting to SQL Server 2008:

On_Click event:

Private Sub cmdRun_Click()
'On Error Resume Next

    Dim strSQL As String

    'Stored procedure + parameters called from form
    strSQL = "Exec sqlsp_searchalltables " & Me.txtTables & _
       ", " & "'%" & Me.txtSearchTerm & "%'"

    OpenMyRecordset rs, strSQL

    'debug - view procedure
    Me.lblQuery.Caption = strSQL
    Me.Repaint

    Set Me.lstResults.Recordset = rs
    'or this
    'Set Me![frmM_SearchForDocumentsSubForm].Form.Recordset = rs
End Sub

I found some solutions for this error on the web and tried all of them to no avail. Most suggested checking the references which I did and verified.

I am able to successfully connect to the SQL server and have the results display in both a Listbox and Subform when I use DAO Querydef and a passthrough query or if I use this .listbox method:

With Me.lstResults
    Do
       strItem = rs.Fields("CLIENT_ID").Value
       .AddItem strItem
       rs.MoveNext
    Loop Until rs.EOF
End With

I would prefer not to use the DAO method because I found I need the coding flexibility of ADO especially with connecting to multiple Recordsets in SQL. Thoughts?

FYI: My OpenMyRecordset public function in Module:

Option Compare Database
Option Explicit

Global con As New ADODB.Connection
Global rs As ADODB.Recordset
Global NoRecords As Boolean

Public Enum rrCursorType
    rrOpenDynamic = adOpenDynamic
    rrOpenForwardOnly = adOpenForwardOnly
    rrOpenKeyset = adOpenKeyset
    rrOpenStatic = adOpenStatic
End Enum

Public Enum rrLockType
    rrLockOptimistic = adLockOptimistic
    rrLockReadOnly = adLockReadOnly
End Enum

Public Function OpenMyRecordset(rs As ADODB.Recordset, strSQL As String, Optional rrCursor As rrCursorType, _
    Optional rrLock As rrLockType, Optional bolClientSide As Boolean) As ADODB.Recordset

    If con.STATE = adStateClosed Then
        con.ConnectionString = "ODBC;Driver={SQL Server};Server=mysqlsvr;DSN=RecordsMgmt_SQLDB;UID=XXX;Trusted_Connection=Yes;DATABASE=RecordsManagementDB;"
        con.Open
    End If

    Set rs = New ADODB.Recordset
    With rs
        .ActiveConnection = con
            .CursorLocation = adUseClient
        .CursorType = IIf((rrCursor = 0), adOpenDynamic, rrCursor)
        .LockType = IIf((rrLock = 0), adLockOptimistic, rrLock)
        .Open strSQL
        If .EOF And .BOF Then
            NoRecords = True
            Exit Function
        End If
    End With

End Function

You definitely do not have to do the looping method to just to populate the listbox. I'm not familiar with the OpenMyRecordset command you used, but I suspect that something in its functionality is what is causing this error (i.e., it's not opening the recordset in a manner compatible with the listbox). This is how I connected to a local instance of SQL Server Express and was able to populate a listbox.

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection

With cn
    .ConnectionString = _
        "Provider=SQLOLEDB;Data Source=localhost\SQLEXPRESS;" & _
        "Initial Catalog=Northwind;Trusted_Connection=yes"
    .Open
End With

Set rs = New ADODB.Recordset

With rs
    Set .ActiveConnection = cn
    .Source = "SELECT FirstName, LastName FROM Employees"
    .LockType = adLockOptimistic
    .CursorType = adOpenKeyset
    .Open
End With

Set Me.lstTest.Recordset = rs

Set rs = Nothing
Set cn = Nothing

You will have to make sure that you have the Microsoft ActiveX Data Objects Library reference enabled in your project.