Problem: Treeview Control, 6.0

[Schippi]

Hi guys,
I was trying to display the org chart in a treeview. I got this code but currently I get an error message saying: Syntax Error (missing operator) in expression.

Sub Form_Load()
      Const strTableQueryName = "Contacts"
      Dim db As Database, rst As Recordset
      Set db = CurrentDb
      Set rst = db.OpenRecordset(strTableQueryName, dbOpenDynaset, dbReadOnly)
      AddBranch _
        rst:=rst, _
        strPointerField:="Reports to", _
        strIDField:="ContactID", _
        strTextField:="Last Name"
End Sub
 
      '================= AddBranch Sub Procedure =========================
      '      Recursive Procedure to add branches to TreeView Control
      'Requires:
      '   ActiveX Control:  TreeView Control
      '              Name:  xTree
      'Parameters:
      '               rst:  Self-referencing Recordset containing the data
      '   strPointerField:  Name of field pointing to parent's primary key
      '        strIDField:  Name of parent's primary key field
      '      strTextField:  Name of field containing text to be displayed
      '===================================================================
      Sub AddBranch(rst As Recordset, strPointerField As String, _
                    strIDField As String, strTextField As String, _
                    Optional varReportToID As Variant)
      On Error GoTo errAddBranch
      Dim nodCurrent As Node, objTree As TreeView
      Dim strCriteria As String, strText As String, strKey As String
      Dim nodParent As Node, bk As String
      Set objTree = Me!xTree.Object
      If IsMissing(varReportToID) Then  ' Root Branch.
         strCriteria = strPointerField & " Is Null"
      Else  ' Search for records pointing to parent.
         strCriteria = BuildCriteria(strPointerField, _
         rst.Fields(strPointerField).Type, _
         "=" & varReportToID)
         Set nodParent = objTree.Nodes("a" & varReportToID)
      End If
      ' Find the first emp to report to the boss node.
      rst.FindFirst strCriteria
      Do Until rst.NoMatch
         ' Create a string with LastName.
         strText = rst(strTextField)
         strKey = "a" & rst(strIDField)
         If Not IsMissing(varReportToID) Then  'add new node to the parent
            Set nodCurrent = objTree.Nodes.Add(nodParent, _
            tvwChild, strKey, strText)
         Else    ' Add new node to the root.
            Set nodCurrent = objTree.Nodes.Add(, , strKey, _
            strText)
         End If
         ' Save your place in the recordset so we can pass by ref for
         ' speed.
         bk = rst.Bookmark
         ' Add employees who report to this node.
         AddBranch rst, strPointerField, strIDField, strTextField, _
         rst(strIDField)
         rst.Bookmark = bk     ' Return to last place and continue search.
         rst.FindNext strCriteria   ' Find next employee.
      Loop
exitAddBranch:
      Exit Sub
      '--------------------------Error Trapping --------------------------
errAddBranch:
      MsgBox "Can't add child:  " & Err.Description, vbCritical, _
      "AddBranch Error:"
      Resume exitAddBranch
      End Sub

I think my first AddBranch is incorrect but to be honest, I have no idea how to fix it.
If you have a better idea on how to create an orgchart in access (purely access, not VISIO or so) I would like to hear you thoughts.

best regards,
Andreas

 

[boblarson]

Well, first off you can simplify your procedure call to this:

'...truncated for brevity...
 
      AddBranch rst, "Reports to", "ContactID", "Last Name"
End Sub

 

[Schippi]

Hi Bob,
thanks for your answer but i already tried it and it is still the same error. And I have removed all ,_ and put them into one line. It always runs into the errAddBranch with the above mentioned error.
best regards,
andreas

 

[boblarson]

It's probably choking on the space in the field name and perhaps other of the values being passed. You can try modifying the Call to this:

AddBranch rst, "[B][COLOR=red][[/COLOR][/B]Reports to[B][COLOR=red]][/COLOR][/B]", "ContactID", "[B][COLOR=red][[/COLOR][/B]Last Name[B][COLOR=red]][/COLOR][/B]"

 

[Schippi]

well you're right. that was the issue, it's now working correctly. thanks for your quick help.