Append and update query with vba

[CJ_London]

not sure why you are updating a record only to then delete it

DELETE * FROM tblAltPartWHERE ActionID=" & DeleteID & "")

you need a space before the WHERE

You could just have

Set myDb = CurrentDb
myDb.Execute ("DELETE * FROM tblAltPart WHERE AltPN='" & Me.lstActionSelectPart.Value & "'")

 

[CJ_London]

and get rid of the me. and .value

 

[mischa]

not sure why you are updating a record only to then delete it

you need a space before the WHERE

You could just have

Set myDb = CurrentDb
myDb.Execute ("DELETE * FROM tblAltPart WHERE AltPN='" & Me.lstActionSelectPart.Value & "'")

The reason I'm updating one table and deleting the second is due to the fact that in tblAltPart, the ActionID has to be removed, and in tblActionsTaken the action has to be removed completely. Therefore I made two seperate sql's. Now I see that I accidentally used one table in both codes. It should have been:

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

 

[MarvinM]

Mischa -

For this type of situation, I use a checkbox instead of a combo box. That will give you a boolean value for your Yes/No field. Only drawback is that you need a default value (usually "No" or unchecked) and you cannot allow a Null, or undecided, option.

 

[mischa]

The Update query works fine but the delete sequence doesn't work properly.
Could you tell me what I am doing wrong?

The intention of the code is to delete the selected action from the Action table and remove the link in tblSubComponent.

            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

 

[MarvinM]

Mischa,

The first thing that jumps out at me is ActionID. Is that a Primary Key or required field? If so, you won't be able to change it to null.
Second thing, and probably the problem, your EXECUTE statement needs a semicolon at the end. Try this:

myDb.Execute "UPDATE tblSubComponent Set ActionID=NULL WHERE SubComponentPN='" & Me.lstActionSelectPart.Value & "';"

HTH

_________________
Thanks,
Marvin M
Windows 7 Professional, MS Access 2007/2010
Windows 8 Professional, MS Access 2013
------------------------------------------------------------------------------------------------
If my post has helped you, please click the scales or click the 'Thumbs up'. Thanks!
------------------------------------------------------------------------------------------------