StringFromGUID with empty replication ID

  • Thread starter Thread starter Matt
  • Start date Start date
M

Matt

Hello NG

I populating a treeview control with the employee data from the tblemployee
table (fields: PersonalID (Replication ID); First (Text); Last (Text);
ReportsToID(Replication ID)) with the code below.
For the supervisors the replication ID in the [ReportsToID] field is empty.
How do I have to formulate the Find.First for the supervisors?

rst.FindFirst StringFromGuid(ReportsToID) = " & "" 'didn't work :-(

Pls. help
Thank you very much
Matt


Private Sub Form_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 StringFromGuid(ReportsToID) = " 'what should I put here?
"" didn't work

' 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 StringFromGuid(ReportsToID) = " 'what should I put here?
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 strGuid as String

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

strGuid = StringFromGuid("[ReportsToID])
' Find the first employee who reports to the supervisor for this
' node.
rst.FindFirst "[ReportsToID] =" & strGuid
' 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]=" & strGuid

Loop
End Sub
 
It appears that you only want the Employees without supervisors. So,
instead of retrieving ALL emps, and trying to find those without the
ReportsTo ID, just change your query to ONLY include those that don't have
it.

Set rst = db.OpenRecordset("Select * from tblEmployees Where ReportsTo is
Null", dbOpenDynaset)


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Matt said:
Hello NG

I populating a treeview control with the employee data from the tblemployee
table (fields: PersonalID (Replication ID); First (Text); Last (Text);
ReportsToID(Replication ID)) with the code below.
For the supervisors the replication ID in the [ReportsToID] field is empty.
How do I have to formulate the Find.First for the supervisors?

rst.FindFirst StringFromGuid(ReportsToID) = " & "" 'didn't work :-(

Pls. help
Thank you very much
Matt


Private Sub Form_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 StringFromGuid(ReportsToID) = " 'what should I put here?
"" didn't work

' 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 StringFromGuid(ReportsToID) = " 'what should I put here?
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 strGuid as String

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

strGuid = StringFromGuid("[ReportsToID])
' Find the first employee who reports to the supervisor for this
' node.
rst.FindFirst "[ReportsToID] =" & strGuid
' 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]=" & strGuid

Loop
End Sub
Click to expand...
 
Steve thanks for your answer

But as you can see from my code, the first procedure calls the AddChildren
procedure which adds the employees to every supervisor set in the first
procedure.
So I need all records - not only the supervisors.(or am I wrong?)

If I create a query and convert the (ReportsToID) with
StringFromGuid(ReportsToID) into a String it shows an "#error" for the
supervisors which have an empty replications ID in [ReportsToID].
If I just could convert this #error into some String - I guess that would
help me a lot!

Please guide me!

best regards
Matt



[MVP] S. Clark said:
It appears that you only want the Employees without supervisors. So,
instead of retrieving ALL emps, and trying to find those without the
ReportsTo ID, just change your query to ONLY include those that don't have
it.

Set rst = db.OpenRecordset("Select * from tblEmployees Where ReportsTo is
Null", dbOpenDynaset)


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Matt said:
Hello NG

I populating a treeview control with the employee data from the tblemployee
table (fields: PersonalID (Replication ID); First (Text); Last (Text);
ReportsToID(Replication ID)) with the code below.
For the supervisors the replication ID in the [ReportsToID] field is empty.
How do I have to formulate the Find.First for the supervisors?

rst.FindFirst StringFromGuid(ReportsToID) = " & "" 'didn't work :-(

Pls. help
Thank you very much
Matt


Private Sub Form_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 StringFromGuid(ReportsToID) = " 'what should I put here?
"" didn't work

' 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 supervisors
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 StringFromGuid(ReportsToID) = " 'what should I put here?
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 strGuid as String

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

strGuid = StringFromGuid("[ReportsToID])
' Find the first employee who reports to the supervisor for this
' node.
rst.FindFirst "[ReportsToID] =" & strGuid
' 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]=" & strGuid

Loop
End Sub
Click to expand...
Click to expand...
 
I get it. A supervisor has a blank ReportsTo. I don't think I'd continue
with this method, as a Supervisor can have a supervisor.

Instead, I would add a Yes/No field to the emps table, to designate a person
as a Supervisor.
I would create a query that extracts just the supervisors, using the Yes/No
field.
I would use the Supervisor query as the basis of your Form_Load population
routine.

When you need to populate the child nodes for the Supervisor, run a query
supplying the specific EmployeeID of the Supervisor.

"Select * from Emps where ReportsTo = " & rstSupervisors!EmployeeID


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Matt said:
Steve thanks for your answer

But as you can see from my code, the first procedure calls the AddChildren
procedure which adds the employees to every supervisor set in the first
procedure.
So I need all records - not only the supervisors.(or am I wrong?)

If I create a query and convert the (ReportsToID) with
StringFromGuid(ReportsToID) into a String it shows an "#error" for the
supervisors which have an empty replications ID in [ReportsToID].
If I just could convert this #error into some String - I guess that would
help me a lot!

Please guide me!

best regards
Matt



[MVP] S. Clark said:
It appears that you only want the Employees without supervisors. So,
instead of retrieving ALL emps, and trying to find those without the
ReportsTo ID, just change your query to ONLY include those that don't have
it.

Set rst = db.OpenRecordset("Select * from tblEmployees Where ReportsTo is
Null", dbOpenDynaset)


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Matt said:
Hello NG

I populating a treeview control with the employee data from the tblemployee
table (fields: PersonalID (Replication ID); First (Text); Last (Text);
ReportsToID(Replication ID)) with the code below.
For the supervisors the replication ID in the [ReportsToID] field is empty.
How do I have to formulate the Find.First for the supervisors?

rst.FindFirst StringFromGuid(ReportsToID) = " & "" 'didn't work :-(

Pls. help
Thank you very much
Matt


Private Sub Form_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 StringFromGuid(ReportsToID) = " 'what should I put here?
"" didn't work

' 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 supervisors
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 StringFromGuid(ReportsToID) = " 'what should I put here?
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 strGuid as String

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

strGuid = StringFromGuid("[ReportsToID])
' Find the first employee who reports to the supervisor for this
' node.
rst.FindFirst "[ReportsToID] =" & strGuid
' 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]=" & strGuid

Loop
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top