I have a form that is using information from multiple database tables to populate values. The form starts by having a teacher select a School from a dropdown list. The School ID is retrieved relevant to what school is selected. The teacher then selects themselves from a Teacher list that is dependent on the School ID. The Teacher ID is retrieved relevant to what teacher is selected along with sections relevant to that teacher.

The part I am now trying to figure out is based on the teacher and the section selected, I need to populate a student field. The Sections table lists a Student ID for each section associated with a student. There is then a Student table that links the Student ID with a Student Name. I can get the Student ID to populate a field and based on the selection populate a field with the Student Name. I need to do just the opposite though, I need the Teacher to pick a Student Name and fill another field with the Student ID. Any thoughts?