Solved Treeview (1 Viewer)

ClaraBarton

Registered User.
Local time
Today, 09:06
Joined
Oct 14, 2019
Messages
427
Code:
Private Sub tvCookbooks_NodeClick(ByVal Node As Object)

On Error GoTo ErrorHandler

    Dim dbs As DAO.Database
    Dim rs As Recordset
    Dim KeyPart As Integer
    Set dbs = CurrentDb
    Set rs = Me.RecordsetClone
    
    KeyPart = Val(Right(Node.key, Len(Node.key) - 1))
 
     rs.FindFirst "[RecipeID] = " & KeyPart
        
        Debug.Print KeyPart
        Me.Bookmark = rs.Bookmark
    Set rs = Nothing
        
cleanup:
    Exit Sub

ErrorHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume cleanup

End Sub

Private Sub tvCookbooks_Load()
On Error GoTo ErrorHandler
 
Dim db As DAO.Database
Dim rsCookbooks As DAO.Recordset
Dim rsChapters As DAO.Recordset
Dim rsRecipes As DAO.Recordset
Dim strSql As String
'Dim intDistance As Integer

' Clear the tree

    Me.tvCookbooks.Nodes.Clear

    Set db = CurrentDb()

'add the cookbooks
    strSql = "SELECT cookbookid, name " & _
        "FROM t_cookbook " & _
        "ORDER BY name"
    Set rsCookbooks = db.OpenRecordset(strSql)

    Do Until rsCookbooks.EOF
         Me.tvCookbooks.Nodes.Add _
            key:="CB" & rsCookbooks!cookbookid, _
            Text:=rsCookbooks!Name
        rsCookbooks.MoveNext
    Loop

'add the chapters
    strSql = "SELECT cookbookchapterid, name, cookbookid " & _
        "FROM t_cookbookchapter " & _
        "ORDER BY name"

    Set rsChapters = db.OpenRecordset(strSql)

    Do Until rsChapters.EOF
        Me.tvCookbooks.Nodes.Add _
            Relative:="CB" & rsChapters!cookbookid, _
            Relationship:=tvwChild, _
            key:="CH" & rsChapters!cookbookchapterid, _
            Text:=rsChapters!Name
        rsChapters.MoveNext
    Loop

'now the recipes

    strSql = "SELECT t_recipe.cookbookid, t_recipe.recipeid, " & _
        "t_recipe.recipename, t_recipe.cookbookchapterid " & _
        "FROM t_recipe " & _
        "ORDER BY t_recipe.cookbookchapterid"
    
    Set rsRecipes = db.OpenRecordset(strSql)
        
        Dim stCBCH As String
  
        Do Until rsRecipes.EOF
            If Nz(rsRecipes!cookbookchapterid, 0) = 0 Then
                stCBCH = "CB" & rsRecipes!cookbookid
            Else
                stCBCH = "CH" & rsRecipes!cookbookchapterid
            End If
            
            Me.tvCookbooks.Nodes.Add _
                Relative:=stCBCH, _
                Relationship:=tvwChild, _
                key:="R" & rsRecipes!recipeid, _
                Text:=rsRecipes!recipename
            rsRecipes.MoveNext
       Loop
    
GoTo cleanup

cleanup:
On Error Resume Next
    rsCookbooks.Close
    Set rsCookbooks = Nothing
    rsChapters.Close
    Set rsChapters = Nothing
    rsRecipes.Close
    Set rsRecipes = Nothing
    Set db = Nothing
Exit Sub

ErrorHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume cleanup

End Sub
This is a project I've been tinkering at for a long time. Living Cookbook went out of business and when I got a new computer I lost the program so all the tables are what I'm left with.
The treeview here looks nice but doesn't work.... Some of the recipes come up right. Some don't at all and some bring up a random recipe. I suspect it has to do with levels, like some cookbooks do not have chapters and some do. They seem to look right, just don't work right. If nothing worked at all I'd get it but this has me buffaloed. I notice levels are a field in the cookbooks but I don't know where to use them.
 

jdraw

Super Moderator
Staff member
Local time
Today, 12:06
Joined
Jan 23, 2006
Messages
15,364
You might try youtube Treeview theSmileyCoder. He is an often referenced Treeview guru.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:06
Joined
May 21, 2018
Messages
8,463
My first look at the code and it looks correct to me. Can you validate that the queries are correct. In other words do the correct recipes match to the correct chapter when a chapter exists? Do the correct recipes show when there is no chapter but there is a book id? My first guess is that the code is OK but the data is corrupted. The parent child relationships are off. Some are missing, and some are related to the wrong chapter or wrong book.
 

