I've got two tables:
我有两张桌子:
Employees:
uid (number) | first_name (string) | last_name (string) | ...
Projects:
uid | project_title (string) | point_of_contact_id (FK: Employees.uid) | ...
I'd like to create a form for Projects with a "Point of Contact" combo box (dropdown) field. The display values should be "first_name last_name" but the backing data is the UID. How do I set up the form to show one thing to the user and save another thing to the table?
我想为具有“联系点”组合框(下拉列表)字段的项目创建表单。显示值应为“first_name last_name”,但后备数据为UID。如何设置表单以向用户显示一件事并将另一件事保存到表中?
I'd be fine with only being able to show one field (just "first_name" for example), since I can create a view with a full_name field.
我可以只显示一个字段(例如“first_name”),因为我可以创建一个带有full_name字段的视图。
Later:
If there is a way to do this at the table design level, I would prefer that, since then I would only have to set a setting per UID column (and there are many tables), rather than one setting per UID field (and there are many forms, each with several UID fields).
如果有一种方法可以在表设计级别执行此操作,我宁愿这样做,因为那时我只需要为每个UID列设置一个设置(并且有很多表),而不是每个UID字段设置一个(并且那里)是多种形式,每种形式都有几个UID字段。
2 个解决方案
#1
3
To expand on Loesje's answer, you use the Bound Column property along with Column Count and Column Widths when you are displaying multiple fields so that you can tell Access which one should be written to the database. (There are other ways to do this using VBA, but this should work for your particular case.)
要展开Loesje的答案,在显示多个字段时,可以使用“绑定列”属性以及“列数”和“列宽”,以便可以告诉Access应将哪一个写入数据库。 (还有其他方法可以使用VBA执行此操作,但这应该适用于您的特定情况。)
In your case, setting Row Source to select uid, first_name, last_name from tablename means that your Bound Column should be 1, for the first column in your Row Source (uid). This is the default value, so you'd only have to change it if you wanted to save a value from a different field. (For example, if you wanted to save last_name from the Row Source above, you'd set Bound Column to 3.)
在您的情况下,将Row Source设置为选择uid,first_name,来自tablename的last_name意味着对于行源(uid)中的第一列,您的Bound Column应为1。这是默认值,因此如果要从其他字段保存值,则只需更改它。 (例如,如果要从上面的行源保存last_name,则将Bound Column设置为3.)
Don't forget that when you set the widths of the other columns you are displaying, the combo box's Width property should be greater than or equal to the sum of the column widths, otherwise you may not see all the columns.
不要忘记,当您设置要显示的其他列的宽度时,组合框的Width属性应大于或等于列宽的总和,否则您可能看不到所有列。
There isn't a way to indicate at the table level that a form based on that table needs to pull particular columns, or that a particular column is the foreign key, but you can copy a combo box to other forms and it will carry all its properties with it. You can also copy the Row Source query and paste it into other combo boxes if that helps you.
没有办法在表级指示基于该表的表单需要提取特定列,或者特定列是外键,但是您可以将组合框复制到其他表单并且它将包含所有表它的属性。您还可以复制行源查询并将其粘贴到其他组合框中(如果这有助于您)。
#2
3
Set the rowsource of the dropdownbox to "select uid,first_name,lastname from tablename' and set the columnwidth to 0. This way the width of the first column is set to zero, so the user doesn't see it. (You can provide the width of the other columns seperating them with a semicolon, ie: 0cm;4cm;4cm)
将下拉框的行源设置为“从表名中选择uid,first_name,lastname”并将columnwidth设置为0.这样,第一列的宽度设置为零,因此用户看不到它。(您可以提供其他列的宽度用分号分隔,即:0cm; 4cm; 4cm)
#1
3
To expand on Loesje's answer, you use the Bound Column property along with Column Count and Column Widths when you are displaying multiple fields so that you can tell Access which one should be written to the database. (There are other ways to do this using VBA, but this should work for your particular case.)
要展开Loesje的答案,在显示多个字段时,可以使用“绑定列”属性以及“列数”和“列宽”,以便可以告诉Access应将哪一个写入数据库。 (还有其他方法可以使用VBA执行此操作,但这应该适用于您的特定情况。)
In your case, setting Row Source to select uid, first_name, last_name from tablename means that your Bound Column should be 1, for the first column in your Row Source (uid). This is the default value, so you'd only have to change it if you wanted to save a value from a different field. (For example, if you wanted to save last_name from the Row Source above, you'd set Bound Column to 3.)
在您的情况下,将Row Source设置为选择uid,first_name,来自tablename的last_name意味着对于行源(uid)中的第一列,您的Bound Column应为1。这是默认值,因此如果要从其他字段保存值,则只需更改它。 (例如,如果要从上面的行源保存last_name,则将Bound Column设置为3.)
Don't forget that when you set the widths of the other columns you are displaying, the combo box's Width property should be greater than or equal to the sum of the column widths, otherwise you may not see all the columns.
不要忘记,当您设置要显示的其他列的宽度时,组合框的Width属性应大于或等于列宽的总和,否则您可能看不到所有列。
There isn't a way to indicate at the table level that a form based on that table needs to pull particular columns, or that a particular column is the foreign key, but you can copy a combo box to other forms and it will carry all its properties with it. You can also copy the Row Source query and paste it into other combo boxes if that helps you.
没有办法在表级指示基于该表的表单需要提取特定列,或者特定列是外键,但是您可以将组合框复制到其他表单并且它将包含所有表它的属性。您还可以复制行源查询并将其粘贴到其他组合框中(如果这有助于您)。
#2
3
Set the rowsource of the dropdownbox to "select uid,first_name,lastname from tablename' and set the columnwidth to 0. This way the width of the first column is set to zero, so the user doesn't see it. (You can provide the width of the other columns seperating them with a semicolon, ie: 0cm;4cm;4cm)
将下拉框的行源设置为“从表名中选择uid,first_name,lastname”并将columnwidth设置为0.这样,第一列的宽度设置为零,因此用户看不到它。(您可以提供其他列的宽度用分号分隔,即:0cm; 4cm; 4cm)