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 ....

 

[Pat Hartman]

You need to use a query that joins the employee table to itself.

Create a new query. Add the employee table to the grid twice. Notice that the name of the second instance has a suffix of - _1 - this will distinguish it from the first instance. Draw a join line from the ReportsTo field of the first instance to the EmpID of the second instance. Select fields for the employee from the first instance of the emp table. Select fields for the supervisor from the second instance of the table. Since the column names will be the same, you'll need to rename them to avoid confusion. Change the Field cell of the supervisor's Surname to:
SuperSurname:Surname
Change the Field cell of the supervisor's Givenname to:
SuperGivenname:Givenname
and so on with all the supervisor fields that you select.

 

[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.

 

[Pat Hartman]

Glad to help.