A Challenge for the experts

[Djbell]

I have a challenge, for the experts.

I have a table with information as follows.

Field1 Field2 Field3 Field4
SERVER1 GROUPS Accounts Admin
SERVER1 GROUPS Accounts 2001
SERVER1 GROUPS FJM
SERVER1 HOME DOUGIE MISC
SERVER1 HOME CATHB
SERVER1 SYSTEM MISC
SERVER2 OTHER
SERVER2 DATA STUFF

The table avove is an example of what my exported Access Rights look like. What I want to do is to create a treeview of this information automatically. All fields must be clickable as there is different user rights for each section. This treeview must be created automatically as this information will change often. Plus I have alot more users, groups and an other two servers. Treeview example

Server1¬
-Groups¬
-Accounts¬
-Admin
-Accounts¬
-2001
-FJM
-Home¬
-Dougie¬
-Misc

-Cathb
-System¬
-Misc
Server2¬
-Other
-Data¬
-Stuff


Regards

Djbell

 

[Djbell]

Sorry I may have not made myself clear in explaining what I want on this database. The treeview that I want to use is the Microsoft Treeview Control, that can be used if you select more controls in design view. I also forgot to mention that it is Access 2000 I am using.

Regards

Djbell

 

[AWithers]

I think the route to go is via recordset sets and 4 levels of loops, quite complicated but will work

here is some code I used for a similar exercise with just two levels of , I hope you can follow the logic

Hope it helps

Andy

Private Sub Form_Load()
Dim db As Database
Dim rs As Recordset
Dim rschild As Recordset

Dim noderef As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("select distinct parent from vwclientdata where parent is not null&quot
noderef = 1

'top level loop
'adds parent names

Do While rs.EOF = False
Dim parent As Node



With tree.Nodes
Set parent = .Add(, , "root" + Str(noderef), rs(0))



'2nd level loop
'add all children for that parent
Dim childsql As String
childsql = "select distinct clientname from vwclientdata where parent = """ & rs(0) & """"

Set rschild = db.OpenRecordset(childsql)

Do While rschild.EOF = False

Set parent = .Add("root" + Str(noderef), tvwChild, , rschild(0))
rschild.MoveNext
Loop


End With
rs.MoveNext
noderef = noderef + 1
Loop
Me.Refresh

End Sub