MS Access Form：使用第一个Combobox的值来过滤第二个Combobox的选项？(MS Access Form: Use value of first Combobox to filter options of second Combobox?)

作为一个例子，让我们使用食物。

表： 食物

ID || Type || Name 1 || Fruit || Apple 2 || Fruit || Orange 3 || Veggie || Pea 4 || Veggie || Corn

组合框 1： TypeCB 行来源（SQL）：

SELECT DISTINCT Count([Food].ID) AS CountOfID, [Food].Type FROM [Food] ORDER BY [Food].Type;

（我使用“CountofID”的原因是获取唯一的Type值，否则它们会重复，因为ID是唯一的）

现在，这对第一个完美的作品，但是，当我试图将值拉到我的下一个ComboBox时，它的第二个组合框仍然是空的。 刷新表单与第一个组合框填充不能解决它。

组合框 2： NameCB 行来源（SQL）：

SELECT Count([Food].ID) AS CountOfID, [Food].Name FROM [Food] HAVING ((([Food].Type)=[Forms]![Food Form]![TypeCB].[SelText]));

有没有更好的方法来获取这些数据？

As an example, let's use food.

Table: Food

ID || Type || Name 1 || Fruit || Apple 2 || Fruit || Orange 3 || Veggie || Pea 4 || Veggie || Corn

Combobox 1: TypeCB Row Source (SQL):

SELECT DISTINCT Count([Food].ID) AS CountOfID, [Food].Type FROM [Food] ORDER BY [Food].Type;

(The reason I use "CountofID" is the get the unique Type values, otherwise they're repeated since the ID's are unique)

Now this works perfectly for the first, however, when I try to pull the value over to my next ComboBox, it the second combobox remains empty. Refreshing the Form with the first combobox filled doesn't fix it.

Combobox 2: NameCB Row Source (SQL):

SELECT Count([Food].ID) AS CountOfID, [Food].Name FROM [Food] HAVING ((([Food].Type)=[Forms]![Food Form]![TypeCB].[SelText]));

Is there another/a better way to grab this data?

最满意答案

您不需要TypeCB的Row Source中的Count，因为您只使用Type列。 如果将它放在中，请确保TypeCB的Bound Column属性设置为2，以便组合框的值将从类型列中取代而不是CountOfID。

您的NameCB行源是可以的，除了[SelText]属性。 更改：

SELECT Count([Food].ID) AS CountOfID, [Food].Name FROM [Food] HAVING ((([Food].Type)=[Forms]![Food Form]![TypeCB]));

如果TypeCB组合框的Bound Column属性设置为具有Type值的列，则可以引用控件本身以获取该值。

在TypeCB组合框的After Update事件中，添加NameCB.Requery()以刷新NameCB组合框。

You don't need Count in the Row Source of your TypeCB because you're only using the Type column. If you leave it in, be sure the Bound Column property of TypeCB is set to 2 so the value of the combo box will pick up from the Type column rather than CountOfID.

Your NameCB Row Source is OK, except for the [SelText] property. Change it:

SELECT Count([Food].ID) AS CountOfID, [Food].Name FROM [Food] HAVING ((([Food].Type)=[Forms]![Food Form]![TypeCB]));

If the Bound Column property of your TypeCB combo box is set to the column with the Type value, you can reference the control itself to get the value.

In the After Update event of the TypeCB combo box, add NameCB.Requery() to refresh the NameCB combo box.

更多推荐