What is wrong with this code?

[Psycholicon]

Hi, I have a command button that has to do several things at once. All of the code works out well except for one DELETE statement. It runs without error, but won't delete the records. The command button is on the form ReleaseDCN, and the control referred to is a drop-down list box. I only want it to delete from the table Drawing Link, but part of the criteria comes from a relation to another table. When I copy and paste this into a select query it works fine. The only difference there is that in the query it says explicit =Deletion at the end, but in VB I made it a string to clean things up (I thought). Please tell me what I'm doing wrong.

strField = "Deletion"
strSQL = "DELETE [Drawing Link].* FROM [Drawing Link] INNER JOIN [DCN Details] ON [Drawing Link].[Drawing Number] = [DCN Details].[Drawing Number] WHERE ((([DCN Details].[DCN Number]) = [Forms]![ReleaseDCN]![ReleasedDCN]) And (([DCN Details].[Change Type]) = '" & strField & "'))"
Debug.Print strSQL
DoCmd.RunSQL strSQL

 

[KeithG]

try the below

strSQL = "DELETE [Drawing Link].* FROM [Drawing Link] INNER JOIN [DCN Details] ON [Drawing Link].[Drawing Number] = [DCN Details].[Drawing Number] WHERE ((([DCN Details].[DCN Number]) =" & [Forms]![ReleaseDCN]![ReleasedDCN]) & " And (([DCN Details].[Change Type]) = '" & strField & "'))"

 

[Psycholicon]

No, that kind of confused it a little more. I decided to try to get right to the point and go with a delete query:

DELETE [Drawing Link].*
FROM [Drawing Link] INNER JOIN [DCN Details] ON [DCN Details].[Drawing Number]=[Drawing Link].[Drawing Number]
WHERE ((([DCN Details].[DCN Number])=[Forms]![ReleaseDCN]![ReleasedDCN]) AND (([DCN Details].[Change Type])="Deletion"));


but it says "Cannot delete from specified tables." I thought it might be something to do with relationships or whatever, so I went into the table, selected the record manually and hit Delete. Blip! Gone. If only it were so easy as that in code... Now, change DELETE to SELECT and Bingo! it comes right up with the records I need, how nice is that? Why is the SELECT working but the DELETE isn't, when there's no locks or anything on the Drawing Link?

 

[ByteMyzer]

Try this for the DELETE SQL:

DELETE D1.*
FROM [Drawing Link] AS D1
WHERE EXISTS
 (SELECT D2.[Drawing Number]
  FROM [DCN Details] AS D2
  WHERE D2.[Drawing Number] = D1.[Drawing Number]
  AND D2.[DCN Number]=[Forms]![ReleaseDCN]![ReleasedDCN]
  AND D2.[Change Type]="Deletion"
 )
;

 

[CraigDolphin]

Maybe this link will be helpful for you.

http://support.microsoft.com/default.aspx?scid=kb;en-us;240098&Product=acc20

Sometimes it helps, sometimes it doesn't. Worth a try though.

 

[Psycholicon]

Thank you very much, ByteMizer. That code worked very nicely!