Create a subform that depends on the relationship of a related table

[rjgcooper]

I have the following tables:

• School - contains a list of schools
• Staff - record staff at particular schools (school id is a foreign key)
• Distribution - record when materials are distributed to a certain teacher (staff id and resource id are foreign keys)
• Resource - contains a list of materials that can be distributed to a staff member

I have a form which is used for entering details of a school. It contains a subform that lists all the teachers at that school.

I want to create another separate subform in the school form that will record the distributions to each staff member. However, how do I constrain the list of staff to those that are recorded as being at the currently selected school?

I hope this makes sense. If not, I shall attempt to further clarify.

[slenish]

@rjgcooper
Hello,

Im having a little trouble understanding what you want to do but im going to take a guess and see if im on the right track.

sounds what you might want to do is create either in a subform or in the main form a drop down where you can select a school then, in another drop down it will reference that school name to a list of teachers, then you can select a teacher and then in a text box or drop down you can type in the distribution and resources for the teacher to go with the school.

I hope im on the right path ;)

[rjgcooper]

Almost.

There is a form which has all the school information on it. Then there is a tab with a sub form for adding staff members to the school.

There is a second tab for the sub form described above which will contain the staff details (a relationship from the staff table) as well as data about the materials and requested. Does this make more sense?

I can get the sub form to work. But I cannot get the list of teachers to be limited only to those who are listed as being at the school, the record of which is currently open and active in the main form.

Does this clarify the situation?