SQL 2000,访问查询
我有一个 SQL 数据库,其中我的字段“状态”之一包含 10 个条目之一(不是来自下拉列表)。我让一些人可以通过Access数据库访问数据,他们可以添加和修改条目。
我的问题是,在 Access 中,如何使“状态”字段成为用户可以从中选择的下拉列表(SQL 数据库中已有的 10 个条目)?这对他们来说会更容易,也意味着不会犯错误。
非常感谢
斯科特
I have a SQL database where one of my fields 'Status' has one of 10 entries in it (not from a dropdown list). I let some people have access to the data through an Access database, they can add and modify entries.
My question is, in Access, how can I make it so that the 'Status' field is a drop-down list from which the users can choose from (the 10 entries which are already in the SQL database)? It would be easier for them and also mean that mistakes cannot be made.
Many thanks
Scott
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
这里有几个问题:
表数据表不适合用户的用户界面。
您可以创建一个保存的 QueryDef,如果您查看字段的属性,第二个选项卡就像表设计中的第二个选项卡一样,并允许您使用组合框作为查询中的显示类型。我通常建议不要这样做,因为与表数据表一样,QueryDef 不是正确的 UI 元素,如果您在其他查询中使用保存的查询,则可能会遇到导致表字段中的查找的相同问题,例如坏主意。
您正在构建 UI,因此请使用 Access 为您提供的用于构建 UI 的工具。这意味着一种形式。您的表单的记录源将包含裸数据,并且您将在表单上创建一个组合框,该组合框绑定到表单后面的表中的字段,并显示查找表中的值。有一个向导可以引导您完成此操作。如果您非常喜欢数据表的外观(列大小、排序、显示/隐藏都是数据表中很好的功能),您可以将表单设置为显示为数据表。
我的建议是,要构建用户界面,请使用 Access 提供的用于创建用户界面的工具。在我看来,下拉列表属于表单,而不是其他地方。虽然我偶尔可能会在查询中添加一个以进行快速而肮脏的编辑,但我永远不会在用户将要使用的对象中这样做。
Several issues here:
table datasheets are not suitable user interface for users.
you can create a saved QueryDef and if you view the properties of a field, the second tab is just like the second tab in table design, and allows you to use a combo box as your display type in your query. I would generally recommend against this, as, like table datasheets, a QueryDef is not a proper UI element, and if you use the saved query in other queries, you can run into the same problems that cause lookups in table fields to be such as bad idea.
you're building a UI, so use the tools that Access provides you for building a UI. That means a form. Your form's recordsource would have the bare data, and you'd create a combo box on your form that is bound to the field in the table behind your form, and displays the values from the lookup tables. There's a wizard to step you through this. If you like the look of datasheets so much (column sizing, sorting, show/hiding are all features that are nice in datasheets), you can set your form to display as a datasheet.
My advice is that for building a user interface, use the tools Access provides for creating user interface. In my opinion, a dropdown list belongs in a form, and nowhere else. While I occasionally might add one to a query for quick-and-dirty editing, I would never do that in objects that users are going to use.
除了 Andomar 描述的解决方案之外,您不得使用其他表作为查找源。您还可以在列表中提供查找值,该列表是硬编码在表定义中的。这对于查找不太可能更改的简单场景来说很好。
In addition to the solution described by Andomar you must not use another table as the source for your lookup. You can also provide the lookup-values in a list, which is hardcoded in the table-definition. This is fine for simple scenarios where the lookup is something that is not likely to be changed.
在 Access 中,您可以将查找信息添加到列中。这将自动显示一个下拉列表。
步骤 1:启动查找向导:
步骤 2:完成向导后,查找设置应如下所示:
步骤 3:当您的用户打开表格时,他们应该会看到下拉框:
In Access you can add lookup information to a column. That will automatically display a dropdown list.
Step 1: Start the lookup wizard:
Step 2: After the wizard, the lookup settings should look like this:
Step 3: When your users open a table, they should see the dropdown box:
通常的方法是使用表单上的组合框,其中行源取自查找表,绑定列设置为要更新的表的字段(列)。
The usual way to do this is to use a combo box on a form with the row source taken from the look-up table and the bound column set to the field (column) of the table to be updated.