How to Use a Combobox in Access Form to Update Record with Foreign Key But Display a Lookup of That Key

How to use a combobox in Access form to update record with foreign key but display a lookup of that key?

Try this for your combo box:

Control Source: Projects.RequestStatus

Row Source: SELECT ID, RequestStatus FROM RequestStatus

Bound Column: 1

Column Count: 2

Column Widths: 0";1"

That way your combo box has two columns (ID and RequestStatus), but the first column is hidden because its width is zero.

MSACCESS. Forms- Dropdown to update foreign key

The the RowSource property of the combobox should only contain a lookup list. It should not be retrieving records from the company table.

The RowSource property should be:

SELECT Country_ID, Country FROM table_country ORDER BY Country

Set Column Count to 2

Set Bound Column to 1

Set Column Width to 0 as you don't want to display the country_id only country.

ms-access: edit form entries using foreign keys

You want combobox to save the EM_ID but display the EM_Name? Use a multi-column combobox.

RowSource: SELECT EM_ID, EM_Name FROM T_Employee
ColumnCount: 2

ColumnWidths: 0";1"

BoundColumn: 1

ControlSource: CR_Employee

BTW, name parts should be in separate fields like: FName, LName, MName. Then RowSource could be: SELECT EM_ID, LName & ", " & FName AS FName FROM T_Employee;.

No need to include T_Employee in the form RecordSource unless there are more fields you want to display, such as address info. If you do include T_Employee table then set any controls bound to its fields as Locked Yes and TabStop No.

How can I display a *foreign* field value in a text box?

You can use DlookUp as the control source of a textbox:

 =DlookUp("[Name]", "Customer", "ID=" & Cust_ID)

Syntax: What to look up, table name, where statement

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.

Note that Name is a very bad name indeed for anything. I suggest you rename the field immediately before things get worse.

It can be useful to know the error(s).

Updating foreign key values in bound ms access form fed with a view

Stolzenberg,

In your view_X add

Select
  Tbl_Main.Quality_Note,
  Tbl_ProductGroup.PG_Description,
  Tbl_Main.PG_ID
From Tbl_Main inner join Tbl_ProductGroup on Tbl_Main.PG_ID = Tbl_ProductGroup.ID

In your front-end form add a combobox bounding to the value PG_ID. As the Record/RowSource for the combobox add

SELECT
    Tbl_ProductGroup.ID,
    Tbl_ProductGroup.PG_Description
FROM Tbl_ProductGroup;

Finally set the combobox's column count to 2 and column width to 0;
this will hide the first key column and only show the product description.

let us know if you are stuck.

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

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.

---

Related Topics