Deleting a nod 2

[hoofit]

I have a treeview control 3 levels deep and I need to delete a nod and am not sure how this is done. Essentially, I'm thinking of setting up an index using a double click event;

idx = TreeView0.SelectedItem.Index

So, to identify the the 3 levels and thus remove them, I see idx+2. I tried delete nod, nod.parent, nod.parent.parent but do not know the correct syntax. Same with DoCmd.runSQL "Delete nod.......Can someone point me north?

hoof

 

[MajP]

using the selected node
Dim objTree As TreeView
Set objTree = Me.xTree.Object
objTree.Nodes.Remove (objTree.SelectedItem.index)

or if you get and index from a node other than the selected node.
dim delIndex as long
Dim objTree As TreeView
Set objTree = Me.xTree.Object
some code to return the index to delete
delIndex = ....
objTree.Nodes.Remove (delIndex)

 

[hoofit]

'Set objTree = Me.xTree.Object' produces an error.....method or data member not found. Am I supposed to insert the name of mine? Treeview0 (zero).

hoof

 

[PHV]

I'd try this:
Set objTree = Me.Treeview0.Object

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hoofit]

Yup, I just changed it. Only the first level is being deleted, not the other 2.

 

[hoofit]

Additionally, the deletion is temporary. If I cloes the form then re-open it, it's still there. Seems like this should be a delete query removing data directly from the tables, no? Then the treeview will never see it.

hoof

 

[MajP]

Only the first level is being deleted, not the other 2.

That is impossible. If you delete a node then all levels below it get deleted. If not, you would have some sort of floating node. Please post an image of the "floating" child nodes. I have never seen it and do not believe it can be done.

Seems like this should be a delete query removing data directly from the tables, no?

Yes of course you have to delete the record from the table as well. It is not a bound control. You have to do all the linkage. If you do not remove it from the query it will just get reloaded. Normally the key of the node is the primary key for the record. When you choose a node for deletion simply get its key and the key for all child records and do a delete query on all of those records.

 

[hoofit]

MajP,

Copy paste??? How do I post an image

 

[MajP]

Are you running a vb app, or is the an activeX MSCOMCTL 6 treeview? I read up on this to be sure, and I think the two work differently. Or there may be some property that effects how child nodes delete or cascade up. I cannot replicate the cascading up of child nodes in an access application. Everything I have tried still deletes the child nodes. I have not tested this in vb. However, this should delete your child nodes.

Private Function ClearChildren(objTV As TreeView, strParentNode As String) as Boolean

On Error Goto errLabel
Dim i As Long
'Use a reference to the Parent node
With objTV.Nodes(strParentNode)
    'Loop through the children
    For i = 1 To .Children
        'Determine if the child has a sub node
        If .Child.Children > 0 Then
            'Recurse this method
            ClearChildren objTV, .Child
        End If

        'Clear the node
        objTV.Nodes.Remove .Child.Index
    Next
End With
ClearChildren = True
Exit Function
errLabel:
End Function

 

[hoofit]

I'm using the ever so challenging activeX MSCOMCTL 6 treeview and wish I had not. Still, I'm thinking that once a node is identified, coding for a delete query would resolve the issue. Something like;
On dblClick, DoCmd.runSQL "Delete * From "PM" where [PMName] = whatever node was clicked. The code you posted does not appear to clear a record from a table where this would. Then close it / open it or refresh and wah-lah, it's gone. BTW, as I step thru my code, the nodes are referred to as nod, nod.parent, nod.parent.parent. this sounds like it's going to be a 2011 project

hoof

 

[hoofit]

Happy New Year everyone,

The 2 tables I need to delete from are “PM” and “PMInterval” set up like this.I've included the text 'Tbl' for clarification

TblPM
PMName (pk)
Description

TblPMInterval
PMName (pk) - composite
PMInterval (pk) - composite
IntervalValue

I’m no expert in coding but I envision something like;

DoCmd.Run SQL “delete * from TblPM” where [PMName] = nod clicked, “delete * from TblPMInterval where [PMName] = nod clicked, TblPMInterval where [PMInterval] = nod clicked and TblPMInterval where IntervalValue=nod clicked.

 

