Enter text into a field based on information contained in two other fields
In table 'Students', have fields...
In table 'Staff', have fields...
In table 'Staff', staff positions are entered using a popup menu. (i.e. teacher, consultant, social worker, etc.)
In the 'Students' table, want the 'StaffFirstName' and 'StaffLastName' to fill-in to the 'Teacher' field, in the Student table based on the 'Room' number (Students table) and 'Staff Positions' (Staff table, popup)
For example: for student: Jim, want to have his teacher's name filled into the 'Teacher' field based on Jim's room number (All staff are assigned to room numbers) and the Position popup.
Also have other staff positions I want to fill in automatically.
Is this do-able?
Thanks for your help,