How do I limit criteria to a combo box in an Access Query?

[decent1]

1. How do I limit a query to [tblSchools]![SCH_NMBR] with a combo-box?
2. Or, how do I make a form with a combo-box that filters
[tblSchools]![SCH_NMBR] when I run the query (outside of the simple query for
reports)? I have no problem filtering for forms; just haven't figured out
how to filter for queries!

DESIRED: A Make-Table Query that returns [tblStudents]![StudentNo] for all
students in a specific school [tblSchools]![SCH_NMBR],
with a specific graduation date [tblStudent_Schools]![CGD].
I have no problem with [tblStudent_Schools]![CGD].

MORE INFO:
1. [tblStudents]: Contains specific student ID data, address, phone, etc.
One-to-Many relationship with [tblStudent_Schools]. Key: [StudentNo].
2. [tblSchools]: Contains all school-specific data, school length,
location, but not class start or class graduation dates. One-to-Many
relationship with [tblStudent_Schools]. Key: [SCH_NMBR]
3. [tblStudent_Schools]: Contains class start date and graduation date,
plus student's temporary dormitory address associated with that school. A
student can be registered in many schools, but not in the same school twice.
Records are added or changed from the data-entry form, bound by
[tblStudents]![StudentNo] and schools are added with combo-box bound to
[tblSchools]![SCH_NMBR]. Key: [StudentNo]/[SCH_NMBR].
4. Data entry is limited almost exclusivey through combo-boxes to prevent
data-entry errors. The only fat-finger entries are dates for queries, with
criteria parameters established to limit errors.
- Help with this query/form/combo-box challenge will be greatly appreciated!
dlm

 

[MGFoster]

1. How do I limit a query to [tblSchools]![SCH_NMBR] with a combo-box?
2. Or, how do I make a form with a combo-box that filters
[tblSchools]![SCH_NMBR] when I run the query (outside of the simple query for
reports)? I have no problem filtering for forms; just haven't figured out
how to filter for queries!

DESIRED: A Make-Table Query that returns [tblStudents]![StudentNo] for all
students in a specific school [tblSchools]![SCH_NMBR],
with a specific graduation date [tblStudent_Schools]![CGD].
I have no problem with [tblStudent_Schools]![CGD].

MORE INFO:
1. [tblStudents]: Contains specific student ID data, address, phone, etc.
One-to-Many relationship with [tblStudent_Schools]. Key: [StudentNo].
2. [tblSchools]: Contains all school-specific data, school length,
location, but not class start or class graduation dates. One-to-Many
relationship with [tblStudent_Schools]. Key: [SCH_NMBR]
3. [tblStudent_Schools]: Contains class start date and graduation date,
plus student's temporary dormitory address associated with that school. A
student can be registered in many schools, but not in the same school twice.
Records are added or changed from the data-entry form, bound by
[tblStudents]![StudentNo] and schools are added with combo-box bound to
[tblSchools]![SCH_NMBR]. Key: [StudentNo]/[SCH_NMBR].
4. Data entry is limited almost exclusivey through combo-boxes to prevent
data-entry errors. The only fat-finger entries are dates for queries, with
criteria parameters established to limit errors.
- Help with this query/form/combo-box challenge will be greatly appreciated!
dlm

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Here's a solution to your "Desired" request. I'd suggest using just a
SELECT query (a View) instead of a make-table query:

PARAMETERS [School Number?] Long, [Graduation Date?] Date;
SELECT S.StudentNo
FROM tblSchools AS S INNER JOIN tblStudent_Schools AS SC ON
S.StudentNo=SC.StudentNo
WHERE S.SCH_NMBR = [School Number?]
AND SC.CGD = [Graduation Date?]

You can use Form references in the PARAMETERS clause instead of user
defined parameters. E.g.:

PARAMETERS Forms!FormName!ControlName Long;
SELECT ...
FROM ...
WHERE S.SCH_NMBR = Forms!FormName!ControlName

If you put an unbound ComboBox in the Form Header and used it's
AfterUpdate event to requery the form's RecordSource query you can
change the info displayed in the form. The form's RecordSource would
have a PARAMETER clause, like the above example, using the ComboBox name
as the ControlName.

Private Sub ComboBoxName_AfterUpdate()
Me.Requery
End Sub
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSbxJBoechKqOuFEgEQL4kgCffKmEpiGPFc6sYHJs4+arbLWWBgMAnjlo
IaEmhQA8NK42SbYJnvVe6Ae0
=HrqF
-----END PGP SIGNATURE-----