populate treeview using replication ID (GUID)

[Matt]

Hello NG

I used this VBA code poulate a treeview from a table with different
employees and supervisors (Anybody can be also a supervisor if his PersonlID
is set in the ReportsToID field of an another emplyee).
It worked very well but now I need the [PersonalID] and [ReportsToID] to be
replication IDs.
But for GUID the FindFirst method doesn't work :-(
I searched the net and found the Seek-method but I have no clue how to use
this with the GUID. I can't get it to work I think it has something to do
with the converting of the GUID to String (StringFromGuid) but I'm stuck
here.

Can anybody guide me adapting this code below to work with GUID?

Thank you very much
Regrads
Matt


Private Sub Form_Load()
On Error GoTo ErrForm_Load

Dim nodCurrent As node
Dim strText As String, nodRoot As node
Dim bk As String, node As node
Dim objTree As TreeView


Set db = CurrentDb
Set rst = db.OpenRecordset("tblEmployees", dbOpenDynaset)

If rst.EOF Then
Exit Sub
Else

' Create a reference to the TreeView Control.
Set objTree = Form_Employee!xTree.Object
' Find first Employee without supervisor
rst.FindFirst "[ReportsToID] = 0" 'would be IS NULL using GUID

' Build the TreeView list with supervisor and employees
Do Until rst.NoMatch
' Extract employee_name.
strText = rst![First] & " " & rst![Last]

' Add a root level node to the tree for the parent process
Set nodCurrent = objTree.Nodes.Add(, , "a" & rst!PersonalID, strText,
Int(rst!Image))
' Use a placeholder to save this place in the recordset.
bk = rst.Bookmark
' Run a recursive procedure to add all the child nodes für
AddChildren nodCurrent, rst
' Return to your placeholder.
rst.Bookmark = bk
' Find the next supervisor.
rst.FindNext "[ReportsToID] = 0"
Loop

End Sub

Sub AddChildrenA(nodboss As node, rst As DAO.Recordset)
Dim nodCurrent As node
Dim objTree As TreeView, strText As String, bk As String
Dim db As DAO.Database
'Dim rst As DAO.Recordset

' Create a reference to the TreeView control.
Set objTree = Form_Employee!xTree.Object

' Find the first employee who reports to the supervisor for this
' node.
rst.FindFirst "[ReportsToID] =" & Mid(nodboss.Key, 2)
' Build the list of employees who report to this supervisor.
Do Until rst.NoMatch
' Extract the employee's name.
strText = rst![First] & " " & rst![Last]
' Add as a child node to the tree.
Set nodCurrent = objTree.Nodes.Add(nodboss, tvwChild, "a" &
rst!PersonalID, strText, Int(rst!Image))
' Save your place in the recordset.
bk = rst.Bookmark
' Add any employees for whom the current node is a supervisor.
AddChildren nodCurrent, rst
' Return to your place in the recordset and continue to search.
rst.Bookmark = bk
' Find the next employee who reports to this supervisor.
rst.FindNext "[ReportsToID]=" & Mid(nodboss.Key, 2)

Loop


End Sub

 

[Dev Ashish]

I searched the net and found the Seek-method but I have no clue how to
use this with the GUID. I can't get it to work I think it has
something to do with the converting of the GUID to String
(StringFromGuid) but I'm stuck here.

See if something like this works

rst .FindFirst "StringFromGUID(ReportsToID) = " & StringFromGUID
(someControl)

-- Dev

 

[Matt]

Thanks Dev

But I don't use the content of a control the records are all related to the
content of the [EmployeeID] or [ReportsToID] field in my table.

I found the problem - hope someone can help me with this:

To find the first "Supervisor" I have to search the table where the
[ReportsToID] is empty
the rst.FindFirst "StringFromGuid(ReportsToID)=" & "" creates an error.

Any advice is highly appreciated

Regards
Matt

 

[John Vinson]

To find the first "Supervisor" I have to search the table where the
[ReportsToID] is empty
the rst.FindFirst "StringFromGuid(ReportsToID)=" & "" creates an error.

That's because a NULL field is not a zero length string.

Try

rst.FindFirst "[ReportsToID] IS NULL"

 

[Matt]

John thank you for your fast reply!

Now at least I get the supervisors in the treeview in the first procedure
but the AddChildren-procedure only adds the one employee who comes before
the two supervisors-records in the table. The empty [ReportsToID] of the
supervisors seems to disturb the 2nd procedure (AddChildren). (Maybe the
bookmark??)

To find out what's happening I tried the following:
I created a query with the query wizard and added an extra field with an
expression in which I relate to the [ReportsToID] field and convert the
replication id in this field to a String with
StringFromGuid[ReportsToID].
If I open that query it shows "#error" for the empty [ReportsToID] related
field of the supervisor records.
I guess this disturbs the whole 2nd procedure?
Is there any way to convert this #error into some String or avoid it
somehow?

PLs. see also my code below.
I tried the whole afternoon but don't know what to do :-(

