Row Source of a ComboBox

[Nadia]

hello

just want to ask if we can use IN or = in the row source of a ComboBox
like:

Select [student].[SID] from Student where ( (student.deptno) IN (select [Department].[deptno] from Department where ((Department.Head_of_dept)=[Forms]![Employee_login]![Eid]));

 

[pr2-eugin]

Well the best way is to try.. Go create a query and try to see if you are getting the desired result.. if so go ahead..

Select [student].[SID] from Student where ( (student.deptno) IN (select [Department].[deptno] from Department where ((Department.Head_of_dept)=[Forms]![Employee_login]![Eid]));

instead of the highligted text use a value.. and see if the query pulls out the desired values, if it did then IN works else go with =

 

[Nadia]

i did try it .. and it didnt work
but when i used a value it worked ..

it ask me to insert the value of Forms!Employee_login!Eid
& BTW Eid is a value from another form called Employee login, if that make difference

 

[VilaRestal]

Access SQL is missing some stuff that proper SQL has like Exists, which is what you would to use here:

SELECT SID FROM Student WHERE EXISTS (SELECT * FROM Department WHERE Student.deptno = Department.deptno AND Department.Head_of_dept=[Forms]![Employee_login]![Eid]);

You can replicate that with Count > 0:

SELECT SID FROM Student WHERE (SELECT COUNT(*) FROM Department WHERE Student.deptno = Department.deptno AND Department.Head_of_dept=[Forms]![Employee_login]![Eid]) > 0;

Exists is more efficient the Count > 0. It returns true at the first one it finds. Count will count them all.

As for getting the [Forms]![Employee_login]![Eid] to work you might have to set the combobox's rowsource during the form load event:

Private Sub Form_Load()
   Combo1.RowSource = "SELECT SID FROM Student WHERE (SELECT COUNT(*) FROM Department WHERE  Student.deptno =  Department.deptno AND Department.Head_of_dept=" & Forms!Employee_login!Eid & ") > 0;"
End Sub

I believe this should work and would be most efficient:

Combo1.RowSource = "SELECT DISTINCT Student.SID FROM Student INNER JOIN Department ON Student.deptno = Department.deptno WHERE Department.Head_of_dept=" & Forms!Employee_login!Eid

 

[Nadia]

i've been searching for "Lood event" and didn't find it ..

 

[VilaRestal]

lol. It's the Load event not Lood

 

[Nadia]

i mean load!

 

[VilaRestal]

Every form has a load event. Called 'On Load' in the form's properties>events. Just below On Current, right at the top.

 

[Nadia]

i found it wasn't able to open Form properties until i select form from properties window ..
however, it still not working and asking me to enter department number

 

[VilaRestal]

Department number? As in deptno?

Do both the Student table and the Department table have that field? If either calls it by a different name then change the SQL accordingly.

 

[Nadia]

Sid.RowSource = "SELECT DISTINCT Student.SID FROM Student INNER JOIN Department ON Student.dept_no = Department.deptno WHERE Department.Head_of_dept='" & Forms!Employee_login!Eid & "'"

this one worked