Split a branch into nodes, reconstrucing and running a query on each = inefficient?

[cafeenman]

Anyone have a better idea how to do this?

Working demo attached. I got the whole tree/branch/node thing sorted and like it.

Tree.zip

Also, DO NOT click create tree until you look at the Editor or you will wipe out the tree in the db.

Edit: This isn't in the upload but when I started this I was more interested in getting it working than anything else. So all the generic DB stuff in frmTree has been moved to the DB module.

Private Sub PopulateParentText()
Dim RST As DAO.Recordset
Dim SQL As String
Dim nRecordcount As Long
Dim sBranch() As String
Dim sToken As String
Dim n As Long
Dim sContent As String
Dim s As String

' Outputs All content in the Branch up to the Node being edited.

' This is convoluted.  There has to be a better way but this works.

' Typical Branch: 001_001_002_001

' Find the Content of 001, 001_001, 001_001_002, and 001_001_002_001

' E.g.

' 001 = "You come to a crossroad."
' 001_001 = "You decide to turn left.
' 001_001_002 = "You are stopped by a sharply dressed man."
' 001_001_002_001 = "He offers you a deal.
' skipping ahead...
' 001_001_002_001_001_003 = "YOU DIED!!"
' (make better choices)

txtParent.Text = vbNullString

sBranch = Split(cmbParent.Text, "_") ' Create an Array of Each Token in the string.

If Not ArrayInitialized(sBranch) Then Exit Sub ' Nothing there.

sToken = sBranch(0) ' First Token is a special case that doesn't contain an underscore.

SQL = "SELECT Content FROM Branches WHERE Branch = '" & sToken & "'"

nRecordcount = OpenRST(RST, SQL, idx_Recordset_Snapshot)
If nRecordcount = 0 Then Exit Sub

s = RST.Fields("Content") ' Save the string.

For n = LBound(sBranch) + 1 To UBound(sBranch)

  sToken = sToken & "_" & sBranch(n) ' Now add in tokens one at a time separated by an underscore.

  SQL = "SELECT Content FROM Branches WHERE Branch = '" & sToken & "'"

  nRecordcount = OpenRST(RST, SQL, idx_Recordset_Snapshot)

  If nRecordcount > 0 Then 

      s = s & vbCrLf & RST.Fields("Content")

  end if
Next n

txtParent.Text = s

End Sub

[ColinE66]

You should probably normalise your database so that you have a table with ParentId and ChildID for each node, and you can construct a full path (branch) from that, by simply 'walking' down the path from parent to child, and so on.

e.g.

ParentID ChildID
1..............2
1..............3
2..............4

This would be very simple with a recursive query, but I don't know if you can do that with DAO.

[cafeenman]

I have no idea how to write a recursive query. It may or may not be possible but I wouldn't even know where to start.

I'll ask my friends at the googles.

Thanks.

PS. I'm about to upload the "final" version in the Code Bank. It's been polished a bit and has some quality of life stuff added. Everything is working now.