anyone up for a treeview challenge? 1

[bfamo]

hello.
found a good example of how to use a treeview for browse through recordsets by Helen Feddema. However, in her example she has only got 2 levels in the tree. I want 3.
It should look something like this.
|-Author
|-Book titles
|-Reviews
I have already managed to populate the tree and everything workes just fine when I have implemented it into my system.
The only thing remaining now is to get the third level in there. I have been trying on my own making a third level (basically copy-paste method using level 2 as source and replacing the different names) but without luck so far. I'm finding it quite difficult wrapping my head around this whole treeview thing to be honest.
So, what do I write to get another level added to my existing code?
Please help me!! Thanks

Here's the code for the first level:

'Fill Level 1
      Set rst = dbs.OpenRecordset(strQuery1, dbOpenForwardOnly)

      Do Until rst.EOF
         strNode1Text = StrConv("Level1" & rst![LastNameFirst], _
            vbLowerCase)
         Set nod = .Nodes.Add(Key:=strNode1Text, _
            Text:=rst![LastNameFirst])
         'Expand the entire node
         nod.Expanded = True
         rst.MoveNext
      Loop
      rst.Close

Here's the code for the second level:

'Fill Level 2
      Set rst = dbs.OpenRecordset(strQuery2, dbOpenForwardOnly)

      Do Until rst.EOF
         strNode1Text = StrConv("Level1" & rst![Initialer], vbLowerCase)
         strNode2Text = StrConv("Level2" & rst![Navn], vbLowerCase)
         strVisibleText = rst![Navn]
         Debug.Print "Text1" & strNode1Text
         Debug.Print "Text2" & strNode2Text
         Debug.Print ""
        .Nodes.Add relative:=strNode1Text, _
            relationship:=tvwChild, _
            Key:=strNode2Text, _
            Text:=strVisibleText
          rst.MoveNext
      Loop
      rst.Close

 

[MajP]

Untested

'Fill Level 3
       Set rst = dbs.OpenRecordset(strQuery3, dbOpenForwardOnly)

      Do Until rst.EOF
         strNode2Text =  ' something that returns the same name from above of the
                         ' level 2 node  returned by StrConv("Level2" & rst![Navn], vbLowerCase)
         strNode3Text =  ' something that returns the text for the level 3 node
         strVisibleText = ' the visible text for this node
            .Nodes.Add relative:=strNode2Text, _
            relationship:=tvwChild, _
            Key:=strNode3Text, _
            Text:=strVisibleText
          rst.MoveNext
      Loop
      rst.Close

the variables are somewhat poorly named
strNodeNText is really being used as the "Key" property


.Nodes.Add relative:=strNode2Text, _
relationship:=tvwChild, _
Key:=strNode3Text, _
Text:=strVisibleText
rst.MoveNext

The above says. Add a node as a child node to a parent node whose key is "strNode2Text". Make the new node's key "strNode3Text", and make its visible text "strVisibleText".

 

[bfamo]

majp,
sorry, I see i have mixed my code here. The right code for the second level is:

      'Fill Level 2
      Set rst = dbs.OpenRecordset(strQuery2, dbOpenForwardOnly)

      Do Until rst.EOF
         strNode1Text = StrConv("Level1" & rst![LastNameFirst], vbLowerCase)
         strNode2Text = StrConv("Level2" & rst![Title], vbLowerCase)
         strVisibleText = rst![Title]
         .Nodes.Add relative:=strNode1Text, _
            relationship:=tvwChild, _
            Key:=strNode2Text, _
            Text:=strVisibleText
         rst.MoveNext
      Loop
      rst.Close

This is what I put in as level3:

'Fill Level 3
       Set rst = dbs.OpenRecordset(strQuery3, dbOpenForwardOnly)

      Do Until rst.EOF
         strNode2Text = StrConv("Level2" & rst![Title], vbLowerCase)
         strNode2Text = StrConv("Level3" & rst![Reviews], vbLowerCase)
         strVisibleText = rst![Reviews]
            .Nodes.Add relative:=strNode2Text, _
            relationship:=tvwChild, _
            Key:=strNode3Text, _
            Text:=strVisibleText
          rst.MoveNext
      Loop
      rst.Close

