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

Function tvwBooks_Fill()
'Created by Helen Feddema 2-10-2002
'Last modified 4-23-2002
 
'============================================================
'Modified from a procedure generated by the Access 97
'Treeview Control Wizard
 
'PURPOSE: Fill the ActiveX Treeview Control 'tvwBooks' with
'author and book information
'ACCEPTS: Nothing
'RETURNS: Nothing
'CALLED FROM: Form Load event
'============================================================
 
On Error GoTo ErrorHandler
 
   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"
  
   With Me![tvwBooks]
      'Fill Level 1
      Set rst = dbs.OpenRecordset(strQuery1, dbOpenForwardOnly)
 
      'Add a node object for each record in the "qryEBookAuthors" table/query.
      'The Key argument concatenates the level number and the LastNameFirst
      'field of the Level 1 query, to create a unique key value for the node.
      'The Text argument is the text displayed as a Level 1 node in the
      'TreeView control
 
      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
     
      'Fill Level 2
      Set rst = dbs.OpenRecordset(strQuery2, dbOpenForwardOnly)
 
      'Add a node object for each record in the "qryEBooksByAuthor"
      'table/query.
      'The value of the Relative argument matches the Key argument value
      'for the Level 1 node this Level 2 node belongs to.
      'The Relationship argument takes a named constant, tvwChild,
      'indicating that the Level 2 node becomes a child node of the
      'Level 1 node named in the Relative argument.
      'The Key argument concatenates the level number and the Title
      'field of the Level 2 query, to create a unique key value for the node.
      'The Text argument is the text displayed as a Level 2 node in the
      'TreeView control
 
      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
     
   End With
   dbs.Close
 
ErrorHandlerExit:
   Exit Function
 
ErrorHandler:
   Select Case Err.Number
      Case 35601
         'Element not found
         strMessage = "Possible Causes: You selected a table/query" _
            & " for a child level which does not correspond to a value" _
            & " from its parent level."
         intVBMsg = MsgBox(Error$ & strMessage, vbOKOnly + _
            vbExclamation, "Run-time Error: " & Err.Number)
      Case 35602
         'Key is not unique in collection
         strMessage = "Possible Causes: You selected a non-unique" _
            & " field to link levels."
         intVBMsg = MsgBox(Error$ & strMessage, vbOKOnly + _
            vbExclamation, "Run-time Error: " & Err.Number)
      Case Else
         intVBMsg = MsgBox(Error$ & "@@", vbOKOnly + _
            vbExclamation, "Run-time Error: " & Err.Number)
   End Select
   Resume ErrorHandlerExit
 
End Function

[Dal Jeanis]

1) Please post the SQL for each of these queries

strQuery1 = "qryEBookAuthors"
strQuery2 = "qryEBooksByAuthor"

2) Please post the structure of your transmittal records.

3) You will design a unique key for your transmittal records

4) You will create a third query that gives you the information to create your keys
strQuery3 = "qryTransmittalsbyEBooks"

5) You will duplicate the section of code from 'Fill Level 2 to RST.close, and adapt it for the new level.

When you post the SQL, we can give you a guess what your new SQL needs to look like, and what changes need to be done to the 'Fill Level Three code.

[Dal Jeanis]

Here's the code for the load. Now we just need to build the "qryTransmittalsbyEBook" query.

'Fill Level 3
      Set rst = dbs.OpenRecordset(strQuery3, dbOpenForwardOnly)
 
      'Add a node object for each record in the "qryTransmittalsByEBook"
      'table/query.
      'The value of the Relative argument matches the Key argument value
      'for the Level 1 node this Level 2 node belongs to.
      'The Relationship argument takes a named constant, tvwChild,
      'indicating that the Level 2 node becomes a child node of the
      'Level 1 node named in the Relative argument.
      'The Key argument concatenates the level number and the Title
      'field of the Level 2 query, to create a unique key value for the node.
      'The Text argument is the text displayed as a Level 2 node in the
      'TreeView control
 
      Do Until rst.EOF
         strNode1Text = StrConv("Level2" & rst![Title], vbLowerCase)
         strNode2Text = StrConv("Level3" & rst![Transmittal], vbLowerCase)
         strVisibleText = rst![Transmittal]
         .Nodes.Add relative:=strNode1Text, _
            relationship:=tvwChild, _
            Key:=strNode3Text, _
            Text:=strVisibleText
         rst.MoveNext
      Loop
      rst.Close

[Dal Jeanis]

1) If your transmittal ID is stored in text form "tt-1000" in a separate field, then the SQL would look something like this:

table layouts:
tblBooks (BookID (pk),title)
tblTransmittals (TransID (PK), BookID (FK to tblBooks), Transmittal)

SELECT TB.[title] AS Title, TT.[Transmittal] AS Transmittal
FROM tblBooks AS TB INNER JOIN tblTransmittals AS TT
ON TB.[BookID] = TT.[BookID];

2) If you have a global function (say, MakeDisplayTransmittal) to format the actual key into the display format, then it might look like this

