Hi I have a problem with a couple of my forms in the attached database (minus sensitive data)

The forms I am having problmes with are:


Using the behavioural one as example:

When filling in the form the user selects the route that the behavioural incident took place on, in the next combo they can enter the school the route was going to and then in the next combo the operator which runs the route.

While only one operator can operate a route, some routes go to more than one school.

I have my tables all set up for this and normalised (see my table relationships)

New route info can be entered in in the relevant form frm_add_new_route
New Operator info can be entered in in the form frm_add_new_operator and the same with new schools. These are set up with subforms to take into account the one to many relationships in the database.

So far all this is working fine, off some Swithcboards that I have removed for space.

The issue I have is that when someone is filling in the Behavioural Incidents form, if they select a route I want it to automatically bring up the relevant bus operator in that part of the form and (this may be a little more complex) list only those schools on the selected route (for selection by the user) on that part of the form. e.g. Route 302 City and County of Swansea goes to both gowerton and YG Gwyr

I am still new to access so any help in easy to follow steps is greatly appreciated!