Re：级联组合盒 [英] Re: Cascading Combo boxes
问题描述
我希望有人可以提供帮助，我已经阅读了很多与此事有关的教程，但我担心VBA不是我的强项，我只需要一点指导。
我有一个名为[tbl Site Info]的表，它有三个字段[Site]，[Department]和[Manager]。在表格上我有三个组合框，显示桌面上每个字段的数据以及额外的所有记录。以下代码启用此代码位于每个组合框的行来源中：
[SELECT [tbl Site Info]。[Site] AS Filter，[Site] FROM [ tbl网站信息] UNION SELECT" *" AS过滤器，所有记录 AS [Site] FROM [tbl网站信息];]
与此相关，我需要/希望根据选择限制组合框中的数据的选项最后，即如果我从cboSite框中选择''UK''那么它只会在cboDept框中从英国给我部门，同样当我选择一个部门时，cboMgr框只会显示与该部门相关的经理。
但是我需要保留所有记录选项，因为我需要显示英国所有部门等...
我尝试在cboDept框的行源中使用以下代码：
[SELECT [tbl Site Info]。[Department] AS Filter，[Department] FROM [tbl Site信息] UNION SELECT" * AS过滤器，所有记录 AS [部门]来自[tbl网站信息]在哪里[tbl网站信息]。[网站] =表格！[frm主要副本]！cboSite;]
我相信只会显示与网站相关的部门，但它没有。
任何帮助将不胜感激，
i hope someone can help, i have read a lot of tutorials relating to this matter but im afraid VBA isnt my strong point and i just need a touch of guidance.
I have one table called [tbl Site Info] which has three fields [Site], [Department] and [Manager]. On a form i have three combo boxes which show data from each field on the table along with an extra ''All Records''. The following code enabling this is in the ''Row Source'' of each combo box:
[SELECT [tbl Site Info].[Site] AS Filter, [Site] FROM [tbl Site Info] UNION SELECT "*" AS Filter, "All Records" AS [Site] FROM [tbl Site Info];]
In conjunction with this, i need/would like the option of limiting the data in the combo boxes based on the selection of the last, i.e. if i select ''UK'' from the cboSite box then it will only give me the Departments from the UK in the cboDept box and similarly when i select a department then the cboMgr box will only show the managers associated with that department.
However i need to keep the ''All Records'' option as i will need to show all departments in UK, etc...
I tried using the following code in the row source of the cboDept box:
[SELECT [tbl Site Info].[Department] AS Filter, [Department] FROM [tbl Site Info] UNION SELECT "*" AS Filter, "All Records" AS [Department] FROM [tbl Site Info] WHERE [tbl Site Info].[Site]=Forms![frm Main copy]!cboSite;]
which i believed would only show the departments related to the site, but it did''nt.
Any help would be greatly appreciated,
推荐答案
我希望有人能提供帮助，我已经阅读了很多与此事有关的教程，但我担心VBA不是我的强项，我只需要一点指导。 />
我有一个名为[tbl Site Info]的表，它有三个字段[Site]，[Department]和[Manager]。在表格上我有三个组合框，显示桌面上每个字段的数据以及额外的所有记录。以下代码启用此代码位于每个组合框的行来源中：
[SELECT [tbl Site Info]。[Site] AS Filter，[Site] FROM [ tbl网站信息] UNION SELECT" *" AS过滤器，所有记录 AS [Site] FROM [tbl网站信息];]
与此相关，我需要/希望根据选择限制组合框中的数据的选项最后，即如果我从cboSite框中选择''UK''那么它只会在cboDept框中从英国给我部门，同样当我选择一个部门时，cboMgr框只会显示与该部门相关的经理。
但是我需要保留所有记录选项，因为我需要显示英国所有部门等...
我尝试在cboDept框的行源中使用以下代码：
[SELECT [tbl Site Info]。[Department] AS Filter，[Department] FROM [tbl Site信息] UNION SELECT" * AS过滤器，所有记录 AS [部门]来自[tbl网站信息]在哪里[tbl网站信息]。[网站] =表格！[frm主要副本]！cboSite;]
我相信只会显示与网站相关的部门，但它确实没有。
任何帮助将不胜感激，
i hope someone can help, i have read a lot of tutorials relating to this matter but im afraid VBA isnt my strong point and i just need a touch of guidance.
I have one table called [tbl Site Info] which has three fields [Site], [Department] and [Manager]. On a form i have three combo boxes which show data from each field on the table along with an extra ''All Records''. The following code enabling this is in the ''Row Source'' of each combo box:
[SELECT [tbl Site Info].[Site] AS Filter, [Site] FROM [tbl Site Info] UNION SELECT "*" AS Filter, "All Records" AS [Site] FROM [tbl Site Info];]
In conjunction with this, i need/would like the option of limiting the data in the combo boxes based on the selection of the last, i.e. if i select ''UK'' from the cboSite box then it will only give me the Departments from the UK in the cboDept box and similarly when i select a department then the cboMgr box will only show the managers associated with that department.
However i need to keep the ''All Records'' option as i will need to show all departments in UK, etc...
I tried using the following code in the row source of the cboDept box:
[SELECT [tbl Site Info].[Department] AS Filter, [Department] FROM [tbl Site Info] UNION SELECT "*" AS Filter, "All Records" AS [Department] FROM [tbl Site Info] WHERE [tbl Site Info].[Site]=Forms![frm Main copy]!cboSite;]
which i believed would only show the departments related to the site, but it did''nt.
Any help would be greatly appreciated,
你好Jon，
你更接近你想要的东西然后你意识到。
使部门组合框工作所需的只是cboSite的afterUpdate事件，它将重新查询部门列表。换句话说，如果您希望部门组合框列表仅反映在cboSite中选择的站点的部门，则需要重新查询部门列表。为此，请将以下代码放在cboSite的afterUpdate事件中。
Hi Jon,
You are much closer to what you want then you realize.
All you needed to make the Department combobox work was an afterUpdate event for cboSite that will requery the Department list. In other words, if you want the Department combobox list to reflect only the Departments for the site(s) selected in cboSite, you need to requery the Department list. To do that, place the following code in the afterUpdate event of cboSite.
谢谢Puppydogbuddy，
当我把代码放入' cboSite的'after_update''，cboDept框的结果没有差异（即显示所有结果，而不仅仅是与该网站相关的结果）。我已经尝试从cboDept中删除Row Source详细信息，但这并没有给我任何条目。
有什么我缺少的吗？..如果我删除行源，那么我不会可以选择所有记录，这是必要的。是否有可能同时拥有两个？
如果您（或任何其他人）有任何建议，那么我对所有想法持开放态度，如果没有，那么我会保留这些框是的。
再次，我非常感谢帮助。
Thanks Puppydogbuddy,
When i put the code into the ''after_update'' of cboSite, there is no difference in the outcome of the cboDept box (i.e. all the results are shown, not just the ones related to that site) . I have tried removing the Row Source details from cboDept but that does not give me any entries.
Is there anything that i am missing?.. if i remove the row source then i wont have the option of ''All Records'' which is necessary. Is it possible to have both??
If there is anything you (or anyone else) can suggest then i am open to all ideas, if not then i will keep the boxes as they are.
Again, i do appreciate the help.
Jon，
请耐心等待。我们会到达那里。在此期间，您可以在cboSite的更新后事件中找到下面的代码。告诉我发生的情况与以前不同。谢谢。
Jon,
Be patient. We will get there. In the meantime, can you ry the code below in the after update event of cboSite. Just tell me what happens that is different than before. Thanks.
这篇关于Re：级联组合盒的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
