I can not tell where you are headed with this db. It doesn't look like you have drawn out a plan on paper/whiteboard/etc before starting Access.
Here is your relationship window
So starting with the unbound form "frmSelectDistrict", you select a district, then click the select button.
The code that is executed is
Code:
Private Sub Command4_Click()
DoCmd.OpenForm "frmSchools", acNormal, , "DistrictID = " & Me.Combo2 & " " ' (1)
DoCmd.GoToRecord , , acNewRec ' (2)
DoCmd.Close acForm, "frmSelectDistrict" ' (3)
End Sub
1) this line opens form "frmSchools" and filters the records to match the value of Me.Combo2
2) this line goes to a new record
3) then a form is closed.
Nowhere has a value been added to any field for frmSchools.DistrictID. All that happens is the records that match the DistrictID are displayed.
You need to have a combo box on the form "frmSchools" to select the DistrictID.
For form "frmStaff", you need to add a combo box to select the "SchoolID".
Also, the field "StaffName" should be split into two fields: StaffFName and StaffLName.
-----------
I was wondering,
can a staff member work at more than one school at the same time?
can a staff member teach more than one subject?
If either is true/yes, you will need to change the structure - you will need to add a junction table (or 2).