help needed to make a unique key! 2

[bfamo]

Still working on making unique keys for my treeview. I've read up on how to make unique keys and also tried making some from examples found in previous posts in the forum.

The method of making keys I currently does not allow me to have nodes with identical names because the key is[/] the nodes name.

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

To solve this I tried making a key which adds a nuber on the end.

 .Nodes.Add relative:=strNode1Text, relationship:=tvwChild, Key:=strNode2Text[B] & i[/b], Text:=strVisibleText  [b]|Where i is "i=i+1"|[/b]

I keep getting "Element not found" so I'm obviously doing something wrong.

Is this whole "i=i+1"-idea a good way to go when I want to make unique keys?

I would greatly appreciate any comments or suggestions of not too advanced character, as I am in great need of assistance!! thanks alot in advance!

Here is the code which need to have the keys added to it:

Function TreeTilsynshistorikk_Fill()

   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 strQuery3 As String
   Dim nod As Object
   Dim strNode1Text As String
   Dim strNode2Text As String
   Dim strNode3Text As String
   Dim strVisibleText As String
      
   Set dbs = CurrentDb()
   strQuery1 = "QryTilsInsTree"
   strQuery2 = "QryTilsInsProdTree"
   strQuery3 = "QryTilsInsProdDatoTree"

With Me![TreeTilsynshistorikk]
      'Fill Level 1
      Set rst = dbs.OpenRecordset(strQuery1, dbOpenForwardOnly)

      Do Until rst.EOF
         strNode1Text = StrConv("Level1" & rst![Initialer], vbLowerCase)
        Set nod = .Nodes.Add(Key:=strNode1Text, Text:=rst![Initialer])
         'Expand the entire node
         nod.Expanded = False
         rst.MoveNext
      Loop
      rst.Close
      
      '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]
        .Nodes.Add relative:=strNode1Text, relationship:=tvwChild, Key:=strNode2Text, Text:=strVisibleText
          rst.MoveNext
      Loop
      rst.Close
      
'Fill Level 3
       Set rst = dbs.OpenRecordset(strQuery3, dbOpenForwardOnly)

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

   End With
   dbs.Close


End Function

 

[MichaelRed]

you should really treat the development of tree views as an exercise in recursion. I realize that you request a

bfanmo, " ... of not too advanced character ... "

on hte other hand this is a fundamental issue for the type of process you are attempting.

