CODEWITHSUNDEEP
ms-access
Wasteland
Wasteland

Reputation: 5379

MS Access 2016 - table - lookup values

I can do a simple lookup field in a table to choose values from another table field. Suppose my new table has the first field as a lookup field (Surname). It gives you a long dropdown menu of all the surnames available.

Now, how do I limit the dropdown menu in the second field to restrict only to first names available, matching the above selected surname (not all the first names available)

The third field should automatically result in an unique ID (taken from another table that is assigned to this particular combination (Surname + First name) (Assuming there are no multiple people with the same surname + first name)

Thank you

Upvotes: 0

Views: 102

Answers (1)

June7
June7

Reputation: 21370

What you describe is known as 'cascading' or 'dependent' combobox. This requires code behind a form. This is a common topic How can i filter a combobox selection list based on another combobox selection

Advise never build lookups in table. Build combobox on form. http://access.mvps.org/Access/lookupfields.htm

Should not be saving name parts into another data table, save only EmpID.

Don't separately select surname and firstname. Concatenate name parts to a single value listed in combobox dropdown:

SELECT EmpID, Surname & ", " & Firstname AS Fullname FROM Employees ORDER BY Surname, Firstname;

To handle possibility of multiple people with same name (e.g. John Smith), include another identifying field in the concatenation, such as job title.

Set combobox properties to hide EmpID column and users see the descriptive info column but EmpID is value of combobox.

Upvotes: 1

Related Questions