Form textbox display problem.

[Justns11]

I'm currently working on an employee data form, and one of the text boxes is "reports to", which shows the employee ID of who the employee reports to (Their supervisor). The problem is I want this to display the supervisor's name instead of their employee ID. How should I go about doing this?

It's been a while since I last worked with access, so I'm kind of rusty.

Thanks a lot in advance.

(Attached is a screen shot of my current form, for better clarification)

 

[pbaldy]

Instead of a textbox, use a combo box with two columns. You want the first column to be the ID and the second the name. The first column is the bound column but is hidden. The combo wizard should walk you through this.

 

[Justns11]

Thanks a lot Paul. That works fine. The only problem is that the employee name is in two fields(first & last), so when the combo box is up, it only displays the first name(1st column), but when you click the arrow, both columns display. It's not really a big deal, but if you know of a way for me to see both columns before clicking the down arrow please let me know.

 

[pbaldy]

Instead of this as the rowsource:

SELECT ID, FirstName, LastName
FROM TableName

you can do something like

SELECT ID, FirstName & " " & LastName AS FullName
FROM TableName

 

[Justns11]

Okay, so I went to the row source for the combo box, opened the query builder, and then changed it to SQL View, and Changed this:

SELECT Employees.EmployeeID, Employees.FirstName, Employees.LastName
FROM Employees;

To this:

SELECT Employees.EmployeeID, Employees.FirstName & " " & Employees.LastName AS fullname
FROM Employees;

And when I click save and go back to the form, it doesn't change anything...and when I go back and check the SQL, it automatically changes back to the original. Any ideas?...like I said, I'm rusty at this stuff.

 

[pbaldy]

If you're using 2007, you're may be running into the third item down under Bugs:

http://allenbrowne.com/Access2007.html#Bugs

 

[Justns11]

Thanks alot Paul. I got it to work. The problem I'm having now is that I want to display the country which the employee lives in. The country is on another table, which has the composite PK of City and Zip Code. So in my control source I'd need two values, the city and zip code. I'm having trouble replicating what I just did, because of the composite PK. Any help? Thanks a million.