Just in case..... Create a form and put a single Treeview control on the form.
Name it Treeview0
copy this code into the form's module
Change the table names and field names to match your own
By the way... Using just "ID" in each table will GET REAL CONFUSING after a
while. Better to name the ID field Employee_ID in the Employee table than it
is to name it just ID. Once you start joining table after table with ID all
in them it will get confusing about which ID you are really looking at. Just
a thought....
Oh... I did not pend much time "formatting" the data... you can play with
that... spacing and such. I also put all the info in one line like you
showed, but you could just as easily have it listed one under another like
this
- Joe Smith
Title - 1
Taken - 1/1/2009
Due - 10/1/2009
making the data expandable at each level if you wished
-Joe Smith
- Title - 1
Taken - 1/1/2009
Due - 10/1/2009
code ************************************
Option Compare Database
Option Explicit
Public WithEvents TrVw As TreeView
Private Sub Form_Load()
Set TrVw = Me.TreeView0.Object
PopulateTree1
End Sub
Private Sub PopulateTree1()
'this will popluate the names
Dim EmplID As Long
Dim rst As New ADODB.Recordset
Dim nod As Node
Dim nod2 As Node
Set nod = TrVw.Nodes.Add(, , "TrSch", "Training")
nod.EnsureVisible
nod.Expanded = True
Set nod2 = TrVw.Nodes.Add(nod.Key, tvwChild, CStr("T1"), "Title" & "
" & "Taken" & " " & "Due")
'This will only give you employees that actually have a training record
With rst
.Open "SELECT DISTINCTROW Employees.EmployeeID, Employees.LastName,
Employees.FirstName " & _
"FROM Employees INNER JOIN [employee training] ON Employees.EmployeeID =
[employee training].Employee_ID " & _
"ORDER BY Employees.LastName;", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
Do Until .EOF
EmplID = !EmployeeID
With TrVw
'the node is added as Relative, Relation,Key,Text, image (not
using here)
Set nod2 = .Nodes.Add(nod.Key, tvwChild, CStr("E" & rst!
EmployeeID), rst!LastName & ", " & rst!FirstName)
PopulateTree2 EmplID, CStr("E" & rst!EmployeeID)
nod2.Expanded = True
End With
.MoveNext
Loop
End With
End Sub
Private Sub PopulateTree2(EmployeeID As Long, NodKey As String)
'this will populate the training
Dim rst As New ADODB.Recordset
With rst
.Open "SELECT [employee training].Employee_ID, [employee training].
Trng_ID, [employee training].Taken, [employee training].Due, Training.Title "
& _
"FROM [employee training] INNER JOIN Training ON [employee training].
Trng_ID = Training.Training_ID " & _
"WHERE ((([employee training].Employee_ID)=" & EmployeeID & "));",
CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Do Until .EOF
With TrVw
.Nodes.Add NodKey, tvwChild, "", rst!Title & " " &
(Nz(rst!Taken, "-")) & " " & (Nz(rst!Due, "-"))
'no key need because there are no relationships below this
level to have a relation with
End With
.MoveNext
Loop
.Close
End With
End Sub
Code end ******************************
If you dont know how to do a treeview let me know and I will send you the
basic code. Fairly straight forward and once you see it you will be able to
use it over and over.
