访问表单 - 如何使文本字段具有来自 SQL 查询的控制源? [英] Access form - how to make text field have a control source from a SQL query?

问题描述

我有两张桌子:

• 项目
  • 员工编号
  • 员工编号
  • 姓名

  我有一个查询，我基于包含以下项目的表单:

  I have a query I am basing a form on containing, among other items:

  SELECT e.Name FROM Projects p JOIN Employee e ON e.EmployeeID=p.EmployeeID
  

  当我基于这个查询在 Access 中创建一个表单时，我可以很容易地在我的表单上显示 e.Name，因为它是从查询中加入的.

  When I make a form in Access based on this query, I can very easily display e.Name on my form because it is joined from the query.

  我的真实例子当然比这个简单的例子复杂得多.以上适用于只读查询并且可以很好地扩展.但是，我想使用 Splitform 视图，即使是少量记录集的许多连接也会变得非常慢.考虑到我的大部分连接都像上面一样简单，我希望有一种方法可以删除尽可能多的连接.

  My real example is of course considerably more complicated than this simple example. The above works fine for read-only queries and scales well. However I would like to use a Splitform view and this becomes very slow with many joins for even small numbers of recordsets. Considering a large percentage of my joins are simple like the above, I am hoping for a way to remove as many as possible.

  在表单上，​​e.Name 将是只读的，不可更新.

  On the form, e.Name will be read only and not be update-able.

  在这个问题中，我能够成功地将组合框更改为查找.接受的答案允许组合框通过在组合框中显示 Employee.Name 字段来控制 Projects.EmployeeID.

  In this question I am able to successfully change a combo-box into a lookup. The accepted answer allows a combo box to control Projects.EmployeeID by displaying Employee.Name field in the combo box.

  我知道一种方法是使用组合框但禁用它.这看起来有点奇怪，因为它有下拉选择器但不可选.

  I know one way I could do this is use a combo-box but disable it. This would look a bit weird since it'd have the drop down selector but not be selectable.

  或者，我可以使它成为一个完全未绑定的字段，并通过运行快速查询、获取我正在搜索的文本值并相应地更新标签来编写 VBA 代码以在每次记录集更改时更新表单.

  Alternatively, I could make it a completely unbound field and write VBA code to update the form each time the recordset changes by running quick queries, getting the text value I am searching for, and updating a label accordingly.

  然而，这些都不太吸引人.

  Neither of these are overly appealing, however.

  • 如何基于主表字段中的 ID 在 Access 拆分表单上显示单个文本字段，这是非常简单的查询查找的结果?

  推荐答案

  你可以使用 Dlookup 非常简单地返回这个引用

  You can use Dlookup to return this reference very simply

  构造一个 Dlookup 公式，如:

  Construct a Dlookup formula like:

  =DLookUp("Name", "Employee", "EmployeeID =" & "[EmployeeID]"
  

  并将其用作文本框的 ControlSource.

  and use this as the ControlSource for the textbox.

  一些注意事项:

  • & 很重要，因为它将公式绑定到表单上显示的单个记录
  • [EmployeeID] 是指表单上显示的当前记录.这假设EmployeeID"包含在表单的查询中，无论是绑定到 Projects 表还是包含在查询中
  • The & is important as it binds the formula to the single record displayed on the form
  • [EmployeeID] refers to the current record displayed on the form. This assumes that "EmployeeID" is included in the query for the form, whether bound to the Projects table or included in the query

  这篇关于访问表单 - 如何使文本字段具有来自 SQL 查询的控制源?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！