ClaraBarton

Registered User.
Local time
Today, 09:06
Joined
Oct 14, 2019
Messages
427
Absolutely. Tried to cut all unnecessary...
 

Attachments

  • Database10.zip
    1.7 MB · Views: 308

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:06
Joined
May 7, 2009
Messages
19,169
i have not thoroughly view if you have all those recipe.
 

Attachments

  • Database10.zip
    1.6 MB · Views: 485

ClaraBarton

Registered User.
Local time
Today, 09:06
Joined
Oct 14, 2019
Messages
427
perhaps I removed too many? Here is a complete list
 

Attachments

  • Database11.zip
    1.7 MB · Views: 518

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:06
Joined
May 21, 2018
Messages
8,463
From what you provided in the last thread the tree is completely correct. I am guessing that is still not the correct data. There is only 228 recipes and all 228 fall into the correct chapters. But I am guessing that is not the correct data. Those 228 only fall into two books (Best of Living and Quick and Easy). Look at the two queries I provided to show that the tree and the data are correct.

As far as I can tell there is nothing wrong with the code.
 

Attachments

  • Database11_MajP.zip
    1.7 MB · Views: 506

Gasman

Enthusiastic Amateur
Local time
Today, 16:06
Joined
Sep 21, 2011
Messages
14,044
@ClaraBarton
If you ever get this working, I'd love to see a copy please?

Plus it does appear to work if a recipe actually exists in qryRecipe?
1605880092588.png


A lot do not exist?, like ID 276 & 466 ?
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:06
Joined
May 21, 2018
Messages
8,463
A lot do not exist?, like ID 276 & 466 ?
That is not correct. They all exist as I shown. The reason that they do not show up in the form is that the OP has an inner join to the recipe image. This should be a left outer join. The only ingredients showing are those for recipes with an image. Make it an outer join and the show up.
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:06
Joined
Sep 21, 2011
Messages
14,044
That is not correct. They all exist as I shown. The reason that they do not show up in the form is that the OP has an inner join to the recipe image. This should be a left outer join. The only ingredients showing are those for recipes with an image. Make it an outer join and the show up.
Like this?
Code:
SELECT t_recipe.recipeid, t_recipe.recipename, t_recipe.comments, t_recipe.preparationtime, t_recipe.cookingtime, t_recipe.readyintime, t_recipe.cookbookchapterid, t_recipe.recipephotographflag, t_recipe.author, t_recipe.source, t_recipe.createdate, t_recipe.yield, t_recipeimage.imagepath, t_cookbook.name AS Cookbook, t_cookbook.cookbookid
FROM (t_recipe 
LEFT JOIN t_recipeimage ON t_recipe.recipeID = t_recipeimage.recipeid) 
LEFT JOIN t_cookbook ON t_recipe.cookbookid = t_cookbook.cookbookid;
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:06
Joined
May 21, 2018
Messages
8,463
So far I cannot see anything wrong except the query on the form. It is hard to fix something, that does not appear broken.
 

Attachments

  • Database12_MajP.zip
    1.7 MB · Views: 563

Gasman

Enthusiastic Amateur
Local time
Today, 16:06
Joined
Sep 21, 2011
Messages
14,044
Thank you @MajP
I'm also trying to work out why the procedure index is incremented by 1 as well. :unsure:

I have never used a treeview control, so was just curious. :)
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:06
Joined
May 21, 2018
Messages
8,463
I'm also trying to work out why the procedure index is incremented by 1 as well.
Not sure what that means. Can you identify a location or screenshot?
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:06
Joined
Sep 21, 2011
Messages
14,044
The procedure for a recipe always start at 2, due to the field source being [procedureindex]+1 :unsure:
1605887226282.png
 

ClaraBarton

Registered User.
Local time
Today, 09:06
Joined
Oct 14, 2019
Messages
427
You nailed it. I was so hung up on getting the treeview to work that I neglected everything else. I've reworked the query and it all works beautifully. Now I can think about the other things. Thank you So So So much.
 

ClaraBarton

Registered User.
Local time
Today, 09:06
Joined
Oct 14, 2019
Messages
427
The procedure index was starting at 0 so I added 1 but I need to work on it some more.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:06
Joined
May 21, 2018
Messages
8,463
As far as I can tell you no longer have any procedure index starting at 0. My guess is you did an update query but never changed the control source.
 

Users who are viewing this thread

Top Bottom