Displaying Multiple Fields in a Text Box

[databasedonr]

Greetings All,

I have an employee table that goes like this:

tblEmployee

EmpID
Surname
GivenName
PositionNo
ReportsTo


The EmpID is a unique identifier; the position number (PositionNo) uniquely identifies a position, which an employee holds. With maternity leaves (and leave of other kinds), it is possible to have two employees have the same Position Number, or, if the position is vacant, I have a position with no one in it.

In all cases, a position reports to another position, which is reflected in the ReportsTo field.

So, in the database, I have a position 12345 that reports to position 98765. This reflects the manager -- so position 98765 is the manager of position 12345.

What I want to do on a form is display the GivenName and Surname of the Manager on the employees record. In my table, I have the ReportsTo Field containing the PositionNo data.

I hope this is clear... For example, my position number is 30754 and I report to 37041, but I want to see my manager's name on the form when I open it, not the number.

Thanks in advance -- I am convinced that this is simple, but so am I, as I can't figure it out.

 

[Mile-O]

As long as the surname and givenname fields are in the recordset underlying the form then:

=[Surname] & ", " & [GivenName]

on the ControlSource of a textbox should do the trick.

 

[databasedonr]

I tried Mile-O's suggestion, and that didn't work ...

If I replace the ReportsTo with Surname as the Control Source, I get the same user's last name, not the manager's; when I try and concatenate it to include both, it fails (returns #name).

The ReportsTo field gives me a value (a number) that refers to a different row in the same table. BTW, the form is based on the table, not a query ....

 

[databasedonr]

One Million Thanks, Pat Hartman.

This is not the first time you've made suggestions that have solved my challenges.

This worked a treat! I really appreciate your help -- thanks again.