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.

score 1 · Answer 1 · answered Feb 02 '13 at 16:06

1

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.

answered Feb 02 '13 at 16:06

Mark Bertenshaw

5,594
2
27
40

it's the Connection procedure duty to make the needed connections, I have a module like this: 'Public cn As New ADODB.connection Public rs As New ADODB.Recordset Sub connection() Set cn = New ADODB.connection With cn .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & App.Path & "\sch.accdb; Persist Security Info=False" .Open .CursorLocation = adUseClient End With End Sub' – user2035282 Feb 03 '13 at 10:05

score 1 · Answer 2 · answered Feb 04 '13 at 21:42

1

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." :)

answered Feb 04 '13 at 21:42

BobRodes

5,990
2
24
26

score 0 · Answer 3 · answered Feb 02 '13 at 14:33

0

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

answered Feb 02 '13 at 14:33

F25rT

114
1
10

visual basic 6 access 2007 database programming cascading combo boxes

3 Answers3