MS Access 2007：筛选组合框的选择列表(MS Access 2007: Filtering selection list for a combo box)

MS Access 2007：筛选组合框的选择列表(MS Access 2007: Filtering selection list for a combo box)

背景：我的表单的记录源是一个查询（“BigQuery”），它只是组合了几个相关的表。 我正在设置组合框来编辑字段; 这些组合框的控件源始终只是BigQuery的一个字段。 其中一个字段是UnitType，另一个是UnitSubType。 UnitSubType有大约100个不同的条目，但是当与特定的UnitType配对时，其中许多条目没有上下文意义：如果UnitType =“Car”，那么UnitSubType =“18 wheeler”没有任何意义，我很快就没有了让客户有机会犯错误。

问题，A部分：当用户在表单上选择UnitType的值时，我想将UnitSubType的组合框限制为已与数据库中的UnitType值配对的UnitSubType值。 这是怎么做到的？

示例：如果表中已存在包含UnitType =“truck”和“UnitSubType =”18 wheeler“的记录的1个或多个实例，则假设用户已在组合框中选择了”truck“，其中UnitType为UnitSubType组合框中显示的选项应为“18 wheeler”。

问题，B部分：我还想让用户只需在组合框中输入一个新的UnitSubType即可：如果用户已经在UnitType的组合框中选择了“truck”并手动输入“flatbed”在UnitSubType的组合框中，编辑后的记录应该在UnitSubType中具有“平板”，并且每当UnitType为“truck”时，未来的编辑操作应该包括“flatbed”作为UnitSubType选项。 在更简单的情况下，将“允许值列表编辑”设置为“是”可以解决此问题，但我想确保在提供给问题A的解决方案中提供此功能。

在SO中也有类似的问题线程，但我在Access中是一个小伙伴，我无法根据我的需要推断出答案。 对不起; 请尽可能具体。

非常感谢！ 戴夫

Background: The Record Source for my form is a query ("BigQuery") that just combines several related tables. I am setting up combo boxes to edit fields; the Control Source for these combo boxes is always just a field from BigQuery. One of the fields is UnitType, and another is UnitSubType. There is about 100 distinct entries for UnitSubType, but many of them make no contextual sense when paired with a particular UnitType: If UnitType="Car", then UnitSubType="18 wheeler" makes no sense, and I'd just as soon not give the client the opportunity to make mistakes.

Question, Part A: When the user chooses a value for UnitType on the form, I would like to limit the combo box for UnitSubType to those UnitSubType values already paired with UnitType values in the database. How is this done?

Example: If 1 or more instances of a record containing UnitType="truck" and "UnitSubType="18 wheeler" already exist in the table, then assuming that the user has already selected "truck" in the combo box for UnitType one of the choices presented in the combo box for UnitSubType should be "18 wheeler".

Question, Part B: I would also like for the user to be able to add a new UnitSubType simply by typing it into the combo box: if the user has already selected "truck" in the combo box for UnitType and manually types "flatbed" in the combo box for UnitSubType, then the edited record should have "flatbed" in the UnitSubType and future editing operations should include "flatbed" as a UnitSubType choice whenever the UnitType is "truck". In simpler situations setting "Allow Value List Edits" to "Yes" took care of this, but I want to make sure this functionality is available in the solution provided to Question Part A.

There are similar question threads already in SO, but I am such a noob at Access that I have been unable to extrapolate the answers to fit my need. I am sorry; please, be as specific as possible.

Thank you so much! Dave

最满意答案

我已经找到了一个我想分享的解决方案。 访问很难描述，但我会列出规范的例外情况，并尝试以这种方式传达解决方案。

用于选择单位类型的第一个（主要）组合框：

名称：CBUnitType ControlSource：UnitType'''主表中的字段：MainTbl

行来源：

SELECT DISTINCT MainTbl.UnitType FROM MainTbl ORDER BY MainTbl.UnitType;

绑定栏：1

允许值列表编辑：是 锁定：不 更新后：[事件过程]'''参见子程序：ComboBoxUnitType_AfterUpdate（）如下所示。

选择单位子类型的第二个（从属）组合框：

名称：CBUnitSubType ControlSource：UnitSubType'''主表中的字段：MainTbl 行源：ComboQueryUnitSubType'''由查询生成器构建，详述如下。 绑定栏：1 允许值列表编辑：是 锁定：不

