0

So I am doing a project where we are attempting to allow our user to open the form, select (via a combobox) an instructor, and then return all the students who have had that instructor over the years in a table.

Without the WHERE clause the code runs, returning all the students who have had that instructor. But when I use the [Forms]![STUDENT FORM]![INSTRUCTOR] in the WHERE clause we suddenly get no results. Can anyone explain why this is? It has worked in the same form with a different combobox, but we didn't need to use any joins, so I'm wondering if that's the issue?

This is the SQL code - our form is named STUDENT FORM and the combobox is named INSTRUCTOR:

SELECT STUDENT.STU_LNAME, STUDENT.STU_FNAME, STUDENT.[STU_ INT],
STUDENT.STU_NICK, STUDENT.STU_YEAR, STUDENT.STU_PHONE, STUDENT.STU_EMAIL,
INSTRUCTOR.INSTRUCTOR_LNAME FROM 
(INSTRUCTOR INNER JOIN CLASS ON 
INSTRUCTOR.[INSTRUCTOR_ID] = CLASS.[INSTRUCTOR_ID]) INNER JOIN 
(STUDENT INNER JOIN ENROLL ON STUDENT.[STU_NUM] = ENROLL.[STU_NUM]) 
ON CLASS.[CLASS_ID] = ENROLL.[CLASS_ID]
WHERE (((INSTRUCTOR.INSTRUCTOR_LNAME)=[Forms]![STUDENT FORM]![INSTRUCTOR]));

Please let me know if you need any more information!

cableload
  • 4,215
  • 5
  • 36
  • 62
Jake D.
  • 3
  • 3
  • @HansUp There are three values in the ComboBox, all names. So we are trying to let the user select a last name, and then have the query return all students who have taken a class with that instructor. – Jake D. Apr 16 '16 at 23:52
  • I wasn't quite sure how you wanted me to use that - I ran JUST that, and it didn't work. Did you mean replacing the select statement with that one? edit - I also replaced just the SQL statement, and it didn't work either, sadly, just gave me a blank table. – Jake D. Apr 17 '16 at 00:03
  • Nope, no errors. It runs the code, but doesn't give any information, which is partly why I'm so confused - if I got an error, I could narrow it down. Instead it just returned a table with one column named [Forms]![STUDENT FORM]![INSTRUCTOR] – Jake D. Apr 17 '16 at 00:17
  • Sorry about the late response! I did what you said, and the answer just says NULL, although when I selected an Instructor, it gave me back their instructor ID. I feel this is somehow related. – Jake D. Apr 17 '16 at 03:58

1 Answers1

0

I bet your INSTRUCTOR combobox has two columns, with INSTRUCTOR_ID being the bound column and having width 0 (so only INSTRUCTOR_LNAME is visible).

So the value you need to filter for is INSTRUCTOR_ID, and you need to change the WHERE clause to:

WHERE (((INSTRUCTOR.INSTRUCTOR_ID)=[Forms]![STUDENT FORM]![INSTRUCTOR]));
Andre
  • 26,751
  • 7
  • 36
  • 80