Value of combo box with condition from value of another combo box

[Khalil Handal]

Hi to all


I have a bounded form with combo boxes. One combo box is for school years cboSchoolYear, and a second combo box for Students Names cboStudentName.
My goal is to show students for the selected school year only.

Control source for cboSchoolYear = fkSchoolYears
row source of is:

SELECT tblSchoolYears.pkSchoolYears, tblSchoolYears.SchoolYear FROM tblSchoolYears ORDER BY tblSchoolYears.SchoolYear DESC;

Control source for cboStudentName = fkStudentGrade
Row source of cboStudentName is qryStudentsLookup


Where qryStudentsLookup is:

SELECT tblStudents.pkIdNumber, [FirstName] & " " & [FathersName] & " " & [GrandFatherName] & " " & [FamilyName] AS Student
FROM tblSchoolYears INNER JOIN (tblStudents INNER JOIN tblJoinStudentsYears ON tblStudents.pkIdNumber = tblJoinStudentsYears.fkStudentGrade) ON tblSchoolYears.pkSchoolYears = tblJoinStudentsYears.fkSchoolYears
WHERE (((tblSchoolYears.pkSchoolYears) Like [Forms]![frmMarksMain]![cboSchoolYear]))
GROUP BY tblStudents.pkIdNumber, [FirstName] & " " & [FathersName] & " " & [GrandFatherName] & " " & [FamilyName]
ORDER BY [FirstName] & " " & [FathersName] & " " & [GrandFatherName] & " " & [FamilyName];

Without the following criteria:
[Forms]![frmMarksMain]![cboSchoolYear]
The query shows ALL students; adding the WHERE criteria:
[Forms]![frmMarksMain]![cboSchoolYear]
Does not show any students at all (No records at all).

Note: The criteria in query design view does not accept the following:
Like [Forms]![frmMarksMain]![cboSchoolYear].column(0)


Any suggestions?

Khalil

[Welshgasman]

When you use Like you need to include an * I believe, one side or the other, or both.
You just want =

Always build your queries in small steps. Get the criteria correct, then add additional fields, and then the sort order/Grouping etc.
It would also depend on what column is bound in your first combo as well.