Tree View Control with Table as a switchboard Form

[esengul]

Hi
I have a table for each subforms object.
CatTable
===========
CatID CatName ParentID ParLvl FrmName
1 CatName1 0 0
2 CatName2 1 1 FrmCatName2
3 CatName3 1 1
4 CatName4 3 2 FrmCatName24
============
I want to create a tree view control shows my record in this table

F.E
CatName1
CatName2 (click event opens the form as my subform)
CatName3
CatName4 (click event opens the form as my subform)

I tried it with many ways but i couldn't do it. Please let me know how i can do it. I hope i explained it very clearly, if not let me know.
Thanks in advance

 

[MajP]

That is a pretty big question, but everything you need is right here?
How to fill a Treeview control recursively in Access 2000

http://support.microsoft.com/kb/q209891/

This code should be about 99% right for you.


Private Sub Form_Load()
Const strTableName = "CatTable"
Dim db As DAO.Database
dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset(strTableName, dbOpenDynaset, dbReadOnly)
AddBranch rst:=rst, strPointerField:="ParentID", strIDField:="CatID", strTextField:="CatName",0
End Sub


'================= AddBranch Sub Procedure ======================
' Recursive Procedure to add branches to TreeView Control
'Requires:
' ActiveX Control: TreeView Control
' Name: xTree
'Parameters:
' rst: Self-referencing Recordset containing the data
' strPointerField: Name of field pointing to parent's primary key
' strIDField: Name of parent's primary key field
' strTextField: Name of field containing text to be displayed
'=============================================================
Sub AddBranch(rst As Recordset, strPointerField As String, _
strIDField As String, strTextField As String, _
Optional varReportToID As Variant)
On Error GoTo errAddBranch
Dim nodCurrent As Node, objTree As TreeView
Dim strCriteria As String
dim strText As String
dim strKey As String
Dim nodParent As Node
dim bk As String
Set objTree = Me!xTree.Object
If varReportToID = 0 Then ' Root Branch.
strCriteria = strPointerField = 0
Else ' Search for records pointing to parent.
strCriteria = BuildCriteria(strPointerField, _
rst.Fields(strPointerField).Type, "=" & varReportToID)
Set nodParent = objTree.Nodes("a" & varReportToID)
End If

' Find the first record under the top level.
rst.FindFirst strCriteria
Do Until rst.NoMatch
' Create a string with CATName.
strText = rst(strTextField)
strKey = "a" & rst(strIDField)
If Not varReportToID = 0 Then 'add new node to the parent
Set nodCurrent = objTree.Nodes.Add(nodParent, tvwChild, strKey, strText)
Else ' Add new node to the root.
Set nodCurrent = objTree.Nodes.Add(, , strKey, strText)
End If
' Save your place in the recordset so we can pass by ref for speed.
bk = rst.Bookmark
' Add node that are under this node.
AddBranch rst, strPointerField, strIDField, strTextField, rst(strIDField)
rst.Bookmark = bk ' Return to last place and continue search.
rst.FindNext strCriteria ' Find next subnode
Loop

exitAddBranch:
Exit Sub

'--------------------------Error Trapping ------
errAddBranch:
MsgBox "Can't add child: " & Err.Description, vbCritical, "AddBranch Error:"
Resume exitAddBranch
End Sub

 

[esengul]

Hi MajP
I tried to work with you code that you provided me. Thanks alot for it, bu I am having problem which is "type mismatch" for rst variable on the line :
AddBranch rst = rst, strPointerField = "ParentID", strIDField = "CategoryID", strTextField = "CategoryName", 0
thanks again

 

[PHV]

Replace this:
Sub AddBranch(rst As Recordset,
With this:
Sub AddBranch(rst As DAO.Recordset,

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[esengul]

I did and stil having the same problem. Thanks all for your help

 

[PHV]

AddBranch rst[highlight]:[/highlight]=rst, strPointerField[highlight]:[/highlight]="ParentID", strIDField[highlight]:[/highlight]="CategoryID", strTextField[highlight]:[/highlight]="CategoryName"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[esengul]

I did that too
then, i am having syntax error.
Thanks

 

[PHV]

And this ?
AddBranch rst, "ParentID", "CategoryID", "CategoryName"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[esengul]

now i am having another error message " can't add child: type mismatch"
Thanks

 

[MajP]

Not sure what PHV is trying to tell you but these are the parameters for the procedure. The error is a type mismatch not a syntax error.

(rst As Recordset, strPointerField As String, _
strIDField As String, strTextField As String, _
Optional varReportToID As Variant)

So lets check
Is your rst a DAO.recordset?
Is strPointerField a str Name of a field?
is strIDField a str Name of a field?
is strTextField a Name of a field?

That is where your problem is. The code works, you are obviously passing in parameters of the wrong type.

 

[MajP]

Can't add child means you are getting somewhere. You were able to add the parent node. If you looked at the Treeview did you see a node added?

 

[esengul]

no, it is empty.
Thanks alot

 

[PHV]

OOps, my bad:
AddBranch rst, "ParentID", "CategoryID", "CategoryName", 0

 

[MajP]

Here is a mistake
If varReportToID = 0 Then ' Root Branch.
strCriteria = strPointerField & " = 0"
Else ' Search for records pointing to parent.

Also I owe you the function BuildCriteria.

 

[esengul]

after changing what MajP told me it work great. Thanksssssssssssssssss alot guys.

 

[MajP]

I have a lot of treeview control stuff. Do you need any functionality for your TV. I have drag and drop, image loading, find record, etc procedure examples.
I assume that you figured out how to do the strCriteria without the build criteria function.
The key is very weird You have to give it a string where the first char is alphabetic. If you set the key equal to "A12" you are fine. "1A2" throws a type mismatch error even if you do
strKey = "1A2"
node.key = strKey

Use both the Key and the Tag to keep information about the record that the node is associated with. Then when you click on the node, you can determine the record and do things like pop up a form to add details to the nodes record.
If your working with large data sets, as you do the recursive call save the sort order on close. This will speed up the load next time.

 

[esengul]

one more question. how can i open the forms that i have in FrmName field in CatTable as my subform.
thanks

 

[MajP]

In the below example I save the unit ID in the key. Remember I had to put a bogus letter in front of my key, so I strip it off.

Private Sub Xtree_NodeClick(ByVal selectedNode As Object)
On Error GoTo Err_NodeClick
Dim stDocName As String
Dim stLinkCriteria As String
Dim selectedUnit As String
selectedUnit = Right(selectedNode.Key, Len(selectedNode.Key) - 1)
stDocName = "frmSelectUnits"
stLinkCriteria = "txtUICcode = '" & selectedUnit & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_NodeClick:
Exit Sub
Err_NodeClick:
MsgBox Err.Description
Resume Exit_NodeClick
End Sub

 

[esengul]

i did this way
Dim res As Integer
res = Right(Node.Key, Len(Node.Key) - 1)
If DCount("FrmName", "dbo_tblCategory", "dbo_tblCategory.CategoryID=" & res) = 0 Then
Me.mysubform.SourceObject = ""
Else
Me.mysubform.SourceObject = DLookup("FrmName", "dbo_tblCategory", "dbo_tblCategory.CategoryID = " & res)
End If

Again, THANKS ALOT for you help.

 

[MajP]

Someone showed me the easier way of
res = Right(Node.Key, Len(Node.Key) - 1)
as
res = mid(Node.Key,1)
If you leve of the second argument it goes to the end.

You got me motivated to encapsulate this into a class module with all of the other drag and drop functionality. Then you would just have to instantiate the class and set the table or query name, field names, treeview control, and starting record id.