Query Builder对象：ComboQueryUnitSubType

SELECT DISTINCT [MainTbl].UnitSubType FROM [MainTbl] WHERE ((([MainTbl].UnitType)=[Forms]![Unit Editor]![UnitType])) ORDER BY [MainTbl].UnitSubType;

通过为组合框中的“更新后事件”[事件过程]“CBUnitType”选择“[事件过程]”创建的VBA子例程.VBA子例程自动放置在VBA环境中的Microsoft Office Access Class Objects文件夹中，名为“Form_Unit Editor”的模块“。子程序名称也预先选择为：”ComboBoxUnitType_AfterUpdate（）“几乎可以肯定，如果你改变这些名称中的任何一个，那么链接就会破坏。模块中的VBA代码是：

Option Compare Database Option Explicit Private Sub ComboBoxUnitType_AfterUpdate() Forms![Unit Editor]![ComboBoxUnitSubType].Requery Forms![Unit Editor]![ComboBoxUnitSubType].Value = "" End Sub

因此，效果如下：在用户更新单元类型的组合框后，vba例程执行并重新查询单元子类型的组合框的查询，然后它任意获取单元子类型组合的.value参数框并将其清除为空字符串。

我要感谢Blue Claw Database Design提供的慷慨教程。 具体来说，重新查询组合框查询的VBA代码是一个救生员，并在他们的相关下拉列表框教程中详细说明为什么指定为组合框的行源的查询ComboQueryUnitSubType不会自动重新运行每次用户选择组合框时，按访问是任何人的猜测。

我很抱歉这个冗长的问题和答案。 我希望在不久的将来回到一些不错的简洁代码！ 戴夫

I've arrived at one solution that I wanted to share. Access is difficult to describe, but I will list exceptions to the norm and try to communicate the solution that way.

First (primary) Combo Box for picking the Unit Type:

Name: CBUnitType ControlSource: UnitType '''A field in the Main Table: MainTbl

Row Source:

SELECT DISTINCT MainTbl.UnitType FROM MainTbl ORDER BY MainTbl.UnitType;

Bound Column: 1

Allow Value List Edits: Yes Locked: No After Update: [Event Procedure] '''See subroutine: ComboBoxUnitType_AfterUpdate() shown below.

Second (dependent) Combo Box for picking the Unit Sub-Type:

Name: CBUnitSubType ControlSource: UnitSubType '''A field in the Main Table: MainTbl Row Source: ComboQueryUnitSubType '''Built by Query Builder, detailed below. Bound Column: 1 Allow Value List Edits: Yes Locked: No

Query Builder object: ComboQueryUnitSubType

SELECT DISTINCT [MainTbl].UnitSubType FROM [MainTbl] WHERE ((([MainTbl].UnitType)=[Forms]![Unit Editor]![UnitType])) ORDER BY [MainTbl].UnitSubType;

VBA subroutine created by selecting "[Event Procedure] for the After Update event in combo box "CBUnitType" described above. The VBA subroutine is automatically placed in the Microsoft Office Access Class Objects folder in the VBA environment in a module named: "Form_Unit Editor". The subroutine name is also pre-chosen to be: "ComboBoxUnitType_AfterUpdate()" Almost certainly if you change any of these names the linkages will break horribly. The VBA code in the module is:

Option Compare Database Option Explicit Private Sub ComboBoxUnitType_AfterUpdate() Forms![Unit Editor]![ComboBoxUnitSubType].Requery Forms![Unit Editor]![ComboBoxUnitSubType].Value = "" End Sub

So, the effect is this: After the user updates the combo box for Unit Type, the vba routine executes and re-queries the query for the combo box for Unit SubType, and then it arbitrarily takes the .value parameter of the Unit SubType combo box and clears it to the empty string.

I would like to gratefully acknowledge the generous tutorials provided by Blue Claw Database Design. Specifically, the VBA code that re-queries the combo box query was a life-saver, and is detailed in their tutorial on Dependent Drop Down List Box Why the query ComboQueryUnitSubType, specified as the combo box's Row Source, is not re-run automatically by Access every time the combo box is selected by the user is anyone's guess.

I'm sorry for both the long-winded question and answer. I hope to be heading back to some nice, terse code in the near future! Dave