在 Access 2013 VBA 中从子窗体动态设置 SourceObject 和 RecordSource
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24273572/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Dynamically set the SourceObject and RecordSource from Subform in Access 2013 VBA
提问by user1552698
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:
在 ParentForm 中，我有一个命令按钮，其 onClick 事件具有以下代码：
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.
我在按钮下有一个名为 ChildForm 的子表单。
My objective is that 'When the user clicks the command button, the query gets executed and the resultant dataset gets displayed in the ChildForm'.
我的目标是“当用户单击命令按钮时，将执行查询并将结果数据集显示在 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 existon this line:
我被困在这一点上，不知道如何进一步进行。如果我执行此代码并单击按钮，则会收到错误运行时错误 2467。您输入的表达式指代已关闭或在此行上不存在的对象：
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 existon this line:
消息框正确显示了 ID..所以上面的代码正在工作..现在的问题是我仍然收到错误运行时错误 2467。您输入的表达式指的是在此行上关闭或不存在的对象：
Me.ChildSubForm.Form.Recordset = rstRet.OpenRecordset
回答by techturtle
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.
您这样做与我以前的做法大不相同，所以我可能会离开这里，但我相信问题在于您的Dynamic_Connection_SQL(strSQL)函数没有返回值，因此它无法将其分配给记录源。就目前而言，您的函数创建记录集并填充它，然后终止并清除检索到的记录，而不将其返回给您的调用命令。