In the snippets, you mentiion adding the index (i to overcome the use of the same node name. Unfortunatly, this is not evidenced in the larges code block following. It could be crucial to even the minimal code you do have to place the generation and use of the index properly in your code.




MichaelRed

 

[MajP]

If you are not going to retrieve a record based on a node, then the key can be anything even random. I bet this would work for all the keys
key:= CSTR("A" & CLNG(rnd()*10000000))

I always use a some concatenation of the primary key so that when I click on the node or drag the node, I can retrieve or modify the record that the node is related to.
As I said the Key property is just strange. The key property has to be a string, but it has to be a special string. I have no idea why. For example
Key = "123" fails
Key = CSTR(123) fails
Key = 123 & "SomeText" fails
but I think
CSTR("SomeText" & 123) will work

so try
Key = CSTR(strNode2Text & i)

This may be the issue, but the "element not found" could be coming from here

.Nodes.Add relative:=strNode1Text

If there is not a node with a key returned by the variable "strNode1Text" you will get that error. However, your code looks correct in that regards.

 

[bfamo]

Hey there!

I still keep getting the "Key is not unique in collection" with the following line highlighted:
.Nodes.Add relative:=strNode1Text, relationship:=tvwChild, Key:=CStr(strNode2Text & i), Text:=strVisibleText

Here is what I have done:

Dim i As Integer

i=i+1

'Fill level2

.Nodes.Add relative:=strNode1Text, relationship:=tvwChild, Key:=CStr(strNode2Text & i), Text:=strVisibleText

'Fill level3
.Nodes.Add relative:=strNode1Text, relationship:=tvwChild, Key:=CStr(strNode3Text & i), Text:=strVisibleText

You would think, or at least I do, that when including "& i" the keys are given +1 to the name of the node.
Example: (with all child nodes having identical names)

|-strNode1Text
|-strNode2Text1
|-strNode2Text2
|-strNode3Text1
|-strNode3Text2
|-strNode2Text3

I also tried key:= CSTR("A" & strNode3Text(rnd()*10000000)), and when I "debug.print", it says "Expected: array"

However, now it at least feels like I'm getting closer

thanks!

 

[MajP]

Is this all you did?
Dim i As Integer
i=i+1

Then i will always = 1. This does not increment

Do Until rst.EOF
         strNode2Text = StrConv("Level2" & rst![Navn], vbLowerCase)
         strNode3Text = StrConv("Level3" & rst![Dato], vbLowerCase) & [b]i[/b]
         strVisibleText = rst![Dato]
           .Nodes.Add relative:=strNode2Text, relationship:=tvwChild, Key:=strNode3Text, Text:=strVisibleText
          rst.MoveNext
          [b] i = i + 1 [/b]
      Loop
      rst.Close
[/close]

 

[bfamo]

Now its starting to look like something here...

I added "&i" and "i=i+1" as you showed me on both level2 and level3.
I got the message "Element not found" with the following line highlighted

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

, and this shows that it most likely is the ".Nodes.Add relative:=strNode2Text" as you mentioned earlier...

What I did to test this was to only have identical names on the third level. This works just fine, becaus there is no fourth level that needs a relationship to the third, like the third hat do the second...

 

[bfamo]

Now its starting to look like something here...

I added "&i" and "i=i+1" as you showed me on both level2 and level3.
I got the message "Element not found" with the following line highlighted

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

, and this shows that it most likely is the ".Nodes.Add relative:=strNode2Text" as you mentioned earlier...

What I did to test this was to only have identical names on the third level. This works just fine, becaus there is no fourth level that needs a relationship to the third, like the third has to the second...

 

[bfamo]

Now its starting to look like something here...

I added "&i" and "i=i+1" as you showed me on both level2 and level3.
I got the message "Element not found" with the following line highlighted

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

, and this shows that it most likely is the ".Nodes.Add relative:=strNode2Text" as you mentioned earlier...

What I did to test this was to only have identical names on the third level. This works just fine, becaus there is no fourth level that needs a relationship to the third, like the third has do the second...

 

[MajP]

Here is your problem. If you make a node at level 2 with keys like
"mike1"
"mike2"
you are going to have to add to nodes called
"mike1" and "mike2"
The way you are doing your algorithm makes this hard.

This is how I would do this.
Get the first node
get its children
get its children

Function TreeTilsynshistorikk_Fill()

   Function TreeTilsynshistorikk_Fill()

   Dim strMessage As String
   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset
   Dim rst2 As DAO.Recordset
   Dim rst3 As DAO.Recordset
   Dim intVBMsg As Integer
   Dim nod As Object
   Dim strNode1Text As String
   Dim strNode2Text As String
   Dim strNode3Text As String
   Dim strVisibleText As String
   Dim strSqlLevel2 As String
   Dim strSqlLevel3 As String
   Set dbs = CurrentDb()
   Dim i As Integer
   Dim j As Integer
   Dim k As Integer
   
With Me![TreeTilsynshistorikk]
      'Fill Level 1
      Set rst = dbs.OpenRecordset(strQuery1, dbOpenForwardOnly)

      Do Until rst.EOF
        strNode1Text = StrConv("Level1" & rst![Initialer], vbLowerCase) & i
        Set nod = .Nodes.Add(Key:=strNode1Text, Text:=rst![Initialer])
        strSqlLevel2 = "select * from QryTilsInsProdTree where [Initialer] = '" & rst![Initialer] & "'"
        Set rst2 = dbs.OpenRecordset(strSqlLevel2, dbOpenForwardOnly)
        i = i + 1
        Do Until rst2.EOF
          strNode2Text = StrConv("Level2" & rst![Navn], vbLowerCase) & j
          strVisibleText = rst![Navn]
          .Nodes.Add relative:=strNode1Text, relationship:=tvwChild, Key:=strNode2Text, Text:=strVisibleText
          strSqlLevel3 = "select * from QryTilsInsProdDatoTree where [Navn] = '" & rst2![Navn] & "'"
          j = j + 1
          Set rst3 = dbs.OpenRecordset(strSqlLevel3, dbOpenForwardOnly)
          Do Until rst3.EOF
            strNode3Text = StrConv("Level3" & rst![Dato], vbLowerCase) & k
            strVisibleText = rst![Dato]
            .Nodes.Add relative:=strNode2Text, relationship:=tvwChild, Key:=strNode3Text, Text:=strVisibleText
            rst3.MoveNext
            k = k + 1
          Loop
          rst3.Close
          rst2.MoveNext
      Loop
       rst2.Close
       rst.MoveNext
     Loop
      rst.Close
 End With
 dbs.Close


End Function

 

[bfamo]

First off, thank you so much for taking the time to show me this! You are a lifesaver. My boss will be very pleased!

I put the code in, and also had to add

   Dim strQuery1 As String
   Dim strQuery2 As String
   Dim strQuery3 As String

   strQuery1 = "QryTilsInsTree"
   strQuery2 = "QryTilsInsProdTree"
   strQuery3 = "QryTilsInsProdDatoTree"

When I run the code I stille get the message "Can not find element in collection". It highlights the line

With Me![TreeTilsynshistorikk]
'Fill Level 1
Set rst = dbs.OpenRecordset(strQuery1, dbOpenForwardOnly)

Do Until rst.EOF
strNode1Text = StrConv("Level1" & rst![Initialer], vbLowerCase) & i
Set nod = .Nodes.Add(Key:=strNode1Text, Text:=rst![Initialer])
strSqlLevel2 = "select * from QryTilsInsProdTree where [Initialer] = '" & rst![Initialer] & "'"
Set rst2 = dbs.OpenRecordset(strSqlLevel2, dbOpenForwardOnly)
i = i + 1
Do Until rst2.EOF
strNode2Text = StrConv("Level2" & rst![Navn], vbLowerCase) & j
strVisibleText = rst![Navn]
.Nodes.Add relative:=strNode1Text, relationship:=tvwChild, Key:=strNode2Text, Text:=strVisibleText
strSqlLevel3 = "select * from QryTilsInsProdDatoTree where [Navn] = '" & rst2![Navn] & "'"
j = j + 1

Let me show you a picture to show you my idea of how this will look in the end, in the middle of all this code talk:

In other words: "GRAVDAL" can appear both under "asber" and "erble".

 

[MajP]

My fault should read
strNode2Text = StrConv("Level2" & rst2![Navn], vbLowerCase) & j
and
strNode3Text = StrConv("Level3" & rst3![Dato], vbLowerCase)

 

[MajP]

also for extra precaution try this

strNode2Text = CSTR(StrConv("Level2" & rst2![Navn], vbLowerCase) & j)
and
strNode3Text = CSTR(StrConv("Level3" & rst3![Dato], vbLowerCase))

 

[bfamo]

yeah, now its starting to work.
This is how it looks now:

What happens now is that once you register "GRAVDAL" under "erble", it automatically also shows the same record under "asber". These two should be separate. You should be able to have the record that has been made by "asber" under "asber" and the records made my "erble" under "erble".

Also, there is no need for "GRAVDAL" to occur twice under "asber". When a new "GRAVDAL"-record is added under "asber" (which already has one stored), it is stored under the date. This way you get a tree that look like this:

Thanks

 

[MajP]

Oops another mistake. The text is not the correct visible text.

strVisibleText = rst2![Navn]
strVisibleText = rst3![Dato]

 

[bfamo]

Yeah I saw that mistake MajP and corrected it before my previous post

I'm trying to figure out why it acts the way it does, it feels like we are so close to the final solution here!
Still, I cannot see anythinh. I'm still where I was after the last post. hmmm...

Here is the code and the changes I've made to it just so that we are on the same page:

Function TreeTilsynshistorikk_Fill()

Dim strMessage As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim rst3 As DAO.Recordset
Dim intVBMsg As Integer
Dim nod As Object
Dim strNode1Text As String
Dim strNode2Text As String
Dim strNode3Text As String
Dim strVisibleText As String
Dim strSqlLevel2 As String
Dim strSqlLevel3 As String
Set dbs = CurrentDb()
Dim strQuery1 As String
Dim strQuery2 As String
Dim strQuery3 As String
Dim i As Integer
Dim j As Integer
Dim k As Integer

strQuery1 = "QryTilsInsTree"
strQuery2 = "QryTilsInsProdTree"
strQuery3 = "QryTilsInsProdDatoTree"

With Me![TreeTilsynshistorikk]
'Fill Level 1
Set rst = dbs.OpenRecordset(strQuery1, dbOpenForwardOnly)

Do Until rst.EOF
strNode1Text = StrConv("Level1" & rst![Initialer], vbLowerCase) & i
Set nod = .Nodes.Add(Key:=strNode1Text, Text:=rst![Initialer])
strSqlLevel2 = "select * from QryTilsInsProdTree where [Initialer] = '" & rst![Initialer] & "'"
Set rst2 = dbs.OpenRecordset(strSqlLevel2, dbOpenForwardOnly)
i = i + 1
Do Until rst2.EOF
strNode2Text = StrConv("Level2" & rst2![Navn], vbLowerCase) & j
strVisibleText = rst2![Navn]
.Nodes.Add relative:=strNode1Text, relationship:=tvwChild, Key:=strNode2Text, Text:=strVisibleText
strSqlLevel3 = "select * from QryTilsInsProdDatoTree where [Navn] = '" & rst2![Navn] & "'"
j = j + 1
Set rst3 = dbs.OpenRecordset(strSqlLevel3, dbOpenForwardOnly)
Do Until rst3.EOF
strNode3Text = StrConv("Level3" & rst3![Dato], vbLowerCase) & k
strVisibleText = rst3![Dato]
.Nodes.Add relative:=strNode2Text, relationship:=tvwChild, Key:=strNode3Text, Text:=strVisibleText
rst3.MoveNext
k = k + 1
Loop
rst3.Close
rst2.MoveNext
Loop
rst2.Close
rst.MoveNext
Loop
rst.Close
End With
dbs.Close


End Function

 

[PHV]

you may try this:
strSqlLevel2 = "SELECT DISTINCT Navn FROM QryTilsInsProdTree WHERE Initialer='" & rst![Initialer] & "'"
and this:
strSqlLevel3 = "SELECT DISTINCT [Dato] FROM QryTilsInsProdDatoTree WHERE Navn='" & rst2![Navn] & "' AND Initialer='" & rst![Initialer] & "'"

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

 

[MajP]

It is hard to tell if my looping is incorrect or if the queries are not returning what I think they are, but this is somewhat hard without seeing the data structure. I am kind of thinking the same thing that PHV is thinking.

If I return query 2 where initialer = asber what records does that return. Should only return 1 record with Gradval Odvar.

 

[bfamo]

YES!
Only thing remaining now is:

Lets say that "erble" has 4 records stored under "GRAVDAL" like this:
|-Erble
|-GRAVDAL ODDVAR
|01.01.2001
|21.02.2005
|13.08.2006
|26.09.2006

If you now add another under "GRAVDAL" that has the date 01.01.2001 (which is the same as a record that has already been stored) you can only see the first record and not the second.

The same also happens if you store a record with the same date under a differend "parent", like this:

|-asber
|-IVESDAL
|01.01.2001 -> Shows the same as the record stored under "erble"
|23.01.2003
|11.07.2005
|26.03.2006

 

[bfamo]

strQuery1 = "QryTilsInsTree" Returns: [Initialer]
strQuery2 = "QryTilsInsProdTree" Returns: [Initialer] and [Navn]
strQuery3 = "QryTilsInsProdDatoTree" Returns: [Initialer], [Navn] and [Dato]

 

[PHV]

Perhaps this ?
strSqlLevel3 = "SELECT DISTINCT * FROM QryTilsInsProdDatoTree WHERE Navn='" & rst2![Navn] & "' AND Initialer='" & rst![Initialer] & "'"

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