Treeview Database

[Memento]

The following code works, but only for ONE Supervisor level. I need to include all that apply.

Goal:
I'd like to have the Director on the Parent level with the Supervisor(Under the Director) next as a child, and the final level with the people under the Supervisors Team(based on the Supervisor child level).

Dim db As Connection
Dim rsDir As New ADODB.Recordset
Dim rsSuper As New ADODB.Recordset
Dim rsTeam As New ADODB.Recordset

Set db = New Connection

db.CursorLocation = adUseClient
db.Open "PROVIDER=MSDASQL;driver={SQL Server};server=sql_serverDB\staging;uid=;pwd=;database=TestDB;"

TreeView1.Nodes.Clear

'rsDir returns info about the Director based on their stf_id number.

rsDir.Open ("Select stf_id, stf_fn, stf_ln, stf_supteam FROM staff WHERE stf_id = '3304'"), db, adOpenStatic, adLockReadOnly

'stf_supteam is equal to the Directors stf_id. I want to return all the Supervisors based on the Directors stf_id.

rsSuper.Open ("SELECT stf_id, stf_fn, stf_ln, stf_supteam FROM staff WHERE stf_type='AS' and stf_supteam = '3304'"), db, adOpenStatic, adLockReadOnly

'Here's the PROBLEM...I'd like to get ALL of the Supervisors stf_id number from the above child, but as it is now, I only get the FIRST supervisor listed.

rsTeam.Open ("SELECT stf_id, stf_fn, stf_ln, stf_supteam FROM staff WHERE stf_type='AS' and stf_supteam= " & rsSuper!stf_id), db, adOpenStatic, adLockReadOnly

Do While Not rsDir.EOF
TreeView1.Nodes.Add , , "R" & rsDir!stf_id, rsDir!stf_ln & ", " & rsDir!stf_fn
rsDir.MoveNext

Loop
Do While Not rsSuper.EOF
TreeView1.Nodes.Add "R" & rsSuper!stf_supteam, tvwChild, "L" & rsSuper!stf_id, rsSuper!stf_ln _
& ", " & rsSuper!stf_fn
rsSuper.MoveNext

Loop
Do While Not rsTeam.EOF
TreeView1.Nodes.Add "L" & rsTeam!stf_supteam, tvwChild, "C" & rsTeam!stf_id, rsTeam!stf_ln _
& ", " & rsTeam!stf_fn
rsTeam.MoveNext
Loop
TreeView1.Nodes(TreeView1.Nodes.Count).EnsureVisible
TreeView1.Nodes(1).EnsureVisible

DoEvents
rsDir.Close
rsSuper.Close
rsTeam.Close

Set rsDir = Nothing
Set rsSuper = Nothing
Set rsTeam = Nothing

 

[Memento]

The problem I was having had everything to do with WHEN my code went through the loop, MoveNext, and Close recordset.
I moved the MoveNext and loops at then end of the subroutine. Also, the order of closing recordset mattered. I sure learned a lot getting this Treeview to work correctly.

rsTeam.MoveNext
Loop
rsTeam.Close
rsSuper.MoveNext
Loop
rsSuper.Close
rsDir.MoveNext
Loop
rsDir.Close