Dynamically set the SourceObject and RecordSource from Subform in Access 2013 VBA

Question

I have a following function to return the resultant dataset:

Function Dynamic_Connection_SQL(ByVal SQL As String)

Dim qdf As DAO.QueryDef, rst As DAO.Recordset
Set qdf = CurrentDb.CreateQueryDef("")
qdf.Connect = "ODBC;Driver=SQL Server;Server=XXXX;DATABASE=XX;Trusted_Connection=Yes;"
qdf.SQL = SQL
qdf.ReturnsRecords = True
Set rst = qdf.OpenRecordset
rst.Close
Set rst = Nothing
Set qdf = Nothing

End Function

In a ParentForm, I have a command button, whose onClick event has the following code:

Private Sub Command0_Click()

Dim strSQL As String
strSQL = "SELECT ID, EmployeeID, EmployeeName " & _
         "FROM XYZ " & _
         "ORDER BY EmployeeName;"
Dynamic_Connection_SQL (strSQL)
Me.ChildSubForm.Form.RecordSource = Dynamic_Connection_SQL(strSQL)

End Sub

and I have a subform named ChildForm under the button.

My objective is that 'When the user clicks the command button, the query gets executed and the resultant dataset gets displayed in the ChildForm'.

I am stuck at this point and not sure how to proceed further. If I execute this code and click on the button, I am getting an error Runtime error 2467. The expression you entered refers to an object that is closed or does not exist on this line:

Me.ChildSubForm.Form.RecordSource = Dynamic_Connection_SQL(strSQL)

Update:

I have updated my code to :

Function Dynamic_Connection_SQL(ByVal SQL As String) As DAO.Recordset

Dim qdf As DAO.QueryDef, rst As DAO.Recordset
Set qdf = CurrentDb.CreateQueryDef("")
qdf.Connect = "ODBC;Driver=SQL    Server;Server=xxxx;DATABASE=xx;Trusted_Connection=Yes;"
qdf.SQL = SQL
qdf.ReturnsRecords = True
Set rst = qdf.OpenRecordset
Set Dynamic_Connection_SQL = rst

End Function

And

Private Sub Command0_Click()

Dim strSQL As String
Dim intCounter As Integer
Dim rstRet As DAO.Recordset

strSQL = "SELECT ID, ClientID, SDP_CategorySK, EmployeeID, RollupToEmployeeID, Allocation " & _
         "FROM ShiftCurrentStaffing " & _
         "ORDER BY EmployeeID;"

Set rstRet = Dynamic_Connection_SQL(strSQL)

With rstRet
  Do While Not rstRet.EOF
    Debug.Print ![ID] & " " & ![EmployeeID] & ", (" & ![EmployeeName] & ")"
    MsgBox ![ID] & " "
   .MoveNext
  Loop
End With
Me.ChildSubForm.Form.Recordset = rstRet.OpenRecordset
Me.Requery
rstRet.Close
Set rstRet = Nothing

End Sub

The messagebox is correctly showing the IDs..so the above code is working..now the problem is that I am still getting an error Runtime error 2467. The expression you entered refers to an object that is closed or does not exist on this line:

Me.ChildSubForm.Form.Recordset = rstRet.OpenRecordset

set a breakpoint on the line and try inspecting the values ChildSubForm and ChildSubForm.Form. also why are you calling ooenrecordse? shouldn't = rstRet be enough? — Conrad Frix
I agree with Conrad. Can't you just change the offending line to Me.ChildSubForm.Form.Recordset = rstRet? You should not have to reopen it to bind it to the form. — techturtle

techturtle techturtle · Accepted Answer · 2014-06-17T21:45:57

You're doing this considerably different than I used to, so I may be way off here, but I believe the problem is that your Dynamic_Connection_SQL(strSQL) function has no return value, so it cannot assign that to the recordsource. As it stands right now, your function creates the recordset and populates it, then terminates and clears out the retrieved records without returning it to your calling command.

Dynamically set the SourceObject and RecordSource from Subform in Access 2013 VBA

1 Answers