将组合框设置为另一个组合框的值 - Access Vba
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14145543/
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
Set combobox to the value of another combobox - Access Vba
提问by user1946392
I have 2 combo boxes on a Form. ComboIDand ComboName
我在一个表单上有 2 个组合框。ComboID和ComboName
ComboID contains items such as 1001, 1002, 1003, 1004 etc... ComboName contains items such as John, Matt, David, Luke etc...
ComboID 包含诸如 1001、1002、1003、1004 等项... ComboName 包含诸如 John、Matt、David、Luke 等项...
I have a table with this data: 1001, John 1002, Matt 1003, David 1004, Luke
我有一个包含以下数据的表格：1001、John 1002、Matt 1003、David 1004、Luke
I want ComboIDto display 1001when I select Johnfrom ComboName.
And I also want ComboNameto display '1002' when I select Matt.
我想ComboID在从 中1001选择时显示。我还想在选择时显示“1002” 。JohnComboNameComboNameMatt
I need to be able to select a new Name or ID at any point and have its corresponding value update in the other combo-box.
我需要能够在任何时候选择一个新的名称或 ID，并在另一个组合框中更新其相应的值。
I'm stuck with trying to get this to work and it can't be as hard as I'm making it out to be. I am still learning VBA code. Any help please?
我一直在努力让它发挥作用，它不可能像我想象的那么难。我还在学习 VBA 代码。请问有什么帮助吗？
回答by Katy
First Glance Solution (not recommended)
第一眼解决方案（不推荐）
What it Was
这是啥
My initial reaction to this problem was to try and use a change event. Setting up subroutines for each combobox to change the value of the other combobox would have then been the solution.
我对这个问题的最初反应是尝试使用更改事件。为每个组合框设置子程序以更改其他组合框的值将是解决方案。
Why you shouldn't use it
为什么你不应该使用它
However, in the documentation for the Access vba change events (link above) it clearly states:
但是，在 Access vba 更改事件的文档（上面的链接）中，它明确指出：
"Avoid creating two or more controls having Change events that affect each other — for example, two text boxes that update each other" (msdn access vba change event)
“避免创建两个或多个具有相互影响的 Change 事件的控件 - 例如，两个相互更新的文本框”（msdn access vba change event）
Unfortunately, this is exactly what you would be doing using the change event solution (using comboboxes instead of text boxes). In your example, a change in the ComboID combobox would trigger the appropriate change in the ComboName combobox, but that change in the ComboName combobox would trigger an attempted change in the ComboID, and this loop could continue on. Therefore, this is not a good answer to your problem.
不幸的是，这正是您使用更改事件解决方案（使用组合框而不是文本框）所做的事情。在您的示例中，ComboID 组合框的更改将触发 ComboName 组合框的相应更改，但 ComboName 组合框的更改将触发 ComboID 中的尝试更改，并且此循环可以继续。因此，这不是您问题的好答案。
A Better Solution
更好的解决方案
An AfterUpdate Eventappears to be the more appropriate approach to use in this case.
一个更新后事件似乎是在这种情况下使用更合适的方法。
Why the problematic loop won't be triggered
为什么不会触发有问题的循环
As mentioned in the AfterUpdate documentation:
正如 AfterUpdate 文档中提到的：
Changing data in a control by using Visual Basic or a macro containing the SetValue action doesn't trigger these events for the control.
使用 Visual Basic 或包含 SetValue 操作的宏更改控件中的数据不会触发控件的这些事件。
Therefore, an AfterUpdate Event (possibly using a SetValue) should be a much better approach.
因此， AfterUpdate 事件（可能使用SetValue）应该是更好的方法。
Much thanks to @Remou for pointing out the error of my change event ways and bringing the AfterUpdate event to my attention.
非常感谢@Remou 指出我更改事件方式的错误并引起我注意 AfterUpdate 事件。
回答by Fionnuala
A change event is very rarely a suitable choice for code. For example, a combobox allows the user to type as well as to select items, so the change event will fire for every single character typed. As will any events that are chained to that event, leading to the possibility of horrible complications.
更改事件很少是代码的合适选择。例如，组合框允许用户输入和选择项目，因此更改事件将针对输入的每个字符触发。与该事件相关的任何事件也将导致可怕的并发症的可能性。
Set up the combo boxes like so, thought it is a bit of a mystery as to why you wish to do this. You may need to keep them in synch in the current event. You do not mention if the combos are bound to a Control Source, so I have not included one.
像这样设置组合框，认为为什么要这样做有点神秘。您可能需要使它们在当前事件中保持同步。你没有提到组合是否绑定到控制源，所以我没有包括一个。
ComboID
组合ID
RowSource: SELECT ID, [Name] FROM Table ORDER BY ID
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 2cm;0cm ''The second column is hidden,
''the first column is any width
Private Sub ComboID_AfterUpdate()
Me.ComboName = Me.ComboID
End Sub
ComboName
组合名称
RowSource: SELECT ID, [Name] FROM Table ORDER BY [Name]
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0cm;2cm ''The first column is hidden,
''the second column is any width
Private Sub ComboName_AfterUpdate()
Me.ComboID = Me.ComboName
End Sub