[hoofit]

If I do this in order, the code works but I need to manually enter the selected item. Still need some refining.

DoCmd.RunSQL "Delete * from PMInterval where [PMName] = objTree.SelectedItem.Index" 'added 1/2/11


DoCmd.RunSQL "Delete * from PM where [PMName] = objTree.SelectedItem.Index" 'added 1/2/11

 

[MajP]

 I'm thinking that once a node is identified, coding for a delete query would resolve the issue. Something like;
On dblClick, DoCmd.runSQL "Delete * From "PM" where [PMName] = whatever node was clicked.

That is wishful thinking, and it would be nice, but what would ever lead you to think that? As I stated it is not a bound control. When you loaded it do you set some query as a recordsource and it loads? No, you looped your records and created a node. There is no relation to the query. Of course my code does not delete your record, I have already clearly stated. The original question was how to delete the node, not the associated record. If you want code to do that then I need to know
1)How do you associate a node with a record? Do you store the PK in the key property?
2) I only see 2 levels
PM
PMInterval
3) Get rid of the composite key in your second table. Makes it too complicated.

So one more time. When you delete a node, you have to somehow determine what record that node represents and run a delete query. As I already stated, the normal means is the the records PK is the key of the node. So you
1) Get the key of the node to delete
2) delete the associated record using a delete query and that key
3) then delete the node from the treeview.

You can envision all you want, but it is just not reality.

 

[PHV]

What about this ?

DoCmd.RunSQL "DELETE * FROM PMInterval WHERE PMName='" & objTree.SelectedItem.Index & "'"

If PMName is numeric then get rid of the single quotes

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hoofit]

Well, I'm not terribly familiar with the process as I have indicated. I did find that deleting a node is the ultimate goal but in order to do this the record itself needs to be deleted, which in turn changed my goal. Thus,I need to go after the table.

Dim rstPMS As New ADODB.Recordset
Dim rstINTERVALS As New ADODB.Recordset
Dim rstIntervalValues As New ADODB.Recordset '####### Create new recordset ######

'***********************************
'* Open the necessary recordsets *
'***********************************

On Error GoTo ErrHandler

rstPMS.Open "query1", CurrentProject.Connection
rstINTERVALS.Open "query3", CurrentProject.Connection
rstIntervalValues.Open "query4", CurrentProject.Connection

hoof

 

[MajP]

PHV,
I assume you meant Key not Index. The key can be set, the index is a numeric value from 0 to number of nodes. Highly unlikely the user has established a relation between the index and the record.

 

[MajP]

This is how I would do it in your case.
First get rid of the composite key in the second table and add an autonumber key "intervalID". That is just an added level of pain.
When you load the tree
if it is a PM node
Tag: = level1
Key: = rstPMS!PMName
if it is a PMInterval (2nd Level)
Tag: = level2
Key: = rstPMInterval!PMName & ";" & rstPMInterval!PMInterval
if it is a Value (3rd level)
Tag: = level3
key: = rstPMInterval!intervalID

So if I click on a 1st level. I check
I check the tag property to determine the level
I know the table to delete from and the record to delete using the key. Then run a second delete query to delete the intervals where the PM is equal to the PM key

If I click on a second level
Check the tag property. And determine the level.
From the key :
pmName = split(key)(0)
pmInterval = split(Key)(1)
delete all records where the pm = pmName and interval = pmInterval

If I click on 3rd level.
Check the tag to determine the level
delete from tblPMIntervals where the intervalID = the key

 

[MajP]

Hoofit,
If you want, post your tables to 4shared.com and provide the link. I will demo a working treeview with add, edit, delete capability.

 

[hoofit]

I've got to much time in mine to start over. May not be the ideal design but I know it will work. I respect your opinion but I'm too far along to change infrastructure. Probably will start another thread shortly similar to this one. PHV you've got the idea, I just need it to function without an error.

hoof

 

[hoofit]

So, in attepting to delete from the tables I have this but it results in a syntax error. Any thoughts?

DoCmd.RunSQL "DELETE * FROM PMInterval WHERE PMName='" & (Right$(nod.Key, 5)). I've ID'd the key rather than the index

hoof