database Access 表单组合框中未显示的值

Values not showing in Access form combo box

提问by zohair

I have an application in Access 2003 that I am working on. In it, I have an employee table, which is connected to two other tables. The two connected tables are tables that hold a few fixed KeyWords. In my main employee table, I just have the ID from the other table, rather than having the whole word.

我正在处理 Access 2003 中的一个应用程序。其中，我有一个员工表，它连接到另外两个表。两个连接的表是包含一些固定关键字的表。在我的主员工表中，我只有来自另一个表的 ID，而不是整个词。

I wanted to make a form for entering data into these tables, so I made a query from the three tables that shows the all the regular fields of the employee table except instead of those two ID's, I showed the words themselves.

我想制作一个表格来将数据输入到这些表中，所以我从三个表中进行了查询，这些表显示了员工表的所有常规字段，除了这两个 ID 之外，我显示了单词本身。

I then made the form and set the query as the RecordSource. The fields that have keywords are Combo boxes on my form, and their ControlSource is the keyword field from the query (like I mentioned earlier, the value can only be one keyword out of a list). Now, the problem I face is this: When I want to see a single record, it shows the correct value in the ComboBox, but when dropdown the menu, it doesnt show any of the other values.

然后我制作了表单并将查询设置为 RecordSource。有关键字的字段是我表单上的组合框，它们的 ControlSource 是查询中的关键字字段（就像我之前提到的，值只能是列表中的一个关键字）。现在，我面临的问题是：当我想查看单个记录时，它会在 ComboBox 中显示正确的值，但是当下拉菜单时，它不会显示任何其他值。

What is the best way to do this? Am I doing this in the wrong way?

做这个的最好方式是什么？我是否以错误的方式这样做？

Thank you

谢谢

采纳答案by Simon

I'd approach this slightly differently, set the combo box Row Sources to include both the id and text fields (you can hide the ID column if required by setting it's width to zero). Now add the ID fields to the employee query; you shouldn't need to join the other tables to the Employee table in this query, these fields are foreign keys and the combo boxes should show the text for you automatically. Set the form Record Source to this query, now set the combo box Control Source to the appropriate ID value from the form Record Source.

我的处理方式略有不同，将组合框 Row Sources 设置为包含 id 和 text 字段（如果需要，可以通过将 ID 列的宽度设置为零来隐藏 ID 列）。现在将 ID 字段添加到员工查询中；在此查询中，您不需要将其他表连接到 Employee 表，这些字段是外键，组合框应自动为您显示文本。将表单记录源设置为此查询，现在将组合框控件源设置为来自表单记录源的适当 ID 值。

If you let the wizard build a form for you based on the Employee table you will see this approach in action.

如果您让向导根据 Employee 表为您构建一个表单，您将看到这种方法在起作用。

回答by Tevernaugh Davis

I had this same issue in access 2016 and the issue was with my datatype. The combo box didnt accept 'Longtext' so i just changed datatype to 'shorttext' instead and all the values appeared.

我在 access 2016 中遇到了同样的问题，问题出在我的数据类型上。组合框不接受“长文本”，所以我只是将数据类型更改为“短文本”，所有值都出现了。

回答by Raj More

You have set the query as the recordsource for the form. A form only shows (and ties to) ONE record at a time.

您已将查询设置为表单的记录源。一个表格一次只显示（并关联到）一个记录。

Since you want the combo box to show all the values, you should set the RecordSource of the ComboBox to your query.

由于您希望组合框显示所有值，您应该将 ComboBox 的 RecordSource 设置为您的查询。

回答by SierraJuliet

ControlSourceis the column name of the table where any user entry will be saved.

ControlSource是将保存任何用户条目的表的列名称。

RowSourceis where you type the name of the saved query or manual query typed directly into that field, which is used to provide a list of available drop down options for the combo box.

RowSource是您在该字段中键入已保存查询或手动查询的名称的地方，用于为组合框提供可用下拉选项列表。

RowSourceTypeshould be set to Table/Query, which is based on aforementioned RowSource method.

RowSourceType应该设置为 Table/Query，这是基于前面提到的 RowSource 方法。

Now to correct the problem of only displaying what you begin to type (i.e. "Allow AutoCorrect" in Property Sheet > Other) or already have saved for that record without other options appearing after clicking the drop down is the following:

现在要纠正仅显示您开始键入的内容（即“属性表”>“其他”中的“允许自动更正” ）或已经为该记录保存而没有在单击下拉菜单后出现其他选项的问题，如下所示：

1. In Design View, click the combobox in question.
2. Property Sheet > Format > Column Count= set the desired number of columns to display in drop down.
3. Property Sheet > Format > Column Widths= set the desired width of column in the drop down.
4. Property Sheet > Format > List Width= set the desired width of the drop down itself.
5. Property Sheet > Data > Bound Column= set to the column of query table (i.e. 1 for ID and 2 for list options, if you only have two entries)

1. 在设计视图中，单击有问题的组合框。
2. 属性表 > 格式 > 列数= 设置要在下拉列表中显示的所需列数。
3. 属性表 > 格式 > 列宽= 在下拉列表中设置所需的列宽。
4. 属性表 > 格式 > 列表宽度= 设置下拉本身的所需宽度。
5. 属性表>数据>绑定列=设置为查询表的列（即ID为1，列表选项为2，如果只有两个条目）

Note: If your desired drop down options are column two and not column one (makes things easier btw) then do the following change from above:

注意：如果您想要的下拉选项是第二列而不是第一列（顺便说一下让事情更容易），那么从上面进行以下更改：

1. Property Sheet > Format > Column Count= set the desired number of columns to two.
2. Property Sheet > Format > Column Widths= add two entries 0",1" which means the ID field is not visible (0") and the options field is one-inch.

2. 属性表 > 格式 > 列数= 将所需的列数设置为 2。
3. 属性表 > 格式 > 列宽= 添加两个条目 0",1"，这意味着 ID 字段不可见 (0")，而选项字段为一英寸。

The easiest way to sort this out in the beginning is to view the column header names to know what you are looking at in the meantime by doing the following: Property Sheet > Format > Column Heads= set to yes. At least you will know what is showing and whether or not you are on the right track.

在开始时解决这个问题的最简单方法是通过执行以下操作查看列标题名称以了解您同时查看的内容：属性表 > 格式 >列标题 = 设置为是。至少你会知道显示的是什么以及你是否在正确的轨道上。