Not a Valid Bookmark in Treeview

[Boomshank]

Hello,

I am trying to create a treeview for users to easily manouver, there are several different levels to the treeview,

Meetings
Issues
Risks

I can populate the meetings level of the treeview with the list of meetings within the department however when i try to populate the Issues section i keep getting the "Not a valid Bookmark" error. I am having to run a different SQL for each level so im sure it has something to do with this but i'm not sure of a way around it.

Private Sub Form_Load()
 On Error GoTo ErrLoad
    Dim db As DAO.Database
    Dim rs As Recordset
    Dim nodX As Node, nodRoot As Node
    Dim bk As String
    
Me.Text5 = User
DoCmd.Close acForm, "frmLogin", acSaveNo
Text3 = DLookup("[EmployeeID]", "tblEmployees", "[Username]= form![text5]")
  level = 1
  index = 0
  ind = 0
  num = 1
  mySQL = "SELECT tblEmployeeProjects.EmployeeID, tblProjects.ProjectName, tblMeetings.MeetingName " & _
    "FROM (tblProjects INNER JOIN tblEmployeeProjects ON tblProjects.ProjectID = tblEmployeeProjects.ProjectID) INNER JOIN tblMeetings ON tblProjects.ProjectID = tblMeetings.ProjectID " & _
    "WHERE (tblEmployeeProjects.EmployeeID)= " & [Forms]![frmMap]![Text3] & ";"
  
    Set db = CurrentDb
    Set rs = db.OpenRecordset(mySQL)
        TreeView1.Nodes.Clear
    Set nodX = TreeView1.Nodes.Add(, , "Home", "Home")
    Set nodX = TreeView1.Nodes.Add("Home", tvwChild, "Proj_Root", "Projects")
        nodX.EnsureVisible
With rs
           'Loop through all EmployeeProjects, adding them to the treeview
           While Not .EOF
                ' increment num by 1
                num = num + 1
                'Assemble a key for this node
                strText = rs![ProjectName]
                    ' create the nodes
                    Set nodX = TreeView1.Nodes.Add("Proj_Root", tvwChild, "Topic" & num, strText)
                    Set nodX = TreeView1.Nodes.Add("Topic" & num, tvwChild, "meeting" & ind, "Meetings")
                    Set nodX = TreeView1.Nodes.Add("Topic" & num, tvwChild, "Issues" & ind, "Issues")
                    Set nodX = TreeView1.Nodes.Add("Topic" & num, tvwChild, "Risks" & ind, "Risks")
                    Set nodX = TreeView1.Nodes.Add("Topic" & num, tvwChild, , "Objectives")
                     bk = rs.Bookmark
                     AddMeetings nodX, rs
                     AddIssues nodX, rs
                     rs.Bookmark = bk
                     
                 .MoveNext
            Wend
End With
    Set nodX = TreeView1.Nodes.Add(1, tvwChild, , "Organisation")
    Set nodX = TreeView1.Nodes.Add(1, tvwChild, , "Searches")
    Set nodX = TreeView1.Nodes.Add(1, tvwChild, , "Reporting and Communication")
    Set nodX = TreeView1.Nodes.Add(1, tvwChild, , "Administration")
    
ExitLoad:
    Exit Sub
ErrLoad:
    MsgBox "Can't add nodes:  " & Err.Description, vbCritical, _
        "FormLoad(nodX As Node) Error:"
    Resume ExitLoad
End Sub

Sub AddIssues(nodBoss As Node, rs As Recordset)
    On Error GoTo ErrAddIssue
    Dim nodX As Node
    Dim bk As String
    Dim mySQL1 As String
    Dim myStr As String
    myStr = nodBoss.Parent
    mySQL1 = "SELECT tblProjects.ProjectName, tblIssues.Issue " & _
        "FROM (tblProjects INNER JOIN tblWorkstreams ON tblProjects.ProjectID = tblWorkstreams.ProjectID) INNER JOIN tblIssues ON tblWorkstreams.WorkstreamID = tblIssues.WorkstreamID " & _
        "WHERE (tblProjects.ProjectName)= " & """ & myStr & """ & " AND (tblIssues.Active)= Yes" & ";"
    Set db = CurrentDb
    Set rs = db.OpenRecordset(mySQL1)
        With rs
        
            While Not .EOF
                If rs![ProjectName] = myStr Then
                    level = level + 1
                    index = index + 1
                        
                    myStr = rs![Issue]
                            ' create the node
                        Set nodX = TreeView1.Nodes.Add("Issues" & ind, tvwChild, , myStr)
                End If
                    ind = ind + 1
                    .MoveNext
            Wend
        End With
ExitAddIssue:
    Exit Sub
ErrAddIssue:
    MsgBox "Can't add child:  " & Err.Description, vbCritical, _
        "AddAddIssue(nodBoss As Node) Error:"
    Resume ExitAddIssue
End Sub

Can anyone shed some light on how to solve my little issue?

Many Thanks

 

[stopher]

There's several things I can see might cause problems:

You pass rs as an arguement but then later use Set rs - so why did you need to pass it?

If you debug.print mySQL1 you'll see that the syntax is wrong around the """ & mystr & """ bit.

You've used ind in the On Load. But you increment it in the Sub. Have you made this a public variable? Otherwise the value won't get passed back.

I haven't had time to walk through the logic of your tree walk.

hth
Chris

 

[Boomshank]

Hi Stopher,

You pass rs as an arguement but then later use Set rs - so why did you need to pass it?
I have now removed the rs arguement.

You've used ind in the On Load. But you increment it in the Sub. Have you made this a public variable?
Ind is a public variable.

If you debug.print mySQL1 you'll see that the syntax is wrong around the """ & mystr & """ bit.
As an initial stage i have removed the AND syntax from mySQL1, but i am still getting errors. The where syntax now looks like

"WHERE (tblProjects.ProjectName)= " & myStr & ";"

But it keeps saying that there is missing syntax

Many Thanks

 

[DCrake]

"WHERE (tblProjects.ProjectName)= " & myStr & ";"

Should read

"WHERE (tblProjects.ProjectName)= '" & myStr & "';"