How to fill level 3 of treeview

[masoud_sedighy]

Hello

I have used below code for filling tree view with 2 levels. This tree view shows books for each Author, now I like to add level 3 (transmittal no) for each book for example author1 has 2 books (book1, book2) and then for book1 we have 4 transmittal no (tt -003, tt-1000,tt-4000,tt-5000) and for book2 we have 1 transmittal no (tt-0009)
My tables for 2 levels are:

tblAuthors (AuthorID (pk), AuthorFirstName, AuthorLastName)

tblBooks (BookID (pk),title)

tblBookAuthor (AuthorID, BookID)

Please help, now for 3 levels, what table I have to add and what changes I have to do in below code

[FONT=&quot]1. [/FONT][FONT=&quot]Function tvwBooks_Fill()[/FONT]​

[FONT=&quot]2. [/FONT][FONT=&quot]'Created by Helen Feddema 2-10-2002[/FONT]​

[FONT=&quot]3. [/FONT][FONT=&quot]'Last modified 4-23-2002[/FONT]​

[FONT=&quot]4. [/FONT][FONT=&quot] [/FONT]​

[FONT=&quot]5. [/FONT][FONT=&quot]'============================================================[/FONT]​

[FONT=&quot]6. [/FONT][FONT=&quot]'Modified from a procedure generated by the Access 97[/FONT]​

[FONT=&quot]7. [/FONT][FONT=&quot]'Treeview Control Wizard[/FONT]​

[FONT=&quot]8. [/FONT][FONT=&quot] [/FONT]​

[FONT=&quot]9. [/FONT][FONT=&quot]'PURPOSE: Fill the ActiveX Treeview Control 'tvwBooks' with[/FONT]​

[FONT=&quot]10.[/FONT][FONT=&quot]'author and book information[/FONT]​

[FONT=&quot]11.[/FONT][FONT=&quot]'ACCEPTS: Nothing[/FONT]​

[FONT=&quot]12.[/FONT][FONT=&quot]'RETURNS: Nothing[/FONT]​

[FONT=&quot]13.[/FONT][FONT=&quot]'CALLED FROM: Form Load event[/FONT]​

[FONT=&quot]14.[/FONT][FONT=&quot]'============================================================[/FONT]​

[FONT=&quot]15.[/FONT][FONT=&quot] [/FONT]​

[FONT=&quot]16.[/FONT][FONT=&quot]On Error GoTo ErrorHandler[/FONT]​

[FONT=&quot]17.[/FONT][FONT=&quot] [/FONT]​

[FONT=&quot]18.[/FONT][FONT=&quot] Dim strMessage As String[/FONT]​

[FONT=&quot]19.[/FONT][FONT=&quot] Dim dbs As DAO.Database[/FONT]​

[FONT=&quot]20.[/FONT][FONT=&quot] Dim rst As DAO.Recordset[/FONT]​

[FONT=&quot]21.[/FONT][FONT=&quot] Dim intVBMsg As Integer[/FONT]​

[FONT=&quot]22.[/FONT][FONT=&quot] Dim strQuery1 As String[/FONT]​

[FONT=&quot]23.[/FONT][FONT=&quot] Dim strQuery2 As String[/FONT]​

[FONT=&quot]24.[/FONT][FONT=&quot] Dim nod As Object[/FONT]​

[FONT=&quot]25.[/FONT][FONT=&quot] Dim strNode1Text As String[/FONT]​

[FONT=&quot]26.[/FONT][FONT=&quot] Dim strNode2Text As String[/FONT]​

[FONT=&quot]27.[/FONT][FONT=&quot] Dim strVisibleText As String[/FONT]​

[FONT=&quot]28.[/FONT][FONT=&quot] [/FONT]​

[FONT=&quot]29.[/FONT][FONT=&quot] Set dbs = CurrentDb()[/FONT]​

[FONT=&quot]30.[/FONT][FONT=&quot] strQuery1 = "qryEBookAuthors"[/FONT]​

[FONT=&quot]31.[/FONT][FONT=&quot] strQuery2 = "qryEBooksByAuthor"[/FONT]​

[FONT=&quot]32.[/FONT][FONT=&quot] [/FONT]​

[FONT=&quot]33.[/FONT][FONT=&quot] With Me![tvwBooks][/FONT]​

[FONT=&quot]34.[/FONT][FONT=&quot] 'Fill Level 1[/FONT]​

[FONT=&quot]35.[/FONT][FONT=&quot] Set rst = dbs.OpenRecordset(strQuery1, dbOpenForwardOnly)[/FONT]​

