how to..Return all Children from a recursive table (1 Viewer)

[cursedeye]

I have a table set up like this

ClassID (pk)
ClassName
ParentClassID

And of course another like this

ItemID (PK)
ClassID (FK)
ItemName




for example,
ClassID ClassName ParentClassID
1 .........Vegetable.......null
2 ......... Meat............ null
3 ......... Drink............ null
4 ......... Pork ............. 2
5 ......... Coke ............ 3
6 ........ dietCoke......... 5
7 ...... springwater ...... 3


ItemID ClassID ItemName
1 ......... 3 ......... A
2 ......... 5 ......... B
3 ......... 6 ......... c
4 ......... 7 ......... D


Now I'm trying to add a search function to my form.

When I search for "drink" I want it to return all the items belong to "drink", so in this case it would be A,B,C,D, but the code I'm using (pleas see the quote below) only returns me "A" because it only puts "ClassID=3" to the filter.

Private Sub Class_AfterUpdate()
Call CheckFilter
End Sub

Private Sub CheckFilter()
Dim strFilter As String, strOldFilter As String

strOldFilter = Me.Filter
'Class - Numeric
If Me!CLass > "" Then _
strFilter = strFilter & _
" AND ([Class ID]=" & _
Me!CLass & ")"

'Debug.Print ".Filter = '" & strOldFilter & "' - ";
'Debug.Print "strFilter = '" & strFilter & " '"
'Tidy up results and apply IF NECESSARY
If strFilter > "" Then strFilter = Mid(strFilter, 6)
If strFilter <> strOldFilter Then
Me.Filter = strFilter
Me.FilterOn = (strFilter > "")
End If
End Sub

Any suggestions?

 

[MarkK]

Your tables are not different enough to justify keeping them both. Consider that if you introduce a type field into one table, you can eliminate the second table. This is more simple and more flexible.

tItem
ItemID (PK)
ParentItemID (Same-Table FK)
ItemType
ItemName

 

[cursedeye]

Your tables are not different enough to justify keeping them both. Consider that if you introduce a type field into one table, you can eliminate the second table. This is more simple and more flexible.

tItem
ItemID (PK)
ParentItemID (Same-Table FK)
ItemType
ItemName

I need both tables for other purposes though.

Even if I merged them into one table like the one you showed, it still is a recursive table. When you filter a certain ItemID, it only returns it's corresponding ItemName but not all items fall in to this parent item.

 

[MarkK]

To find the descendents of a particular node in a recursive table you need to use recursion.

You need a routine that receives a node and lists that nodes children and for each child you call that same routine...

Sub CreateNodes(nParent as node)
[COLOR="Green"]  'open a recordset of children of the given node nParent[/COLOR]
  dim rst as dao.recordset
  set rst = currentdb.openrecordset( _
    "SELECT ItemID, ItemName, ItemType FROM tItem WHERE ParentItemID = " & nParent.tag)
  with rst 
    do while not .eof
      dim nChild as node
[COLOR="Green"]      'create a node for each child[/COLOR]
      set nChild = Tree.nodes.add(nParent.key, tvwChild, nParent.key & "\" & !ItemID, !ItemName)
      nChild.tag = !ItemID
[COLOR="Green"]      'special handling for !ItemType items[/COLOR]
      MsgBox "Easier than checking two tables!", vbInformation
[COLOR="Green"]      'recursion: create the children of each child[/COLOR]
      CreateNodes nChild
      .movenext
    loop
    .close
  end with
end sub

Observe that with your two table solution you need to check both tables at each loop to be sure you've found all the children!