Delete Certain Records with VBA

[Psycholicon]

Hi, I'm looking for some help with SQL. I have two tables, one that's permanent, and one that's temporary, and I need a command button to be able to read the temporary table and delete all the records from the permanent one that share the same Drawing Number. Then I want to append all the records from the temporary into the permanent and delete the temporary. The append SQL is easy enough, however, I can't get the delete one to work, mainly because the tables aren't joined. There are similar temporary table being created all the time, so it has to be variable, and I can't seem to get the INNER JOIN part of the SQL to work. Please Help!

 

[KeithG]

Can you post your field and table names? Something like below should work

Delete * from PermTable Where DrawingID In (Select DrawingID from TempTable)

 

[Psycholicon]

Here's what I had:

strSQL = "DELETE [Drawing Log].* FROM [Drawing Log] WHERE [Drawing Number] = [" & DCN & " Log].[Drawing Number]"

DoCmd.RunSQL strSQL

and it just prompted me to fill in the parameter (ex. '07-31 Log' in an input box)

 

[Psycholicon]

Now it looks like this:

strSQL = "Delete * FROM [Drawing Log] WHERE [Drawing Number] In (SELECT [Drawing Number] FROM [" & DCN & " Log])"

DoCmd.RunSQL strSQL

but that's got a syntax error. Do I need the parentheses?

 

[Psycholicon]

Never mind, dumb mistake forgetting to define the variable. The SQL works like a dream, thanks!