Your help is highly appreciated

regards Matt

Private Sub Form_Load()
On Error GoTo ErrForm_Load

Dim nodCurrent As node
Dim strText As String, nodRoot As node
Dim bk As String, node As node
Dim objTree As TreeView


Set db = CurrentDb
Set rst = db.OpenRecordset("tblEmployees", dbOpenDynaset)

If rst.EOF Then
Exit Sub
Else

' Create a reference to the TreeView Control.
Set objTree = Form_Employee!xTree.Object
' Find first Employee without supervisor
rst.FindFirst "[ReportsToID] IS NULL"
' Build the TreeView list with supervisor and employees
Do Until rst.NoMatch
' Extract employee_name.
strText = rst![First] & " " & rst![Last]

' Add a root level node to the tree for the supervisor
Set nodCurrent = objTree.Nodes.Add(, , "a" & rst!PersonalID, strText,
Int(rst!Image))
' Use a placeholder to save this place in the recordset.
bk = rst.Bookmark
' Run a recursive procedure to add all the child nodes für
AddChildren nodCurrent, rst
' Return to your placeholder.
rst.Bookmark = bk
' Find the next supervisor.
rst.FindNext "[ReportsToID] IS NULL"
Loop

End Sub

Sub AddChildrenA(nodboss As node, rst As DAO.Recordset)
Dim nodCurrent As node
Dim objTree As TreeView, strText As String, bk As String
Dim db As DAO.Database
'Dim rst As DAO.Recordset

' Create a reference to the TreeView control.
Set objTree = Form_Employee!xTree.Object

' Find the first employee who reports to the supervisor for this
' node.
rst.FindFirst "[ReportsToID] =" & Mid(nodboss.Key, 2)
' Build the list of employees who report to this supervisor.
Do Until rst.NoMatch
' Extract the employee's name.
strText = rst![First] & " " & rst![Last]
' Add as a child node to the tree.
Set nodCurrent = objTree.Nodes.Add(nodboss, tvwChild, "a" &
rst!PersonalID, strText, Int(rst!Image))
' Save your place in the recordset.
bk = rst.Bookmark
' Add any employees for whom the current node is a supervisor.
AddChildren nodCurrent, rst
' Return to your place in the recordset and continue to search.
rst.Bookmark = bk
' Find the next employee who reports to this supervisor.
rst.FindNext "[ReportsToID]=" & Mid(nodboss.Key, 2)

Loop
End Sub

To find the first "Supervisor" I have to search the table where the
[ReportsToID] is empty
the rst.FindFirst "StringFromGuid(ReportsToID)=" & "" creates an error.

That's because a NULL field is not a zero length string.

Try

rst.FindFirst "[ReportsToID] IS NULL"

 

[Matt]

Anbody please?

John thank you for your fast reply!

Now at least I get the supervisors in the treeview in the first procedure
but the AddChildren-procedure only adds the one employee who comes before
the two supervisors-records in the table. The empty [ReportsToID] of the
supervisors seems to disturb the 2nd procedure (AddChildren). (Maybe the
bookmark??)

To find out what's happening I tried the following:
I created a query with the query wizard and added an extra field with an
expression in which I relate to the [ReportsToID] field and convert the
replication id in this field to a String with
StringFromGuid[ReportsToID].
If I open that query it shows "#error" for the empty [ReportsToID] related
field of the supervisor records.
I guess this disturbs the whole 2nd procedure?
Is there any way to convert this #error into some String or avoid it
somehow?

PLs. see also my code below.
I tried the whole afternoon but don't know what to do :-(

Your help is highly appreciated

regards Matt

Private Sub Form_Load()
On Error GoTo ErrForm_Load

Dim nodCurrent As node
Dim strText As String, nodRoot As node
Dim bk As String, node As node
Dim objTree As TreeView


Set db = CurrentDb
Set rst = db.OpenRecordset("tblEmployees", dbOpenDynaset)

If rst.EOF Then
Exit Sub
Else

' Create a reference to the TreeView Control.
Set objTree = Form_Employee!xTree.Object
' Find first Employee without supervisor
rst.FindFirst "[ReportsToID] IS NULL"
' Build the TreeView list with supervisor and employees
Do Until rst.NoMatch
' Extract employee_name.
strText = rst![First] & " " & rst![Last]

' Add a root level node to the tree for the supervisor
Set nodCurrent = objTree.Nodes.Add(, , "a" & rst!PersonalID, strText,
Int(rst!Image))
' Use a placeholder to save this place in the recordset.
bk = rst.Bookmark
' Run a recursive procedure to add all the child nodes für
AddChildren nodCurrent, rst
' Return to your placeholder.
rst.Bookmark = bk
' Find the next supervisor.
rst.FindNext "[ReportsToID] IS NULL"
Loop

End Sub

