How to automatically display related value in a text box when a field is selected in a combo box

[accessLearn]

I have an employee table TblEmployee with employee id(auto num), name (txt) and role(numeric field)
I have another table with ID(autonum) and rolename
I have a form in which I have a combo box to select employee name and a textbox to automatically display role name.
The rowsource property for the combo box is


SELECT TblEmployee.EmpId, TblEmployee.EmpName, TblEmployee.Role FROM TblEmployee ORDER BY [EmpName];
and column width is 0";2";0"
I have the text box for the role and I want to display role name automatically, so Control Source property for the text box is = CboEmp.column(2)
where CboEmp is the name of combo box
but the problem is that it is displaying the number for the role as it is the numeric fiel and I want to display the actual role.
How can I do that?

Thanks!!

[orange]

You could try (untested not sure of syntax???)
Control Source property for the text box =DLookup("Rolename","tblRole","id = " & CboEmp.column(2))

Also, as you have doe in tblEmployee EmployeeID, you should consider RoleId in tblRole.
After a while it's hard to keep the ID's straight when all of the tables have an ID field.

[ssanfu]

The rowsource property for the combo box is
SELECT TblEmployee.EmpId, TblEmployee.EmpName, TblEmployee.Role FROM TblEmployee ORDER BY [EmpName];
and column width is 0";2";0"

So "TblEmployee.Role" is a number? Is it referenced by any other controls or code? If not then you could change the row source for the combo box .
Add the table "tblRole" to the SQL (linked PK to FK), delete the column "TblEmployee.Role", add a column for "tblRole.RoleName".

Something like

SELECT TblEmployee.EmpId, TblEmployee.EmpName, TblRole.RoleName 
FROM tblRole INNER JOIN TblEmployee ON tblRole.RoleID_PK = TblEmployee.Role_FK
ORDER BY [EmpName]

I always name my PK and FK field with a suffix ... change the names as required. Keeps me from getting confused (most of the time )

Shouldn't need any other changes.