MS Access通过依赖于窗体值 [英] MS Access pass through dependent on form value
问题描述
如何将传递查询分配给依赖于表单中另一个值的行源?
How do I assign a pass-through query to Row Source that is dependent on another value in the form?
基本上我想这样做:
SELECT x.companyid,
x.companyname,
x.productid
FROM x
WHERE (((x.CompanyID) = [Forms]![Reporting]![CompanyID_Control]))
ORDER BY x.productid;
但是,当然,传递查询不支持对任何表单控件的引用.
But of course pass-through queries do not support reference to any form controls.
我已阅读这里有一种通过VBA的方法,但是我不知道如何将VBA与控件的行源一起使用.
I have read here that there is a method via VBA, however I do not know how to use VBA in conjunction with the Row Source of a control.
推荐答案
正如Remou在他的回答中所述,链接表将使此操作变得更加容易.但是,如果您有一个名为MyQuery
的传递查询,则可以执行以下操作,以使MyComboOrListBox
控件的RowSource在CompanyID_Control
的值更改时动态更新:
As Remou stated in his answer, linked tables will make this easier. However, if you have a pass-through query named MyQuery
, you can do the following to make the RowSource of a MyComboOrListBox
control update dynamically when the value of the CompanyID_Control
changes:
Private Sub CompanyID_Control_AfterUpdate()
Dim SQL As String, qdf AS DAO.QueryDef
Set qdf = CurrentDB.QueryDefs("MyQuery")
qdf.SQL = " SELECT x.companyid, x.companyname, x.productid " & _
" FROM x " & _
" WHERE x.CompanyID =" & Me.CompanyID_Control & _
" ORDER BY x.productid;"
Me.MyComboOrListBox.RowSource = "MyQuery"
End Sub
您还需要将CompanyID_Control
的AfterUpdate
属性设置为:
[Event Procedure]
.
You'll also need to set the AfterUpdate
property of the CompanyID_Control
to:
[Event Procedure]
.
请注意,即使按照Remou的建议使用链接表,您仍然需要CompanyID_Control
的AfterUpdate
事件中的代码来刷新组合框/列表框的行来源:
Note that even if you use linked tables as Remou suggested, you will still need code in the AfterUpdate
event of the CompanyID_Control
to refresh your combobox/listbox RowSource:
Private Sub CompanyID_Control_AfterUpdate()
Me.MyComboOrListBox.Requery
End Sub
这篇关于MS Access通过依赖于窗体值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!