Repost - Combo question

[Guest]

Hi. I posted this yesterday but have had no replies. Sorry to be impatient.
What I want to do is limit the records shown in a combo dropdown, based on
field values shown in a main and subform. I would have thought this had been
done before. Can anyone help please? If its impossible, please let me know.
Thanks, JohnB

Original post:

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

 

[Guest]

Hi. Ive just spent some time searching the newsgroups and stumbled on
something called Cascading Combos. I had said originally that my Subject and
SchoolID fields were shown as text fields in the form/subfom. I had
simplified this, cos they are actually combos. Stupid of me - I thought the
answer would suit either text fields or Combos.

I will now go off and read up on Cascading Combos but any general advice
would still be welcome. In essence, I want my third Combo to show records
based on the values selected in Combo1 and Combo2. Thanks, JohnB

 

[John Griffiths]

Hi. I posted this yesterday but have had no replies. Sorry to be impatient.
What I want to do is limit the records shown in a combo dropdown, based on
field values shown in a main and subform. I would have thought this had been
done before. Can anyone help please? If its impossible, please let me know.
Thanks, JohnB

Original post:

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

in the WHERE clause of the combo rowsource you can refer to the main form
using Forms!

SELECT ColumnA, ColumnB
FROM TableX
WHERE Subject = Forms!Students!Subject AND SchoolID =
Forms!Students!subformSchools!SchoolID

See "Refer to the value of a control or property in an expression" in help.

Regards John

 

[JohnB]

Thank you very much John.

So, if the main form contains combo cboSubject and the subform contains
combo cboSchool, the WHERE statement would be:

WHERE Subject = Forms!Students!cboSubject AND SchoolID =
Forms!Students!subformSchools!cboSchool!SchoolID

Also, does subformSchools assume that the subform name is Schools, or
subformSchools? I ask because Forms is spelt with a capital F but subform is
not.

Lastly, do I need to do any refreshing of records? The user will have chosen
a value in cboSubject some weeks before they select a value in cboSchool and
immediately afterwards, a value in the combo which holds your suggested
code.

Again, thank you for your help. JohnB