update and delete query with vba

[mischa]

Two things I would like to do with this vba code:

1. Update a value from one table and save the ID as DeleteID
2. Use the DeleteID in the where clause of a delete query

I got the following code but it doesnt work as intended.
Could someone please help me with this:

            Dim myDb As Database
            Dim rst As Recordset
            Set myDb = CurrentDb
            myDb.Execute ("UPDATE tblSubComponent Set ActionID=NULL WHERE SubComponentPN='" & Me.lstActionSelectPart.Value & "'")
            Set rst = myDb.OpenRecordset("SELECT  @@Identity as DeleteID")
            DeleteID = rst.Fields(0)
            myDb.Execute ("DELETE * FROM tblSubComponent WHERE ActionID=" & DeleteID & "")
            Set rst = Nothing
            Set myDb = Nothing

Thanks in advance !!

 

[pr2-eugin]

You need to be descriptive - when you say,

I got the following code but it doesnt work as intended.

What is your intention? What is happening?

Why would the following not work?

    Dim myDb As Database
    Dim rst As Recordset
    
    Set myDb = CurrentDb
    myDb.Execute ("UPDATE tblSubComponent Set ActionID = NULL WHERE SubComponentPN = '" & Me.lstActionSelectPart.Value & "'")
    
    myDb.Execute ("DELETE * FROM tblSubComponent WHERE SubComponentPN = '" & Me.lstActionSelectPart.Value & "'")
[COLOR=Green]    'Or Even
    'myDb.Execute ("DELETE * FROM tblSubComponent WHERE ActionID Is NULL")[/COLOR]
    Set rst = Nothing
    Set myDb = Nothing

 

[CJ_London]

See this link where OP has already asked this question

http://www.access-programmers.co.uk/forums/showthread.php?p=1315519#post1315519

I suspect the reason is the same - see post #23

Now I see that I accidentally used one table in both codes. It should have been:


Code:
Dim myDb As Database Dim rst As Recordset Set myDb = CurrentDb myDb.Execute ("UPDATE tblAltPart Set ActionID=NULL WHERE AltPN='" & lstActionSelectPart & "'") Set rst = myDb.OpenRecordset("SELECT @@Identity as DeleteID") DeleteID = rst.Fields(0) myDb.Execute ("DELETE * FROM tblActionsTaken WHERE ActionID=" & DeleteID & "") Set rst = Nothing Set myDb = Nothing

 

[mischa]

Thanks for the answers!
The code does update tblSubComponent but it doesn't delete the row from the other table which requires the ActionID in the where clause.

What is your intention? What is happening?

I want the code to update the value in [tblSubComponent]![ActionID] to NULL (which it does). After that I would like to delete the value from tblActionsTaken. The value which should be deleted is the ActionID which just has been removed from tblSubComponent.

At the moment I use the following:

            Dim myDb As Database
            Dim rst As Recordset
            Set myDb = CurrentDb
            myDb.Execute ("UPDATE tblSubComponent Set ActionID=NULL WHERE SubComponentPN='" & Me.lstActionSelectPart.Value & "'")
            Set rst = myDb.OpenRecordset("SELECT  @@Identity as DeleteID")
            DeleteID = rst.Fields(0)
            myDb.Execute ("DELETE * FROM tblActionsTaken WHERE ActionID=" & DeleteID & "")
            Set rst = Nothing
            Set myDb = Nothing

 

[pr2-eugin]

The IDENTITY returned would be the PK of the table where the previous action was performed. So unless your second table has a ActionID exactly the same as the one you just uodated in another table will not work. Did you try my code?

 

[mischa]

pr2-eugin thanks for you reply!

The IDENTITY returned would be the PK of the table where the previous action was performed. So unless your second table has a ActionID exactly the same as the one you just uodated in another table will not work. Did you try my code?

    Dim myDb As Database
    Dim rst As Recordset
    
    Set myDb = CurrentDb
    myDb.Execute ("UPDATE tblSubComponent Set ActionID = NULL WHERE SubComponentPN = '" & Me.lstActionSelectPart.Value & "'")
    
    myDb.Execute ("DELETE * FROM tblSubComponent WHERE SubComponentPN = '" & Me.lstActionSelectPart.Value & "'")
    'Or Even
    'myDb.Execute ("DELETE * FROM tblSubComponent WHERE ActionID Is NULL")
    Set rst = Nothing
    Set myDb = Nothing

No I didn't try this code because I don't want to delete the entire row in tblSubComponent. In this table only the refference to the ActionID has to be deleted. tblActionsTaken contains the detailed information about the acitons, which should be deleted.

The IDENTITY returned would be the PK of the table where the previous action was performed.

ActionID is the PK in tblActionstaken and the FK in tblSubComponents.
IDENTITY could work but I don't know how to use it in this case.

 

[pr2-eugin]

What are you saying? I am totally lost. I only followed the code you gave, I have no clue how your tables are set up or what exactly that you are trying to do.

You don't want to delete the row but only the relationship? If the tables are related and then the referential integrity is turned on, then the delete will remove the entire row. There cannot exist a Child without a parent. If it does these are called Orphaned child, which is in no way useful except claiming unnecessary space.