'Cascading' a filter from form to subform

[Rhysickle]

Hello,

I have a form (xSchools) and a subform (xteachers)
I have an untied combo box in xSchools from which I select a school, which
then populates the form with the school's details - I used Access's default
visual basic for doing this:

Private Sub Combo10_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[School ID] = " & Str(Nz(Me![Combo10], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

However, once the school's details have been populated I want a similar
combo box in the xTeachers subform to:

- restrict itself to listing only those teachers from that particular school.
- perform a similar "populate the subform with the teacher's details" action
when a teacher is selected from this list. (Using the standard visual Basic
results in no action.)

Can someone please help me to edit and add to the above code to make this
work.

cheers

Rhys

 

[Ivan Grozney]

Assuming that you have a teachers table with a SchoolID to link to the school
table AND that your xTeacher SubForm is linked to the xSchool subform on
SCHOOLID
then restrict your CMB on the xTeacher subform to only those teachers where
forms!xteacher!schoolid = forms!xSchool!schoolid

hth

Vayna