Treeview control wizard

Question

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))

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;

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

Answer 1

Unfortunately, your email address was removed from the post.  You can send me an email through the link on my Website (I won't put it here because it would probably also be removed).

Answer 2

Hi Helen

Yes I got the code from (Ebook Companion.mdb) and it was very helpful for me, around 6 months ago I need a tree view with more than 2 level and searched a lot in Google and many of questions in many forums but I did not get complete answer. with helping one of the forums I tried to change your code for 3 level and it was done but tree view was working very slow because my records was around (40000 records), after I found another sample database (TreeViewMenuItem.mdb) that was keeping parent & position of each node in one table and then tree view will be made from this table but making this table is very complicate for me (it needs some reverse query and union query).

My email is (masoud_@yahoo.com) and I will be very happy if I can have new database (Reader's Companion) because using that was easy and very quick.

I could open your site, there are great books in that, since 10 years ago I used for example the code & samples there are in (contacts.mdb in Access inside out) and they are helping me a lot in these years.

I would like to read all of books found in your site (www.helenfeddema.com) because I am sure they are very helpful in action for me. 

Best Regards.

Answer 3

Your original code looks like the code from my old sample database (Code Sample 42, Ebook Companion.mdb).  The Wizard was for Access 97 only; in later versions of Access, I copied the code created by the Wizard and modified it as needed.  Recently, I found that the Treeview control is not supported in 64-bit Access, but I found an alternative at this Website:  http://www.jkp-ads.com/articles/treeview.asp.

I have redone my Reader's Companion database as an .accdb database, using the Treeview alternative.  One of the forms has a 3-level Treeview (Authors, Series, Books).  This database will be featured in the next Access Archon article, and will be posted to my Website as Access Archon #226.  The Website (done with FrontPage) is not supported by IE 11 and current versions of some other browsers, so I need to update it with a  more modern Web design program.   If you need the database, and can't access it via my Website (www.helenfeddema.com), let me know and I can email it to you.

Answer 4

i found the article with this address (http://www.databasejournal.com/features/msaccess/article.php/1483461/Access-2000-How-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 reply.

i think it will solve my problem.

thanks

Answer 5

Your job is not to find some code that shows how to fill 3 levels, and perhaps down the road when you need 4 levels to search again and find a sample that fills 4 levels, but rather to understand how treeviews are filled.

Only 2 lines of code are relevant:

Set nod = .Nodes.Add(Key:=strNode1Text, Text:=rst![LastNameFirst])

and

.Nodes.Add relative:=strNode1Text, _

            relationship:=tvwChild, _

            Key:=strNode2Text, _

            Text:=strVisibleText

In the second line you can see how a child node is added. This is how ALL child nodes are added, regardless of level.