Sub AddChildrenA(nodboss As node, rst As DAO.Recordset)
Dim nodCurrent As node
Dim objTree As TreeView, strText As String, bk As String
Dim db As DAO.Database
'Dim rst As DAO.Recordset

' Create a reference to the TreeView control.
Set objTree = Form_Employee!xTree.Object

' Find the first employee who reports to the supervisor for this
' node.
rst.FindFirst "[ReportsToID] =" & Mid(nodboss.Key, 2)
' Build the list of employees who report to this supervisor.
Do Until rst.NoMatch
' Extract the employee's name.
strText = rst![First] & " " & rst![Last]
' Add as a child node to the tree.
Set nodCurrent = objTree.Nodes.Add(nodboss, tvwChild, "a" &
rst!PersonalID, strText, Int(rst!Image))
' Save your place in the recordset.
bk = rst.Bookmark
' Add any employees for whom the current node is a supervisor.
AddChildren nodCurrent, rst
' Return to your place in the recordset and continue to search.
rst.Bookmark = bk
' Find the next employee who reports to this supervisor.
rst.FindNext "[ReportsToID]=" & Mid(nodboss.Key, 2)

Loop
End Sub

To find the first "Supervisor" I have to search the table where the
[ReportsToID] is empty
the rst.FindFirst "StringFromGuid(ReportsToID)=" & "" creates an error.

That's because a NULL field is not a zero length string.

Try

rst.FindFirst "[ReportsToID] IS NULL"

 

[John Vinson]

Anbody please?

Patience please, Matt. Like almost everyone here, I'm an unpaid
volunteer, donating time for which I could otherwise be charging my
clients at $<N.O.Y.B.> per hour on the newsgroup. I had other work to
do this afternoon; I'll try to review your lengthy post later this
evening.

If the service is unsatisfactory... please take the cost into
consideration.

 

[Matt]

Sorry John for pushing - no personal offense - I just hoped to solve this
issue yesterday night (after 2 days trying) :-(
But you are right: I'll be patient now and take it more easy.

Sorry and thank you one more time

Regrads
Matt

 

[John Vinson]

John thank you for your fast reply!

Now at least I get the supervisors in the treeview in the first procedure
but the AddChildren-procedure only adds the one employee who comes before
the two supervisors-records in the table. The empty [ReportsToID] of the
supervisors seems to disturb the 2nd procedure (AddChildren). (Maybe the
bookmark??)

To find out what's happening I tried the following:
I created a query with the query wizard and added an extra field with an
expression in which I relate to the [ReportsToID] field and convert the
replication id in this field to a String with
StringFromGuid[ReportsToID].
If I open that query it shows "#error" for the empty [ReportsToID] related
field of the supervisor records.

Why do you need the string? Evidently the StringFromGuID function
errors when you pass it a NULL; maybe you could use an expression like

IIF(IsNull([ReportsToID], "<Supervisor>",
StringFromGuid([ReportsToID])

 

[Matt]

Hi John

Thank you again for your patience and support!

The String I need because FindFirst/Next wouldn't work with GUID - if you
have another solution for that, it would be very helpful.

I didn't know the IIF function you proposed - it looks very promising. I
have to go abroad now for 1,5 weeks and report back to you after that.

If you're looking at my original code and something better comes to your
mind - pls. let me know (although , you might have better things to do
....;-))

Thank you again for your professional and continuous help

Best regards
Matt

John thank you for your fast reply!

Now at least I get the supervisors in the treeview in the first procedure
but the AddChildren-procedure only adds the one employee who comes before
the two supervisors-records in the table. The empty [ReportsToID] of the
supervisors seems to disturb the 2nd procedure (AddChildren). (Maybe the
bookmark??)

To find out what's happening I tried the following:
I created a query with the query wizard and added an extra field with an
expression in which I relate to the [ReportsToID] field and convert the
replication id in this field to a String with
StringFromGuid[ReportsToID].
If I open that query it shows "#error" for the empty [ReportsToID] related
field of the supervisor records.

Why do you need the string? Evidently the StringFromGuID function
errors when you pass it a NULL; maybe you could use an expression like

IIF(IsNull([ReportsToID], "<Supervisor>",
StringFromGuid([ReportsToID])

 

[John Vinson]

Hi John

Thank you again for your patience and support!

The String I need because FindFirst/Next wouldn't work with GUID - if you
have another solution for that, it would be very helpful.

I didn't know the IIF function you proposed - it looks very promising. I
have to go abroad now for 1,5 weeks and report back to you after that.

I'll be out of town the first part of December so I'll likely be gone
when you get back... so here's a quick idea. I'd suggest starting a
new thread when you get back so some of the other MVP's will join in.

If you're looking at my original code and something better comes to your
mind - pls. let me know (although , you might have better things to do
...;-))

You could do without directly referencing the GUID at all by using a
Recordset based on a Query, joining the table to itself, linking the
supervisor's ID to that supervisor's reports' ID's. That would give
you just that group of employees as the entirity of the recordset,
without any need to FindFirst on a GUID.