How do I bind a text box with a field, which doesn't belong to form's "Record Source" table, through the Design View?
Example: I have "Order.cust_id" (Record Source=Order) and I want to display "Customers.name". I believe it is trivial but I have no experience with MS Access. I tried to use the text box "Control Source" property but no luck.
如何通过设计视图将文本框与不属于表单“记录源”表的字段绑定?示例:我有“Order.cust_id”(记录源=订单),我想显示“Customers.name”。我相信这是微不足道的,但我没有使用MS Access的经验。我试图使用文本框“控制源”属性,但没有运气。
3 个解决方案
#1
You can use DlookUp as the control source of a textbox:
您可以使用DlookUp作为文本框的控件源:
=DlookUp("[Name]", "Customer", "ID=" & Cust_ID)
Syntax: What to look up, table name, where statement
语法:查找内容,表名,where语句
The Where statement should follow the rules for Jet SQL, which means that you must use delimiters if the field is text or date format.
Where语句应遵循Jet SQL的规则,这意味着如果字段是文本或日期格式,则必须使用分隔符。
Note that Name is a very bad name indeed for anything. I suggest you rename the field immediately before things get worse.
请注意,Name确实是一个非常糟糕的名字。我建议你在情况恶化之前立即重命名。
It can be useful to know the error(s).
了解错误可能很有用。
#2
One method would be to convert the text box to a combo box. Then set the row source to include both the cust_Id and the Customer.Name from the customer table. SQL statement example
一种方法是将文本框转换为组合框。然后设置行源以包括customer表中的cust_Id和Customer.Name。 SQL语句示例
Select Cust_ID, Name From Customer
Order By Name;
By setting the number of columns to 2 and the column widths; the first column as zero (i.e. "0;6") then the foreign key would be hidden from the user and the customer name would be displayed.
通过将列数设置为2和列宽;第一列为零(即“0; 6”),则外键将对用户隐藏,并显示客户名称。
Note this method does force you to have limit to list set to true.
请注意,此方法会强制您将列表集限制为true。
Also you do end up with a drop down list which may not be what you want.
此外,您最终会得到一个可能不是您想要的下拉列表。
#3
You could create a new View (e.g. OrdersAndCustomerNames), select all the columns you want to use in the form, then instead of using the Order table as Record Source, you would just switch to OrdersAndCustomerNames. You say you have no experience with MS Access, so I am guessing you are not building anything huge and overly complicated, so I would do it this way. I am quite sure it can be done more elegantly but this will do for now.
您可以创建一个新视图(例如OrdersAndCustomerNames),选择要在表单中使用的所有列,然后只需切换到OrdersAndCustomerNames,而不是使用Order表作为Record Source。你说你没有MS Access的经验,所以我猜你没有构建任何巨大而过于复杂的东西,所以我会这样做。我很确定它可以更优雅地完成,但现在这样做。
#1
You can use DlookUp as the control source of a textbox:
您可以使用DlookUp作为文本框的控件源:
=DlookUp("[Name]", "Customer", "ID=" & Cust_ID)
Syntax: What to look up, table name, where statement
语法:查找内容,表名,where语句
The Where statement should follow the rules for Jet SQL, which means that you must use delimiters if the field is text or date format.
Where语句应遵循Jet SQL的规则,这意味着如果字段是文本或日期格式,则必须使用分隔符。
Note that Name is a very bad name indeed for anything. I suggest you rename the field immediately before things get worse.
请注意,Name确实是一个非常糟糕的名字。我建议你在情况恶化之前立即重命名。
It can be useful to know the error(s).
了解错误可能很有用。
#2
One method would be to convert the text box to a combo box. Then set the row source to include both the cust_Id and the Customer.Name from the customer table. SQL statement example
一种方法是将文本框转换为组合框。然后设置行源以包括customer表中的cust_Id和Customer.Name。 SQL语句示例
Select Cust_ID, Name From Customer
Order By Name;
By setting the number of columns to 2 and the column widths; the first column as zero (i.e. "0;6") then the foreign key would be hidden from the user and the customer name would be displayed.
通过将列数设置为2和列宽;第一列为零(即“0; 6”),则外键将对用户隐藏,并显示客户名称。
Note this method does force you to have limit to list set to true.
请注意,此方法会强制您将列表集限制为true。
Also you do end up with a drop down list which may not be what you want.
此外,您最终会得到一个可能不是您想要的下拉列表。
#3
You could create a new View (e.g. OrdersAndCustomerNames), select all the columns you want to use in the form, then instead of using the Order table as Record Source, you would just switch to OrdersAndCustomerNames. You say you have no experience with MS Access, so I am guessing you are not building anything huge and overly complicated, so I would do it this way. I am quite sure it can be done more elegantly but this will do for now.
您可以创建一个新视图(例如OrdersAndCustomerNames),选择要在表单中使用的所有列,然后只需切换到OrdersAndCustomerNames,而不是使用Order表作为Record Source。你说你没有MS Access的经验,所以我猜你没有构建任何巨大而过于复杂的东西,所以我会这样做。我很确定它可以更优雅地完成,但现在这样做。