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

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

[rpeare]

What does the output of this currently look like something like:

Dickens, Charles
A Tale of Two Cities
Oliver Twist

and you want it to look like

Dickens, Charles
A Tale of Two Cities
Ref-00001
Ref-00002
Oliver Twist
Ref-00003
Ref-00004
Ref-00005

[masoud_sedighy]

yes i want like you mentioned also i could have similar refno for different books like below:

Dickens, Charles
A Tale of Two Cities
Ref-00001
Ref-00002
Oliver Twist
Ref-00002
Ref-00003
Ref-00004
Ref-00005

[rpeare]

so the same transmittal number can apply to more than one book with a different title and/or author? or is the transmittal number unique to a specific book/author.

[masoud_sedighy]

yes same transmittal number can apply to more than one book with a different title and/or author

[rpeare]

how do you know which transmittal number applies to which book/author combination? There's no indication of how the transmittal table connects to everything else in your database.

[masoud_sedighy]

there is a table tblTransmittal_Book_Author (TransID,Bookid,Authorid) both fields (combination) of (bookid,Authorid) are fk for table tblBookAuthor and transid is fk for table tblTransmittal

[rpeare]

Can you provide a sample of your database, I'd rather not mess with the code *and* have to recreate a sample database.

[masoud_sedighy]

i send the sample i am working on that. this is for 2 level , i like make it for 3 level treeview.

[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.

[rpeare]

Copy of Reader's Companion-Modified.zip

Here's a copy of your database back. The way you have your tables set up you have to nest your tree view stuff.
I also had to add a unique key to your tblTrans_Book_Autho to make this work as the text in your treeview needs to be unique as well. I just made an autonumber field to do the job.

I haven't seen this done before nor any of the code before so thanks for bringing a new and unique problem to the board.