TreeView Debug Error

[oskar]

I follow this lesson "mymsaccessblog.blogspot.ca/2008/02/my-treeview-project-episode-two.html"

to learn about the TreeView Control and when I open the form in FormView I get the Debug error message "Item not found in this collection" and the line in red is highlighted on the code below.

Option Compare Database

Private Sub CreateCategoryNodes()
Dim rst As DAO.Recordset ' recordset for category data

' open the recordset for categories
Set rst = CurrentDb.TableDefs!Categories.OpenRecordset

' loop through the rows in the recordset
rst.MoveFirst
Do Until rst.EOF
Me.xProductTreeView.Nodes.Add Text:=rst!CategoryName, _
Key:="Cat=" & CStr(rst!CategoryID)
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End Sub

Am I correct to say that the error occurs because the Product table (where the rst should come from) is not set properly? Like it should be:

Set rst=db.OpenRecordset("Products", dbOpenDynaset, dbReadOnly)

 

[speakers_86]

I've never seen anyone open a recordset like that before, but that doesn't mean it won't work! The first question is: do you have a table named Categories? It seems you are trying to use the wrong table, since at the end of your post your table is called Products. FYI, in reference to the error you have, the collection is TableDefs, and the item is Categories. If you don't have a Categories table, as I suspect you don't, then the table Categories is indeed not in the TableDefs collection, as the error states.

The second question, is do objects in the TableDefs collection have an OpenRecordset
method? I don't think they do, but change Categories to Products and you should find out. I would expect the error to say property or method not defined.

The later Set statement in your post should work (the last line), except that you have not defined db. You still need

Dim db as dao.Database
Set db = CurrentDb()
'Other stuff here
Set db = Nothing

Also, you will need to improve your method of looping through the recordset. For instance, you've got rst.MoveNext, but you did not test to see if there is a next record to go to.

Also, make sure you have some error handling, though I would imagine you know that (or hope at least) .

 

[oskar]

Thank you for your comments but finally I got it, the required changes were more than I thought but now the form works. If there are any improvements I will gladly implement but pls note this is an exorcize to learn the treeview control and making the code more efficient is not the real object. There are 6 lessons in the series to learn the treeview control and will look to incorporate your comments after I complete all lessons


Private Sub CreateCategoryNodes()
Dim rst As DAO.Recordset ' recordset for category data
Const strTable = "Products"
Dim db As Database
Set db = CurrentDb

Set rst = db.OpenRecordset(strTable, dbOpenDynaset, dbReadOnly)

' open the recordset for categories
'Set rst = CurrentDb.TableDefs!Categories.OpenRecordset

' loop through the rows in the recordset
rst.MoveFirst
Do Until rst.EOF
Me.xProductTreeView.Nodes.Add Text:=rst!Category, _
Key:="Cat=" & CStr(rst!ID)
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End Sub

Private Sub CreateProductNodes()
Dim rst As DAO.Recordset ' recordset for product data

' open the recordset for products
Set rst = CurrentDb.TableDefs!Products.OpenRecordset

' loop through the rows in the recordset
rst.MoveFirst
Do Until rst.EOF
Me.xProductTreeView.Nodes.Add Relationship:=tvwChild, _
Relative:="Cat=" & CStr(rst!ID), _
Text:=rst!ProductName, Key:="Prod=" & CStr(rst!ID)
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End Sub

Private Sub Form_Open(Cancel As Integer)
CreateCategoryNodes
CreateProductNodes
End Sub