visual basic 6 access 2007 database programming cascading combo boxes

Question

I have a table named: schoolInfo in access 2007 and it has two fields (schName and mjrName).

Now I'm trying to design a combo in Visual Basic 6 (cboMajors) which is related to the other combo (cboSchool).

As a matter of fact I want to have to cascading combo boxes. When I choose an Item in cboSchool the other combo should represents just the related majors for that school (records with schName=x and mjrName=y).

Private Sub Form_Activate()

connection
' the Connection is a code in module contains codes are needed to make the connection between form and the database

fill_schools
fill_majors

End Sub

Also,

Private Sub fill_schools()
 With rs

    .Open "select DISTINCT schName from tblSchoolsInfo", cn, 2, 3

        Do While Not .EOF
        cboSchool.AddItem (.Fields(0))
        .MoveNext
    Loop
  End With
  rs.Close
End Sub

Private Sub fill_majors()

 With rs
    .Open "select DISTINCT mjrName from tblSchoolsInfo where schName= '" & Me.cboSchool & " '", cn, 2, 3

        Do While Not .EOF
        cboMajors.AddItem (.Fields(0))
        .MoveNext
    Loop
  End With
End Sub

Now: the first combo get correct values but the second one is completely empty.

Answer 1

just a suggestion did you check the cboMajors.AddItem (.Fields(0)) <--- .Fields()

Answer 2

In the snippet of code you have given us, I can't see anywhere where you actually select the school in Form_Activate(). This means that by the end of that procedure, there will be no selection in school, so fill_majors() will execute:

select DISTINCT mjrName from tblSchoolsInfo where schName= ' '

Incidentally, is that trailing space deliberate? In which case, this won't return records even if a school is selected.

Answer 3

The OP solved this in dreamincode.net. He was tacking an extra space on the end of his combo box string: Me.cboSchool & " '"

I've always wanted to say this: "This behavior is by design." :)