Delete Queries work individually,but not from one macro

[alley]

I have 3 files.. Job Headers, Job tasks, and Job workers, one to many on the last two from the header file, not cascaded.<br><br>The first query is a simple select, finding job numbers with dates less than a 'Purge Up To' parameter, then 3 delete queries matching the job numbers to delete the tasks and worker records, and finally a delete on the header file.<br><br>Individually, the deletes work, each asking for the purge to date, but if I piut them all in one macro they fail with a 'read only' error.<br><br>The chances of the user putting in the same date for each query is a big weakness, and cascading the delete from the header file is even more dangerous.<br><br>The 'read only' error doesn't make sense, unless it is referring to the files Access opens itself.<br><br>Any suggestions?<br><br>alley

 

[Robertd]

Hi Alley,<br><br>i'm not sure why you would get the error maybe a data dependency.. or record locks <br><br>if the date is common to all 3 delete queries perhaps make the date a control on a form and refer to that as a parameter form!yourform.yourcontrol in the query.<br><br>as you are deleting records from the child tables first there shouldn't be a problem.<br><br>also maybe put the queries in the code module for the form either call the queries with <br>DoCmd.OpenQuery stDocName, acNormal, acEdit<br>or<br><br>convert the SQL to a string and use<br>&nbsp;<br>DoCmd.SetWarnings (False) '<br>DoCmd.RunSQL yoursqlstatement<br>.<br>.<br>.<br>DoCmd.SetWarnings (True) '<br><br>another idea might be to create a temp table with the select query use this to identify the releted records for the delete query then drop the temp table<br><br>see ya<br>RobertD

 

[alley]

Thanks, RobertD, for your respnse.&nbsp;&nbsp;I use a select query to find all the job numbers with dates &lt; 'Purge To' date parameter.&nbsp;&nbsp;I would like to save these job nimbers and run all 3 delete queries with the&nbsp;&nbsp;same set, to avoid errors.<br><br>Maybe I should write the whole thing in code, but the delete queries would certainly be simpler.<br><br>Thanks for your response.&nbsp;&nbsp;There are very few people out there who know about delete queries, or at least talking about it.<br><br>alley

 

[tsman]

I agree somewhat with Robertd.&nbsp;&nbsp;You should use code to run the queries and not macro's.&nbsp;&nbsp;Some macro actions will allow the next one to run immediately.&nbsp;&nbsp;This can cause the header file purge to begin before the others have ended.&nbsp;&nbsp;If you use code this will not happen.<br><br>Good Luck