[FONT=&quot]36.[/FONT][FONT=&quot] [/FONT]​

[FONT=&quot]37.[/FONT][FONT=&quot] 'Add a node object for each record in the "qryEBookAuthors" table/query.[/FONT]​

[FONT=&quot]38.[/FONT][FONT=&quot] 'The Key argument concatenates the level number and the LastNameFirst[/FONT]​

[FONT=&quot]39.[/FONT][FONT=&quot] 'field of the Level 1 query, to create a unique key value for the node.[/FONT]​

[FONT=&quot]40.[/FONT][FONT=&quot] 'The Text argument is the text displayed as a Level 1 node in the[/FONT]​

[FONT=&quot]41.[/FONT][FONT=&quot] 'TreeView control[/FONT]​

[FONT=&quot]42.[/FONT][FONT=&quot] [/FONT]​

[FONT=&quot]43.[/FONT][FONT=&quot] Do Until rst.EOF[/FONT]​

[FONT=&quot]44.[/FONT][FONT=&quot] strNode1Text = StrConv("Level1" & rst![LastNameFirst], _[/FONT]​

[FONT=&quot]45.[/FONT][FONT=&quot] vbLowerCase)[/FONT]​

[FONT=&quot]46.[/FONT][FONT=&quot] Set nod = .Nodes.Add(Key:=strNode1Text, _[/FONT]​

[FONT=&quot]47.[/FONT][FONT=&quot] Text:=rst![LastNameFirst])[/FONT]​

[FONT=&quot]48.[/FONT][FONT=&quot] 'Expand the entire node[/FONT]​

[FONT=&quot]49.[/FONT][FONT=&quot] nod.Expanded = True[/FONT]​

[FONT=&quot]50.[/FONT][FONT=&quot] rst.MoveNext[/FONT]​

[FONT=&quot]51.[/FONT][FONT=&quot] Loop[/FONT]​

[FONT=&quot]52.[/FONT][FONT=&quot] rst.Close[/FONT]​

[FONT=&quot]53.[/FONT][FONT=&quot] [/FONT]​

[FONT=&quot]54.[/FONT][FONT=&quot] 'Fill Level 2[/FONT]​

[FONT=&quot]55.[/FONT][FONT=&quot] Set rst = dbs.OpenRecordset(strQuery2, dbOpenForwardOnly)[/FONT]​

[FONT=&quot]56.[/FONT][FONT=&quot] [/FONT]​

[FONT=&quot]57.[/FONT][FONT=&quot] 'Add a node object for each record in the "qryEBooksByAuthor"[/FONT]​

[FONT=&quot]58.[/FONT][FONT=&quot] 'table/query.[/FONT]​

[FONT=&quot]59.[/FONT][FONT=&quot] 'The value of the Relative argument matches the Key argument value[/FONT]​

[FONT=&quot]60.[/FONT][FONT=&quot] 'for the Level 1 node this Level 2 node belongs to.[/FONT]​

[FONT=&quot]61.[/FONT][FONT=&quot] 'The Relationship argument takes a named constant, tvwChild,[/FONT]​

[FONT=&quot]62.[/FONT][FONT=&quot] 'indicating that the Level 2 node becomes a child node of the[/FONT]​

[FONT=&quot]63.[/FONT][FONT=&quot] 'Level 1 node named in the Relative argument.[/FONT]​

[FONT=&quot]64.[/FONT][FONT=&quot] 'The Key argument concatenates the level number and the Title[/FONT]​

[FONT=&quot]65.[/FONT][FONT=&quot] 'field of the Level 2 query, to create a unique key value for the node.[/FONT]​

[FONT=&quot]66.[/FONT][FONT=&quot] 'The Text argument is the text displayed as a Level 2 node in the[/FONT]​

[FONT=&quot]67.[/FONT][FONT=&quot] 'TreeView control[/FONT]​

[FONT=&quot]68.[/FONT][FONT=&quot] [/FONT]​

[FONT=&quot]69.[/FONT][FONT=&quot] Do Until rst.EOF[/FONT]​

[FONT=&quot]70.[/FONT][FONT=&quot] strNode1Text = StrConv("Level1" & rst![LastNameFirst], vbLowerCase)[/FONT]​

[FONT=&quot]71.[/FONT][FONT=&quot] strNode2Text = StrConv("Level2" & rst![Title], vbLowerCase)[/FONT]​

[FONT=&quot]72.[/FONT][FONT=&quot] strVisibleText = rst![Title][/FONT]​

[FONT=&quot]73.[/FONT][FONT=&quot] .Nodes.Add relative:=strNode1Text, _[/FONT]​

