Listbox populate from another listbox

[Manos39]

Hello, i would like a solution about three list boxes that i have on an unbound form that i need to populate from selections on A) first and b) second
here it is

first on is lstInstructor

SELECT Tbl_Instructor.instructorID, Nz([iSurname] & " " & [iName]) AS Instructor, Tbl_Courses.courseID FROM Tbl_Instructor INNER JOIN Tbl_Courses ON Tbl_Instructor.instructorID = Tbl_Courses.courseID;

second on is lstGroups

SELECT Tbl_Courses.courseID AS ID, [dName] & " | " & [dDepartmName] AS Course, Tbl_Courses.courseID FROM Tbl_Departments INNER JOIN Tbl_Courses ON Tbl_Departments.departmID = Tbl_Courses.departmID ORDER BY Tbl_Courses.courseID;

and the third is lstAthlets

SELECT Tbl_Courses.courseID, " " & [aSurname] & " " & [aName] AS Athlet FROM Tbl_Departments INNER JOIN (Tbl_Athlets INNER JOIN (Tbl_Courses INNER JOIN Tbl_AthletCourse ON Tbl_Courses.courseID=Tbl_AthletCourse.courseID) ON Tbl_Athlets.atheltID=Tbl_AthletCourse.atheltID) ON Tbl_Departments.departmID=Tbl_Courses.departmID ORDER BY Tbl_Courses.courseID;

Any help would be appreciated

 

[MajP]

Your question is unclear. You have three listboxes. If the user selects one or more choices what do you want to happen?

Here they are more readable, which may help?

1
SELECT tbl_instructor.instructorid,
       Nz([isurname] & "" & [iname]) AS Instructor,
       tbl_courses.courseid
FROM   tbl_instructor
       INNER JOIN tbl_courses
               ON tbl_instructor.instructorid = tbl_courses.courseid;

2
SELECT tbl_courses.courseid             AS ID,
       [dname] & " | " & [ddepartmname] AS Course,
       tbl_courses.courseid
FROM   tbl_departments
       INNER JOIN tbl_courses
               ON tbl_departments.departmid = tbl_courses.departmid
ORDER  BY tbl_courses.courseid;

3
SELECT tbl_courses.courseid,
       "" & [asurname] & "" & [aname] AS Athlet
FROM   tbl_departments
       INNER JOIN (tbl_athlets
                   INNER JOIN (tbl_courses
                               INNER JOIN tbl_athletcourse
                                       ON tbl_courses.courseid =
                                          tbl_athletcourse.courseid)
                           ON tbl_athlets.atheltid = tbl_athletcourse.atheltid)
               ON tbl_departments.departmid = tbl_courses.departmid
ORDER  BY tbl_courses.courseid;

 

[Manos39]

Your question is unclear. You have three listboxes. If the user selects one or more choices what do you want to happen?

Here they are more readable, which may help?

1
SELECT tbl_instructor.instructorid,
       Nz([isurname] & "" & [iname]) AS Instructor,
       tbl_courses.courseid
FROM   tbl_instructor
       INNER JOIN tbl_courses
               ON tbl_instructor.instructorid = tbl_courses.courseid;

2
SELECT tbl_courses.courseid             AS ID,
       [dname] & " | " & [ddepartmname] AS Course,
       tbl_courses.courseid
FROM   tbl_departments
       INNER JOIN tbl_courses
               ON tbl_departments.departmid = tbl_courses.departmid
ORDER  BY tbl_courses.courseid;

3
SELECT tbl_courses.courseid,
       "" & [asurname] & "" & [aname] AS Athlet
FROM   tbl_departments
       INNER JOIN (tbl_athlets
                   INNER JOIN (tbl_courses
                               INNER JOIN tbl_athletcourse
                                       ON tbl_courses.courseid =
                                          tbl_athletcourse.courseid)
                           ON tbl_athlets.atheltid = tbl_athletcourse.atheltid)
               ON tbl_departments.departmid = tbl_courses.departmid
ORDER  BY tbl_courses.courseid;

Because mostly the tennis instructor will use the db, besides husband (manos39) i would like a) tennis instructor selected by first listbox to see her courses ..- I should had involved date of the course in the second listbox lstGroups, b) then, in the third listbox , lstAthlets, to provide info about who {athlete} is in the group selected by the second listbox... Thank you

 

[Pat Hartman]

Here is an example of cascading combos. It has only two levels. In the example, the second level refers to the control bound to the first level. Subsequent levels work the same way. They refer to the control "above" them in the hierarchy so the third refers to the second. The fourth refers to the third.

Autofill and other examples

This database shows examples of how autofill works. It also shows examples of filtering with a multi-select listbox. I added an updated version of the database with additional examples.

www.access-programmers.co.uk

Sorry

 

[Manos39]

Here is an example of cascading combos. It has only two levels. In the example, the second level refers to the control bound to the first level. Subsequent levels work the same way. They refer to the control "above" them in the hierarchy so the third refers to the second. The fourth refers to the third.

Sorry i cannot see the example..

 

[Pat Hartman]

Soryr, I forgot to paste it
I updated the post

 

[Manos39]

Could you please help me solving this?
I have tried a lot with listboxes and comboboxes in my db wich is intended to hold tennis classes

In form named Fr_InstructorCourseAthlet i need help making three listboxes a) On first on top, selecting a tennis Instructor to show his courses in the second listbox and b) picking a course in the second, to show athletes in the third listbox

I uploaded my db