Cascading Combo not showing results

[bluclouds9]

I am new to Access and have been charged with creating a database for our course alumni.

I currently have a "Contacts" form and am trying to create a subform to hold the course alumni information. I would like the first combo box to contain the course and the second to show the approriate years. Everything seems to be working except the years are not showing in the second combo box.

My tables are set up as:

CourseNames
CourseID (primary key)
CourseName (text)

Years
YearID (primary key)
Years (text)

CourseYears
CourseYearID (primary key)
CourseID (numbers)
YearID (numbers)

In the CourseYears table I have entered in the CourseID and YearID for the appropriate combinations. I am trying to create cascading combo boxes so that when the course is chosen, the appropriate years will appear.

I have two combo boxes: CourseName and CourseYear

The code for my CourseName after update is:

Private Sub CourseName_AfterUpdate()
Dim strSQL As String
 
strSQL = "SELECT CourseNames.CourseID, Years.YearID, Years.Years FROM CourseYears INNER JOIN " & _
         "(CourseNames INNER JOIN CourseYears ON CourseNames.CourseID = CourseYears.CourseID) " & _
         "ON Years.YearID = CourseYears.YearID WHERE CourseNames.CourseID= " & Me![CourseName].Column(0)
Me![CourseYear].RowSource = strSQL
Me![CourseYear].SetFocus
Me![CourseYear].Dropdown
End Sub

This is not showing the years in the second combo box.

Any help you could give me is much appreciated!

[NeoPa]

Hi. Check out Example Filtering on a Form and let us know if you manage to resolve it from this.

Welcome to Bytes!