Treeview control wizard

[masoud_sedighy]

Before I have a question about "how to fill level 3 of treeview by query" but I did not get answer, after searching a lot in Google I read that there is a control wizard that works with Access97.​

I like to know how I can use this wizard with Access 2007. That is possible?​

What I have found for 2 levels is like below code for Function tvwBooks_Fill()​

Design of tables and query is like below and I like to change below code for the treeview that​

First level is: Author​

Second level is: book​

Third level is: transmittal no​

------------------------------------------------------------------------------------​

tblTransmittal (TransId (pk),TransmittalNo)​

tblbook (Bookid (pk),title)​

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

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

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

​

[FONT=&quot]qryEBookAuthors[/FONT]:​

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],"");​

​

​

[FONT=&quot]qryEBooksByAuthor[/FONT] :​

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;​

​

​

  [LEFT]Function tvwBooks_Fill()[/LEFT]
  [LEFT]'Created by Helen Feddema 2-10-2002[/LEFT]
  [LEFT]'Last modified 4-23-2002[/LEFT]
  [LEFT][FONT=&quot] [/FONT][/LEFT]
  [LEFT]'============================================================[FONT=&quot][/FONT][/LEFT]
  [LEFT]'Modified from a procedure generated by the Access 97[/LEFT]
  [LEFT]'Treeview Control Wizard[/LEFT]
  [LEFT][FONT=&quot] [/FONT][/LEFT]
  [LEFT]'PURPOSE: Fill the ActiveX Treeview Control 'tvwBooks' with[/LEFT]
  [LEFT]'author and book information[/LEFT]
  [LEFT]'ACCEPTS: Nothing[/LEFT]
  [LEFT]'RETURNS: Nothing[/LEFT]
  [LEFT]'CALLED FROM: Form Load event[/LEFT]
  [LEFT]'============================================================[FONT=&quot][/FONT][/LEFT]
  [LEFT][FONT=&quot] [/FONT][/LEFT]
  [LEFT]On Error GoTo ErrorHandler[/LEFT]
  [LEFT][FONT=&quot] [/FONT][/LEFT]
  [LEFT]   Dim strMessage As String[/LEFT]
  [LEFT]   Dim dbs As DAO.Database[/LEFT]
  [LEFT]   Dim rst As DAO.Recordset[/LEFT]
  [LEFT]   Dim intVBMsg As Integer[/LEFT]
  [LEFT]   Dim strQuery1 As String[/LEFT]
  [LEFT]   Dim strQuery2 As String[/LEFT]
  [LEFT]   Dim nod As Object[/LEFT]
  [LEFT]   Dim strNode1Text As String[/LEFT]
  [LEFT]   Dim strNode2Text As String[/LEFT]
  [LEFT]   Dim strVisibleText As String[/LEFT]
  [LEFT]   [FONT=&quot][/FONT][/LEFT]
  [LEFT]   Set dbs = CurrentDb()[/LEFT]
  [LEFT]   strQuery1 = "qryEBookAuthors"[/LEFT]
  [LEFT]   strQuery2 = "qryEBooksByAuthor"[/LEFT]
  [LEFT]   [FONT=&quot][/FONT][/LEFT]
  [LEFT]   With Me![tvwBooks][/LEFT]
  [LEFT]      'Fill Level 1[/LEFT]
  [LEFT]      Set rst = dbs.OpenRecordset(strQuery1, dbOpenForwardOnly)[/LEFT]
  [LEFT][FONT=&quot] [/FONT][/LEFT]
  [LEFT]      'Add a node object for each record in the "qryEBookAuthors" table/query.[/LEFT]
  [LEFT]      'The Key argument concatenates the level number and the LastNameFirst[/LEFT]
  [LEFT]      'field of the Level 1 query, to create a unique key value for the node.[/LEFT]
  [LEFT]      'The Text argument is the text displayed as a Level 1 node in the[/LEFT]
  [LEFT]      'TreeView control[/LEFT]
  [LEFT][FONT=&quot] [/FONT][/LEFT]
  [LEFT]      Do Until rst.EOF[/LEFT]
  [LEFT]         strNode1Text = StrConv("Level1" & rst![LastNameFirst], _[/LEFT]
  [LEFT]            vbLowerCase)[/LEFT]
  [LEFT]         Set nod = .Nodes.Add(Key:=strNode1Text, _[/LEFT]
  [LEFT]            Text:=rst![LastNameFirst])[/LEFT]
  [LEFT]         'Expand the entire node[/LEFT]
  [LEFT]         nod.Expanded = True[/LEFT]
  [LEFT]         rst.MoveNext[/LEFT]
  [LEFT]      Loop[/LEFT]
  [LEFT]      rst.Close[/LEFT]
  [LEFT]      [FONT=&quot][/FONT][/LEFT]
  [LEFT]      'Fill Level 2[/LEFT]
  [LEFT]      Set rst = dbs.OpenRecordset(strQuery2, dbOpenForwardOnly)[/LEFT]
  [LEFT][FONT=&quot] [/FONT][/LEFT]
  [LEFT]      'Add a node object for each record in the "qryEBooksByAuthor"[/LEFT]
  [LEFT]      'table/query.[/LEFT]
  [LEFT]      'The value of the Relative argument matches the Key argument value[/LEFT]
  [LEFT]      'for the Level 1 node this Level 2 node belongs to.[/LEFT]
  [LEFT]      'The Relationship argument takes a named constant, tvwChild,[/LEFT]
  [LEFT]      'indicating that the Level 2 node becomes a child node of the[/LEFT]
  [LEFT]      'Level 1 node named in the Relative argument.[/LEFT]
  [LEFT]      'The Key argument concatenates the level number and the Title[/LEFT]
  [LEFT]      'field of the Level 2 query, to create a unique key value for the node.[/LEFT]
  [LEFT]      'The Text argument is the text displayed as a Level 2 node in the[/LEFT]
  [LEFT]      'TreeView control[/LEFT]
  [LEFT][FONT=&quot] [/FONT][/LEFT]
  [LEFT]      Do Until rst.EOF[/LEFT]
  [LEFT]         strNode1Text = StrConv("Level1" & rst![LastNameFirst], vbLowerCase)[/LEFT]
  [LEFT]         strNode2Text = StrConv("Level2" & rst![Title], vbLowerCase)[/LEFT]
  [LEFT]         strVisibleText = rst![Title][/LEFT]
  [LEFT]         .Nodes.Add relative:=strNode1Text, _[/LEFT]
  [LEFT]            relationship:=tvwChild, _[/LEFT]
  [LEFT]            Key:=strNode2Text, _[/LEFT]
  [LEFT]            Text:=strVisibleText[/LEFT]
  [LEFT]         rst.MoveNext[/LEFT]
  [LEFT]      Loop[/LEFT]
  [LEFT]      rst.Close[/LEFT]
  [LEFT]      [FONT=&quot][/FONT][/LEFT]
  [LEFT]   End With[/LEFT]
  [LEFT]   dbs.Close[/LEFT]
  [LEFT][FONT=&quot] [/FONT][/LEFT]
  [LEFT]ErrorHandlerExit:[/LEFT]
  [LEFT]   Exit Function[/LEFT]
  [LEFT][FONT=&quot] [/FONT][/LEFT]
  [LEFT]ErrorHandler:[/LEFT]
  [LEFT]   Select Case Err.Number[/LEFT]
  [LEFT]      Case 35601[/LEFT]
  [LEFT]         'Element not found[/LEFT]
  [LEFT]         strMessage = "Possible Causes: You selected a table/query" _[/LEFT]
  [LEFT]            & " for a child level which does not correspond to a value" _[/LEFT]
  [LEFT]            & " from its parent level."[/LEFT]
  [LEFT]         intVBMsg = MsgBox(Error$ & strMessage, vbOKOnly + _[/LEFT]
  [LEFT]            vbExclamation, "Run-time Error: " & Err.Number)[/LEFT]
  [LEFT]      Case 35602[/LEFT]
  [LEFT]         'Key is not unique in collection[/LEFT]
  [LEFT]         strMessage = "Possible Causes: You selected a non-unique" _[/LEFT]
  [LEFT]            & " field to link levels."[/LEFT]
  [LEFT]         intVBMsg = MsgBox(Error$ & strMessage, vbOKOnly + _[/LEFT]
  [LEFT]            vbExclamation, "Run-time Error: " & Err.Number)[/LEFT]
  [LEFT]      Case Else[/LEFT]
  [LEFT]         intVBMsg = MsgBox(Error$ & "@@", vbOKOnly + _[/LEFT]
  [LEFT]            vbExclamation, "Run-time Error: " & Err.Number)[/LEFT]
  [LEFT]   End Select[/LEFT]
  [LEFT]   Resume ErrorHandlerExit[/LEFT]
  [LEFT][FONT=&quot] [/FONT][/LEFT]
  [LEFT]End Function[/LEFT]