Combo list dependant on main/subform field values

[JohnB]

Hi. Im using Access 2002 on XP.

I have a main form with a subform and a combo on the subform. The main
form, subform and combo are fed from a queries. The main form has a text
field txtSubject bound to field Subject in tblStudents and the subform has a
field txtSchoolID, bound to autonumber field SchoolID in tblSchools. The
combos query includes both of these fields.

Is it possible to include something in the combos query criteria, for the
Subject and SchoolID fields, such that the combo list shows only those
records that have the same Subject value as that being shown on the main form
record and the same SchoolID as that shown in the subform record?

So, if the main form shows Maths in the Subject field and the subform shows
334 as the SchoolID, then the combo dropdown list shows only those records
that have Maths as the Subject and 334 as the SchoolID.

Thanks for any help, JohnB

 

[Bill]

Yes.
In the RecordSource property of the combo box use a query that selects the
two fields from the two tables. In the criteria field of the query builder
set the criteria for the Subject field to Me.Parent!txtSubject and the
criteria for the SchoolID to Me!txtSchoolID.
When you change the Subject field in the parent form you must requery the
combobox with the afterupdate event.

 

[JohnB]

Excellent Bill.

Thank you. JohnB

Yes.
In the RecordSource property of the combo box use a query that selects the
two fields from the two tables. In the criteria field of the query builder
set the criteria for the Subject field to Me.Parent!txtSubject and the
criteria for the SchoolID to Me!txtSchoolID.
When you change the Subject field in the parent form you must requery the
combobox with the afterupdate event.
has