[FONT=&quot]74.[/FONT][FONT=&quot] relationship:=tvwChild, _[/FONT]​

[FONT=&quot]75.[/FONT][FONT=&quot] Key:=strNode2Text, _[/FONT]​

[FONT=&quot]76.[/FONT][FONT=&quot] Text:=strVisibleText[/FONT]​

[FONT=&quot]77.[/FONT][FONT=&quot] rst.MoveNext[/FONT]​

[FONT=&quot]78.[/FONT][FONT=&quot] Loop[/FONT]​

[FONT=&quot]79.[/FONT][FONT=&quot] rst.Close[/FONT]​

[FONT=&quot]80.[/FONT][FONT=&quot] [/FONT]​

[FONT=&quot]81.[/FONT][FONT=&quot] End With[/FONT]​

[FONT=&quot]82.[/FONT][FONT=&quot] dbs.Close[/FONT]​

[FONT=&quot]83.[/FONT][FONT=&quot] [/FONT]​

[FONT=&quot]84.[/FONT][FONT=&quot]ErrorHandlerExit:[/FONT]​

[FONT=&quot]85.[/FONT][FONT=&quot] Exit Function[/FONT]​

[FONT=&quot]86.[/FONT][FONT=&quot] [/FONT]​

[FONT=&quot]87.[/FONT][FONT=&quot]ErrorHandler:[/FONT]​

[FONT=&quot]88.[/FONT][FONT=&quot] Select Case Err.Number[/FONT]​

[FONT=&quot]89.[/FONT][FONT=&quot] Case 35601[/FONT]​

[FONT=&quot]90.[/FONT][FONT=&quot] 'Element not found[/FONT]​

[FONT=&quot]91.[/FONT][FONT=&quot] strMessage = "Possible Causes: You selected a table/query" _[/FONT]​

[FONT=&quot]92.[/FONT][FONT=&quot] & " for a child level which does not correspond to a value" _[/FONT]​

[FONT=&quot]93.[/FONT][FONT=&quot] & " from its parent level."[/FONT]​

[FONT=&quot]94.[/FONT][FONT=&quot] intVBMsg = MsgBox(Error$ & strMessage, vbOKOnly + _[/FONT]​

[FONT=&quot]95.[/FONT][FONT=&quot] vbExclamation, "Run-time Error: " & Err.Number)[/FONT]​

[FONT=&quot]96.[/FONT][FONT=&quot] Case 35602[/FONT]​

[FONT=&quot]97.[/FONT][FONT=&quot] 'Key is not unique in collection[/FONT]​

[FONT=&quot]98.[/FONT][FONT=&quot] strMessage = "Possible Causes: You selected a non-unique" _[/FONT]​

[FONT=&quot]99.[/FONT][FONT=&quot] & " field to link levels."[/FONT]​

[FONT=&quot]100. [/FONT][FONT=&quot] intVBMsg = MsgBox(Error$ & strMessage, vbOKOnly + _[/FONT]​

[FONT=&quot]101. [/FONT][FONT=&quot] vbExclamation, "Run-time Error: " & Err.Number)[/FONT]​

[FONT=&quot]102. [/FONT][FONT=&quot] Case Else[/FONT]​

[FONT=&quot]103. [/FONT][FONT=&quot] intVBMsg = MsgBox(Error$ & "@@", vbOKOnly + _[/FONT]​

[FONT=&quot]104. [/FONT][FONT=&quot] vbExclamation, "Run-time Error: " & Err.Number)[/FONT]​

[FONT=&quot]105. [/FONT][FONT=&quot] End Select[/FONT]​

[FONT=&quot]106. [/FONT][FONT=&quot] Resume ErrorHandlerExit[/FONT]​

[FONT=&quot]107. [/FONT][FONT=&quot] [/FONT]​

[FONT=&quot]108. [/FONT][FONT=&quot]End Function[/FONT]​

[FONT=&quot] [/FONT]​

​

 

[Isskint]

Repeat the loop you have, to assign the first child node, setting the parent as the first level child node and add a child to that.

I use a main loop to allocate level 1 nodes, then a while/wend and for/next loop to step through each data point step by step. Essentially i exhaust each branch before adding the next.

 

[masoud_sedighy]

thanks
it it possible for more clarification send code or a sample

 

[Isskint]

certainly

This is the document that cleared things up for me.

 

[masoud_sedighy]

i found the article with this address (http://www.databasejournal.com/feat...Tos-Adding-a-Tree-View-Control-and-Filter.htm)

that open record set and filter record set by the parent nodes and then add child node according to your pdf file.

i think it will solve my problem.

thanks