I have an After Update Method:
Private Sub TurtleName_AfterUpdate()
'Find the record that matches the control.
Dim rs As Object
Dim vTurtleID As Variant
Dim strSQL As String
Dim rsFeeding As Object
Dim dbsql As Database
Set rs = Me.Recordset.Clone
rs.FindFirst "[TurtleId] = " & Str(Nz(Me![TurtleName], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
vTurtleID = Me![TurtleName]
'SQL Server PassThrough query
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.Recordset = rsFeeding
When I run the stored procedure from SSMS, I get 2200 records. When I run it inside Access it returns only one record.
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.
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.