A challenge for the experts 1

[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

 

[Shortie]

Try using a Form with a list box. On opening the form, populate the List Box with data from the table.

1. Creat a form with source to point to your Table.
2. Put a text box on the form (Call it txtServers) that has control source of =Count(Field1).
3. Put a list box on the form (Call it lstTreeView).
4. Put a text box on you form for each Table Field (hide if necessary by setting visible property false)

Private Sub Form_Open()

   Dim rcnt as Integer
   Dim stLastField1() as String
   Dim stLastField2() as String
   Dim stLastField3() as String
   Dim stLastField4() as String

   stLastField1 = ""
   stLastField2 = ""
   stLastField3 = ""
   DoCmd.GoToRecord,,acLast

   For rcnt = 1 to Me!txtServers

      With lstTreeView

         If stLastField1 <> txtField1 then
              .AddItem txtField1 & iif(IsNull(txtField2),&quot;&quot;,&quot;¬&quot;)
              .AddItem txtField2 & iif(IsNull(txtField3),&quot;&quot;,&quot;¬&quot;)
              If Not IsNull(txtField3) then
                 .AddItem txtField3 & iif(IsNull(txtField4),&quot;&quot;,&quot;¬&quot;)
                 If Not IsNull(txtField4) then
                 .AddItem txtField4
              End If
         Else

blah blah blah

Sorry I aint go tome to finish it

 

[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

 

[thekon]

Put this code in the open event of the form wich has the Treeview control:

Private Sub Form_Open(Cancel As Integer)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim tree As TreeView
Dim n1 As Node, n2 As Node, n3 As Node, n4 As Node
Dim x As Integer

Set tree = Me![ActiveXCtl0].Object
Set db = CurrentDb
Set rst = db.OpenRecordset("Your_Table&quot
x = 1
While Not rst.EOF
Set n1 = tree.Nodes.Add(, , "Server" & x, CStr(Nz(rst!Field1)))
Set n2 = tree.Nodes.Add("Server" & x, tvwChild, "Level1" & x, CStr(Nz(rst!Field2)))
Set n3 = tree.Nodes.Add("Level1" & x, tvwChild, "Level2" & x, CStr(Nz(rst!Field3)))
Set n4 = tree.Nodes.Add("Level2" & x, tvwChild, , CStr(Nz(rst!Field4)))

n1.Expanded = True 'If you like them expanded
n2.Expanded = True
x = x + 1
rst.MoveNext
Wend

Set rst = Nothing
Set db = Nothing
End Sub

 

[thekon]

Quite complicated but much better :

Private Sub Form_Open(Cancel As Integer)
Dim db As DAO.Database
Dim rst1 As DAO.Recordset, rst2 As DAO.Recordset, rst3 As DAO.Recordset, rst4 As DAO.Recordset
Dim tree As TreeView
Dim n1 As Node, n2 As Node, n3 As Node, n4 As Node
Dim a As Integer, b As Integer, c As Integer, d As Integer
Dim str1 As String, str2 As String, str3 As String, str4 As String

Set tree = Me![ActiveXCtl0].Object
Set db = CurrentDb

Set rst1 = db.OpenRecordset("SELECT Field1 FROM Your_Table GROUP BY Field1&quot
While Not rst1.EOF
str1 = rst1!Field1
Set n1 = tree.Nodes.Add(, , str1, str1)
Set rst2 = db.OpenRecordset("SELECT Field2 FROM Your_Table WHERE Field1 = '" & str1 & "' GROUP BY Field2 HAVING Field2 Is Not Null&quot
While Not rst2.EOF
str2 = rst2!Field2
Set n2 = tree.Nodes.Add(str1, tvwChild, str2, str2)
Set rst3 = db.OpenRecordset("SELECT Field3 FROM Your_Table WHERE (Field1 = '" & str1 & "' AND Field2='" & str2 & "') GROUP BY Field3 HAVING Field3 Is Not Null&quot
While Not rst3.EOF
str3 = rst3!Field3
Set n3 = tree.Nodes.Add(str2, tvwChild, str3, str3)
Set rst4 = db.OpenRecordset("SELECT Field4 FROM Your_Table WHERE (Field1 = '" & str1 & "' AND Field2='" & str2 & "' AND Field3='" & str3 & "') GROUP BY Field4 HAVING Field4 Is Not Null&quot
While Not rst4.EOF
str4 = rst4!Field4
Set n4 = tree.Nodes.Add(str3, tvwChild, , str4)
rst4.MoveNext
Wend
Set rst4 = Nothing
rst3.MoveNext
Wend
Set rst3 = Nothing
rst2.MoveNext
Wend
Set rst2 = Nothing
rst1.MoveNext
Wend

Set rst1 = Nothing
Set db = Nothing
End Sub

 

[Djbell]

Hi Thekon

Thanks for the post but it keeps on coming up as Complie error.

User-defined type not defined on most of the Dim statements

Regards

Druid

 

[thekon]

Make sure u have references to the DAO object library and to the Microsoft Common Controls object library

 

[Djbell]

Hi Thekon

Yes I didnt have the Microsoft Common Controls referenced, I am very new at this and have never programmed before, so I am having to use some Access and VBA books. I am now having the following error, sorry for being a lamer.

Set tree = Me![ActiveXCtl0].Object

Type Mismatch

Regards

Djbell

 

[thekon]

ActiveXCtl0 is the name Access gives to the Treeview control by default (and for other ActiveX objects, but now we deal with Treeview ). Replace it with the name u gave to your control.

(Talking about Common Controls 6.0 (SP4) )

 

[Djbell]

Hi thekon

I hate to keep on bothering you but I have changed the line
Set tree = Me![ActiveXCtl0].Object to the name I have given the Treeview object but I still get the type mismatch. To test the rest of the code, I commented that line out and the following four lines now give me the following error. I am using Access 2000, will this make a difference.

Set n1 = tree.Nodes.Add(, , str1, str1)
Set n2 = tree.Nodes.Add(str1, tvwChild, str2, str2)
Set n3 = tree.Nodes.Add(str2, tvwChild, str3, str3)
Set n4 = tree.Nodes.Add(str3, tvwChild, , str4

Run time error 91
Object variable or with block variable not set

Regards

Djbell

 

[thekon]

If you use Treeview Control version 5.0 (SP2) make sure that the coresponding reference, Microsoft Windows Common Controls 5.0 (SP2), has higher priority in references from Microsoft Windows Common Controls 6.0 (SP4). On the other hand, if u use Treeview Control version 6.0 (SP4) do the opposite. And yes this is Access 2000.

HTH

 

[Djbell]

Thekon, Thankyou.

Programming is an art, but at the moment I only paint by numbers.

Regards

Djbell

 

[Djbell]

Hi Thekon

Sorry I have another question for you.

The table which makes up the treeview also contain another 3 fields, these are users, Directory access and File access. Is it possible that when I click on any part of the tree that I can get it to show me the associated Users, File and group access on another control such as a text box list box ect. For example

Table View

Field1 Field2 Field3 Field4 Field5 Field6 Field7

SERVER1 GROUPS FJM Admin All All
SERVER1 GROUPS FJM Dougie All All
SERVER1 HOME DOUGIE Admin All All
SERVER1 HOME DOUGIE Dougie All All
SERVER1 HOME CATHB Admin All All
SERVER1 HOME CATHB Cathb RX RX

So the form would look like:-

Treeview

SERVER1
GROUPS
FJM
HOME
DOUGIE
CATHB

So in the treeview if FJM was clicked on then a list box would display what users had access to it. In this case it would be Admin and Dougie. Then clicking on either Admin or Dougie would show you the associated File and directory rights in either a text box or something. I have tried but it will just not work for me.

Regards

Djbell

 

[Djbell]

Sorry Thekon

I also forgot to add, that my table has over 2000 records so it takes about 5 mins to load my Treeview form, is there anyway to speed this up.

Regards

Djbell

 

[dirkg]

Hi, just read this interesting thread and I wanted to try out myself but I'm already stucked with the reference: I do not find Microsoft Common Objects in my list. Could anyone tell me what the exact name of the dll is so that I can find it manually? Thanks a lot in advance.

Greetz,

Dirk

 

[Djbell]

Hi dirkg

You are looking for MSCOMCTL.OCX and ODBCCONF.DLL

Regards

Djbell