asked on

Assigning Subform record source from DAO query

I have the following code. Everything appears to work until this line:
Set Forms!TurtleMain!TurtleFeedingSub.Form.RecordSource = rsFeeding
I get an error '438' Object doesn't support this property or method.

Private Sub TurtleName_AfterUpdate()
'Find the record that matches the control.

Dim rs As Object
Dim vTurtleID As Variant
Dim dbs As Database
Dim qdf As QueryDef
Dim strSQL As String
Dim rsFeeding As Object
Dim dbsql As Database

Set dbs = CurrentDb

' Set qdf = dbs.CreateQueryDef(strName, strSQL)
Set rs = Me.Recordset.Clone
rs.FindFirst "[TurtleId] = " & Str(Nz(Me![TurtleName], 0))
vTurtleID = Me![TurtleName]
Set dbsql = DBEngine.Workspaces(0).OpenDatabase _
("", False, False, "ODBC;DRIVER=SQL Server Native Client 11.0;SERVER=WIN7DEV\SQLEXPRESS2012;UID=David Blankman;Trusted_Connection=Yes;DATABASE=TurtlesDB_beSQL;")

strSQL = "spSubFeeding " & vTurtleID & " "
Set rsFeeding = dbsql.OpenRecordset(strSQL, dbOpenSnapshot, dbSQLPassThrough)
Set Forms!TurtleMain!TurtleFeedingSub.Form.RecordSource = rsFeeding
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
TurtleChosen

End Sub

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dblankman

ASKER

That helps, but when I run the stored procedure from SSMS, I get 2200 records. When I run it inside Access it returns only one record.

dblankman

ASKER

Also, after I run the code once, it won't let me run it with another TurtleID. I thought that, perhaps, I should close the connection. I tried doing a dbsql.Close, but that seems to close the recordset as well.

dblankman

ASKER

Another thought that I had was defining the vTurtleID as a global variable and then assigning the recordset in the subform's onload event, although, I don't know if that would make any difference.

Scott McDaniel (EE MVE )

Looks like you've asked the followup question here:

https://www.experts-exchange.com/questions/28758859/Access-PassThrough-Query-not-returning-correct-number-of-records.html

So you should close this one out, as it seems your original question was answered.