table layouts:
tblBooks (BookID (pk),title)
tblTransmittals (TransID (PK), BookID (FK to tblBooks))

SELECT TB.[title] AS Title, MakeDisplayTransmittal(TT.[TransID]) AS Transmittal
FROM tblBooks AS TB INNER JOIN tblTransmittals AS TT
ON TB.[BookID] = TT.[BookID];

3) If for some reason you have a separate relation table relating Book to Transmittal, then it might look like this:

table layouts:
tblBooks (BookID (pk),title)
tblTransmittals (TransID (PK), Transmittal)
tblBookTrans (BookID (FK to tblBooks), TransID (FK to tblTransmittals))

SELECT TB.[title] AS Title, TT.[Transmittal] AS Transmittal
FROM tblBooks AS TB INNER JOIN 
    (tblTransmittals AS TT INNER JOIN tblBookTrans AS TX
     ON TT.TransID = TX.TransID)
ON TB.[BookID] = TX.[BookID];

[masoud_sedighy]

thanks,
my question is what happens for AuthorId , it seems level3 just depend on bookid, i need also it depends on Authorid, for example we have

Authorid Bookid transmittal
1 2 tt-1000
2 2 tt-5000

so in the treeview for AuthorID=1 we have Bookid=2 and two transmittal tt-1000 and tt-5000 while it should show just tt-1000

[Dal Jeanis]

1) Can a BookID have two different authors? I thought it was a key?

2) Please post the structure of your transmittal table. Be sure to explain whether the key is actually a text "TT-5000", whether the actual key is an autonumber and TT-5000 is stored in another field, or if there's a function (or method) for making TT-5000 out of the stored key field.

3) Please post the SQL from those two queries. "qryEBookAuthors" and "qryEBooksByAuthor"

[masoud_sedighy]

Yes I assume a Bookid can have have two different authors.

Also I assume a Transid can have two different books. And design of my tables is like below:

tblTransmittal (TransId (pk),TransmittalNo)

tblbook (Bookid (pk),title, Category, File, Word Length,…)

tblAuthor(AuthorID(pk), AuthorPrefix, AuthorFirstName, AuthorMiddleName, AuthorLastName, AuthorSuffix)

tblBookAuthors (Bookid(pk), AuthorID(pk))

tblTransmittal_Book_Author (Transid(pk), Bookid(pk), AuthorID(pk))

qryEBookAuthors:

SELECT DISTINCT tblAuthors.AuthorID, Trim(IIf([AuthorFirstName],[AuthorLastName] & IIf([AuthorPrefix],[AuthorPrefix] & " ","") & IIf([AuthorFirstName],", " & [AuthorFirstName],"") & IIf([AuthorMiddleName]," " & [AuthorMiddleName]),[AuthorLastName])) & IIf([AuthorSuffix]," " & [AuthorSuffix],"") AS LastNameFirst

FROM tblAuthors

ORDER BY Trim(IIf([AuthorFirstName],[AuthorLastName] & IIf([AuthorPrefix],[AuthorPrefix] & " ","") & IIf([AuthorFirstName],", " & [AuthorFirstName],"") & IIf([AuthorMiddleName]," " & [AuthorMiddleName]),[AuthorLastName])) & IIf([AuthorSuffix]," " & [AuthorSuffix],"");

qryEBooksByAuthor :

SELECT tblBookAuthors.AuthorID, tblBookAuthors.BookID, qryBookAuthors.LastNameFirst, tblBooks.Title, tblBooks.Type, Switch([ReadStatus]=3," " & Chr$(215),[ReadStatus]=2," " & Chr$(247)) AS BeenRead

FROM tblBooks INNER JOIN (tblBookAuthors INNER JOIN qryBookAuthors ON tblBookAuthors.AuthorID = qryBookAuthors.AuthorID) ON tblBooks.BookID = tblBookAuthors.BookID

ORDER BY qryBookAuthors.LastNameFirst, tblBooks.Title;

[Dal Jeanis]

Okay, then if you have two different books with the same title, your existing method will bomb, because two different nodes will try to register themselves with the same Key -
strNode2Text = StrConv("Level2" & rst![Title], vbLowerCase).

It looks to me like you'll need to have a strNode%Key and a strNode%Text (where % = 1 and 2) in order to make the nodes unique. You probably should do the same at every level, and concatenate the actual Key into the strNode%Key fields.

I'll take a look at lunch and see what I can do.

[masoud_sedighy]

i found the article with this address (http://www.databasejournal.com/featu...and-Filter.htm)

that open record set and filter record set by the parent nodes and then add child node,i think it will solve my problem.

Best Regards.

[Dal Jeanis]

Great! Keep us posted, and if that works, please park the thread solved. Top of page, under thread tools.

[masoud_sedighy]

rpeare has solved the my problem in this thread https://www.accessforums.net/program...ard-35788.html

thanks

[Dal Jeanis]

Yay! Be sure to give him a reputation point! (Click the little sherriff's star under his post.)