How do I make foreign-key combo boxes user-friendly on an Access form?

Question

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?

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.

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).

Answer 1

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)

Answer 2

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.)

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.)

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.

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.