How do I get a linked list to display more then just the first search item in Access?

Question

I just feel like I'm missing something really stupid. I have a simple access database I'm using for tracking my projects. I have multiple individuals and I have multiple projects. For the project, I have a drop down and I can select the lead for the project from my list of employees. No problem. however, it only displays the first name of the person. How do I get it to display the first and last name?

• Table 1: ID, firstName, lastName
• Table 2: ID, lead, notes - "lead" is a long integer.

The Row Source is

SELECT [Table1].[ID], [Table1].[lastName], [Table1].[firstName], [Table1].[Position]
FROM Applicants
ORDER BY [lastName], [firstName];

On the Form, I get the drop down box. It shows the first, last name, and what position they have but once I select the person I want, it only shows the first name.

The database is far more complex then this but none of the other information is relevant.

Answer 1

One option would be to create a display column fullName that concats your first and last name values into a single column. I believe the following should work in your Row Source:

SELECT [Table1].[ID], [Table1].[lastName] & ", " & [Table1].[firstName] as fullName, [Table1].[Position]
FROM Applicants
ORDER BY [Table1].[lastName] & ", " & [Table1].[firstName];

A couple notes:

Even though we alias [Table1].[lastName] & ", " & [Table1].[firstName] as fullName, we can't order by the alias, so we repeat the expression in the ORDER BY.

Using the above query, your column count will be 3 instead of 4, so you may need to change the column count property of the ComboBox.

I'm not very Access savvy, so I'd suggest checking other properties to make sure I haven't missed anything else.