When I test this code, I get the message "Run-time error '35601': Element not found" and it highlights the following code under level 3:

            .Nodes.Add relative:=strNode2Text, _
            relationship:=tvwChild, _
            Key:=strNode3Text, _
            Text:=strVisibleText

 

[MajP]

Most likely it is here
.Nodes.Add relative:=strNode2Text,

If your text returned from "strNode2Text" is not correct it will not be able to find a parent node with the same key "strNode2Text". So put this in
debug.print strNode2Text

and ensure that there is a level 2 node with a key that matches "strNode2Text

 

[bfamo]

Got it! It was just a reference error.

'Fill Level 3
       Set rst = dbs.OpenRecordset(strQuery3, dbOpenForwardOnly)

      Do Until rst.EOF
         strNode2Text = StrConv("Level2" & rst![Title], vbLowerCase)
         strNode2Text[B](Replaced strNode2Text with strNode3Text)[/B] = StrConv("Level3" & rst![Reviews], vbLowerCase)
         strVisibleText = rst![Reviews]
            .Nodes.Add relative:=strNode2Text, _
            relationship:=tvwChild, _
            Key:=strNode3Text, _
            Text:=strVisibleText
          rst.MoveNext
      Loop
      rst.Close

It works! Thanks alot!

 

[bfamo]

When I have two identical names at the second level, I get the error message "not unique key in collection".
My plan, that obviously failed big time, was that the unique key was suppose to be set to level 3 automatically once level 3 got added.

I've tried reading thorugh the code and look it up in present posts here on the forum, and I really cant make any sence out of it...

Any advice on how to do this?

thanks

 

[PHV]

What is strQuery2 ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bfamo]

   Dim strMessage As String
   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset
   Dim intVBMsg As Integer
   Dim strQuery1 As String
   Dim strQuery2 As String
   Dim nod As Object
   Dim strNode1Text As String
   Dim strNode2Text As String
   Dim strVisibleText As String
   
   Set dbs = CurrentDb()
   strQuery1 = "qryEBookAuthors"
   strQuery2 = "qryEBooksByAuthor"

strQuery2 is a query that shows all the books that is written by the selected author. I have now added:
strQuery3 = "qryReviewsEBooks" which is all the reviews for the authors book.

This is an example to illustarte my problem, otherwise I obviously would not need to have two of the same authors.

In thread702-879892 FancyPrairie writes "Also, note that each node is assigned a unique key. Since several tables may have a field with the same name, I append a number to the key to force it to be unique." The way he does this is by setting i = i + 1 as a unique key.

Is this possible to implement in my code?

 

[MajP]

strNode2Text = StrConv("Level2" & rst![Title], vbLowerCase)

So your key for the level two nodes are
"Level2BookTitle"
Why do you get two books with the same title?

Author
Title
Review
Review
Title
Author

I always use a primary key from the table as the my key for the node. Usually this is an autoID.

Level 1 key - autoAuthorID
Level 2 Key - autoBookID
Level 3 Key - autoReviewID.

 

[PHV]

Have a look at the DISTINCT predicate in SQL.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MajP]

One thing wierd about a treeview is the key property. I do not understand it, but the key has to be a string and start with a nonnumeric character. "A1234" is a good key, but "1A234" throws an error. Anyone know why?

 

[bfamo]

Majp,
I'll try to be less confusing from now on...

When talking about these books and authors in my previous posts, It was meant as an example and not in my actual system.

My system is meant to store information about inspections.

Its suppose to looke like this:

|-Inspector initials
|-Name of inspected place
|-Inspection Date

In other words:
Its possible for an inspector to have been at one place on many occations, but not twice in one day. Therefore, It seems to me that the unique key here should be the date.

Let me give you a practical example:

|-JD
|-Place01
|-01.06.03
|-03.02.05
|-02.09.06
|-Place02
|-11.10.02
|-09.09.04
|-CF
|-etc.
|-etc.