Gladly! Critique away...
Private Sub tvCookbooks_Load()
On Error GoTo ErrorHandler
Dim db As DAO.Database
Dim rsCookbooks As DAO.Recordset
Dim rsChapters As DAO.Recordset
Dim rsRecipes As DAO.Recordset
Dim strSQL As String
Dim intDistance As Integer
' Clear the tree
Me.tvCookbooks.Nodes.Clear
Me.tvCookbooks.Nodes.Add _
Key:="Cookbooks", _
Text:="Recipes by Cookbook & Chapter"
Set db = CurrentDb()
'add the cookbooks
strSQL = "SELECT cookbookid, name " & _
"FROM t_cookbook " & _
"ORDER BY name"
Set rsCookbooks = db.OpenRecordset(strSQL)
Do Until rsCookbooks.EOF
Me.tvCookbooks.Nodes.Add _
Relative:="Cookbooks", _
Relationship:=tvwChild, _
Key:="CB" & rsCookbooks!cookbookid, _
Text:=rsCookbooks!Name
rsCookbooks.MoveNext
Loop
'add the chapters
strSQL = "SELECT cookbookchapterid, name, cookbookid " & _
"FROM t_cookbookchapter as tblChapter " & _
"ORDER BY name"
Set rsChapters = db.OpenRecordset(strSQL)
Do Until rsChapters.EOF
Me.tvCookbooks.Nodes.Add _
Relative:="CB" & rsChapters!cookbookid, _
Relationship:=tvwChild, _
Key:="CH" & rsChapters!cookbookchapterid, _
Text:=rsChapters!Name
rsChapters.MoveNext
Loop
'add the recipes using cookbookchapterid
strSQL = "SELECT t_recipe.recipeid," & _
" t_recipe.recipename," & _
" t_cookbookchapterassociation.cookbookchapterid," & _
" t_cookbookchapterassociation.distance" & _
" FROM t_recipe INNER JOIN t_cookbookchapterassociation" & _
" ON t_recipe.recipeid = t_cookbookchapterassociation.recipeid" & _
" WHERE distance = " & intDistance & _
" ORDER BY recipename"
'add recipes with index 1
intDistance = 1
Set rsRecipes = db.OpenRecordset(strSQL)
Debug.Print strSQL
Do Until rsRecipes.EOF
Me.tvCookbooks.Nodes.Add _
Relative:="CH" & rsRecipes!cookbookchapterid, _
Relationship:=tvwChild, _
Key:="R" & rsRecipes!recipeid, _
Text:=rsRecipes!recipename
rsRecipes.MoveNext
Loop
'add recipes with index 2
intDistance = 2
Set rsRecipes = db.OpenRecordset(strSQL)
Debug.Print strSQL
Do Until rsRecipes.EOF
Me.tvCookbooks.Nodes.Add _
Relative:="CH" & rsRecipes!cookbookchapterid, _
Relationship:=tvwChild, _
Key:="R" & rsRecipes!recipeid, _
Text:=rsRecipes!recipename
rsRecipes.MoveNext
Loop
'add the recipes with index 3 (tied to chapters)
intDistance = 3
Set rsRecipes = db.OpenRecordset(strSQL)
Do Until rsRecipes.EOF
Me.tvCookbooks.Nodes.Add _
Relative:="CH" & rsRecipes!cookbookchapterid, _
Relationship:=tvwChild, _
Key:="R" & rsRecipes!recipeid, _
Text:=rsRecipes!recipename
rsRecipes.MoveNext
Loop
cleanup:
On Error Resume Next
rsCookbooks.Close
Set rsCookbooks = Nothing
rsChapters.Close
Set rsChapters = Nothing
rsRecipes.Close
Set rsRecipes = Nothing
Set db = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume cleanup
End Sub