Type Mismatch on VBA Function That Returns ADO Recordset at Exit Function or End Function

Ok...I thought I had tested this all out.  I even THOUGHT about what you suggested before I posted and in hindsight, I know what I did wrong, and it was PROFOUNDLY dumb (just as I expected).

Anyway, based on your suggestion, this is what I came up with:

'the original function code, without the debugging stuff
Public Function GetRS(NameOfTable As String) As ADODB.Recordset
On Error GoTo HandleError

    Dim rs As ADODB.Recordset
   
    Set rs = New ADODB.Recordset
   
    rs.Open NameOfTable, CurrentProject.Connection, adOpenStatic, adLockReadOnly, adCmdTableDirect
   
    Set GetRS = rs
   
    'rs.Close
    Set rs = Nothing
   
ExitHere:
    Exit Function
   
HandleError:
    MsgBox Err.Number & ": " & Err.Description
    Resume ExitHere
   
End Function
-------------------------------------------------------------

'a new sub to accept the recordset and process it
Public Sub ReadRS(SomeRS As ADODB.Recordset)
   
    Do
        Debug.Print SomeRS(0)
        SomeRS.MoveNext
    Loop Until SomeRS.EOF

End Sub

So if I call my sub in the immediate window like so:
ReadRS GetRS("Product")
I get the list of values from the first column of the table--exactly what is expected.  The error I made earlier was to call the sub like this:
ReadRS(GetRS("Product"))
which for reasons that I might be able to explain with a great deal of reflection produces a Type Mismatch error (but at this instant, it's not at all clear to me).

So you've solved the problem.  I still have one lingering concern:  Why CAN'T I close the recordset in GetRS after I SET the function return, but I can SET it to Nothing?  You're correct, it absolutely will not work if the recordset is closed explicitly.  Any ideas?  (You'll get the points, but I'm interested in a more general conceptual discussion now).  Thanks again.

Think of object references as pointers to the location of the object in memory (it is a little more complex, but that is the basic principle).  
 
When you set one reference to another, you aren't copying the object, you are just copying the address.  Both references then point to the same object.  So it does not  matter which reference is used to call the close method, the object will be closed.
 
When you set a reference to something else, you don't change the objects, you just change which objects are referenced.  So if you set a reference to nothing, you do not close or destroy the object, you just stop referring to it through that reference.  (If an object has no references set to it, it still exists anonymously in memory until VBA's garbage collection decides to detroy it.)

Look at the following code:

Public Sub Dummy(SQL as String)
  Dim A as ADODB.Recordset, B As ADOBD.Recordset
  Set A=New ADODB.Recordset  '<-creates a new object
  A.Open SQL, CurrentProject.Connection, adOpenSnapshot '<-opens the object
  Set B=A  '<- sets a second reference to the same object
  A.Close  '<- closes the object
  Set A = Nothing '<- unsets the reference
  If Not B.EOF Then  '<!!! produces an error because the object is closed.
    B.MoveLast
  End if
  B.Close
  Set B = Nothing
End Sub

So if I call my sub in the immediate window like so:
   ReadRS GetRS("Product")
I get the list of values from the first column of the table--exactly what is expected.  The error I made earlier was to call the sub like this:
   ReadRS(GetRS("Product"))
which for reasons that I might be able to explain with a great deal of reflection produces a Type Mismatch error (but at this instant, it's not at all clear to me).
----

Yeap, that trips up programmers switching to VBA from another language all the time.  

The problem is that ReadRS(GetRS("Product")) looks entirely sensible.  That's the form of a procedure call in most other programming languages.  But these brackets aren't a part of the procedure call at all, they are part of the parameter being passed to the procedure.

It had me bamboozled too until I figured out what was going on.

When you put an object inside brackets, VBA does something you would not expect - it calls the default method!   In this case, putting a recordset inside brackets means that you are trying to pass the recordset's Fields collection to the calling function, not the recordset itself.

In the intermediate window, try executing the following two lines:

  ?TypeName( New ADODB.Recordset )
  ?TypeName( (New ADODB.Recordset) )

It is not weird?

Nice GreymanMSC! Nice insight mate.