Recursive - - Function to identify the next higher level

[Damian3215]

How would I modify this code to get me the Parent one level up from the current record?

https://www.tek-tips.com/viewthread.cfm?qid=1516577

Public Function getTopParent(varID As Variant) As Long
  Dim rs As DAO.Recordset
  Dim parentID As Variant
  Dim strSql As String
  strSql = "Select parentID from tblEquipment WHERE ID = " & varID
  Set rs = CurrentDb.OpenRecordset(strSql)
  parentID = rs!parentID
  If IsNull(parentID) Then
     getTopParent = varID
     Exit Function
  Else
    getTopParent = getTopParent(parentID)
  End If
End Function

Then how could I create a path from the highest level to the current record?
It would visually look something like this.

Name Path
Mac OS 10.1 (Cheetah) Home // Apple // Apple OS // OS X // Cheetah (Mac OS 10.1)

Function Path(ByVal id As Long)
    
' Builds a human-readable path from the root node to the current node.
' This can be done by recursion or by a loop. The loop has been selected here
' and limits the maximal depth of the returned path using a For/Next loop.
    
    Dim i As Integer
    
    Path = Null
    With GetTable("tblZones")
        For i = 1 To MAX_DEPTH
            .Seek "=", id
            If .NoMatch Then Exit Function
            
            ' append the name to the front of the path
            Path = Nz(!ChildName, !Name) & " // " + Path
            
            ' expected exit point, at the root node!
            If IsNull(!ParentID) Then Exit Function
            
            ' continue with parent node
            id = !ParentID
        Next i
    End With
    ' if this line is reached, the For/Next was exhausted
    Path = "... // " + Path
    
End Function

 

[SkipVought]

Hi,

Does your getTopParent() function return the parent for the supplied argument?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

Maybe this?

Sub GetTopParent
  Dim vID As Variant, vPID As Variant, sPath as String
  Const DELIM = "//"
  vID = InputBox("Enter value")

  Do 
    vPID = getParent(vID)
    If vPID = vID Then Exit Do
    If sPath = "" Then
       sPath = DELIM & vPID
    Else
       sPath = DELIM & vPID & sPath
    End If
    vID = vPID
  Loop
  sPath = Right(sPath, Len(sPath)-2)
  Debug.Print sPath
End Sub

Public Function getParent(varID As Variant) As Long
  Dim rs As DAO.Recordset
  Dim parentID As Variant
  Dim strSql As String
  strSql = "Select parentID from tblEquipment WHERE ID = " & varID
  Set rs = CurrentDb.OpenRecordset(strSql)
  parentID = rs!parentID
  If IsNull(parentID) Then
    getParent = varID
  Else
    getParent = parentID
  End If

  Set rs = Nothimg
End Function

Made the function not recursive in order to build your string, in the calling procedure, as you requested.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Damian3215]

Thanks for the quick feedback. I'll try and implement later today.

 

[SkipVought]

Plz make this change...

'
  vID = InputBox("Enter value")
  sPath = DELIM & vPID
  Do 
    vPID = getParent(vID)
    If vPID = vID Then Exit Do
       sPath = DELIM & vPID & sPath
    End If
    vID = vPID
  Loop

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Andrzejek]

Skip,
Don't you end up with [tt]End If[/tt] without [tt]If[/tt] ?

'
  vID = InputBox("Enter value")
  sPath = DELIM & vPID
  Do 
    vPID = getParent(vID)
    [blue]If vPID = vID Then Exit Do[/blue]
       sPath = DELIM & vPID & sPath[red]
    End If[/red]
    vID = vPID
  Loop

---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Yup!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Damian3215]

Brought in the code to my test database. Erroring in the getParent code, so needed to comment it out.

Can you take a look at the attachment and comment where I don't have it right.

 

[SkipVought]

Does your getTopParent() function return the parent for the supplied argument? I assumed that it did.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Damian3215]

Nope. and I'm not sure why. I thought it was.

 

[Damian3215]

I guess we work our way through this more deliberately.....

This code is erroring on Parent_ID = rs!Parent_ID because the former value is null??

Public Function getTopParent(varid As Variant) As Long
    Dim rs As DAO.Recordset
    Dim Parent_ID As Variant
    Dim strSql As String
    strSql = "Select Parent_ID from t_E2E WHERE Parent_ID = " & varid
    Set rs = CurrentDb.OpenRecordset(strSql)
    Parent_ID = rs!Parent_ID
    If IsNull(Parent_ID) Then
        getTopParent = varid
     Exit Function
    Else
        getTopParent = getTopParent(Parent_ID)
  End If
End Function

The current failling version is attached. It fails when running the query.

 

[SkipVought]

Don't know what your table structure is but to get a parent, you need to supply a child value.

However, in my experience with multi-level bills-of-material, I would start with a child to get a where-used output. One child part might yield many used-on parts.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

How about this...

Public Function getParent(varID As Variant) As Long
  Dim rs As DAO.Recordset
  Dim parentID As Variant
  Dim strSql As String
  strSql = "Select parentID from tblEquipment WHERE ID = " & varID
  Set rs = CurrentDb.OpenRecordset(strSql)
  
  If IsNull(rs!parentID) Then
    getParent = varID
  Else
    getParent = rs!parentID
  End If

  Set rs = Nothimg
End Function

Since you want an accumulated result that looks like a URL, showing the full path from the given child to top parent, the GetParent() function can't be recursive, IMO.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Damian3215]

The Access database is attached for your review and comments.
My Child is E2E_ID. Every child has a parent with the exception of the top children.
My Parent is Parent_ID
My Sort is Sort

Ideally the Parent_ID with the blank value and a sort value of 1 is the first item on the query and everything flows from there.

The TopParent, Parent and Level appear to be working now. I don't know how to implement the GetPath() code in the query. I'm still stuck on how to sort this list within a treeview style format.