passing a parameter between 2 queries

[alant37]

I have 2 queries as follows:

DoCmd.OpenQuery "qryappendtblHelpdeskcallstoArchive", acViewNormal, acEdit
DoCmd.OpenQuery "qrydeletetblHelpdeskcallsArchived", acViewNormal, acEdit

Both queries have a parameter of Archive date which is used to first append calls less than the Archive date to another table then delete those calls from the original table.

The SQL for the 2 queries are below respectively:
PARAMETERS [Archive Date] DateTime;
INSERT INTO tblHelpdeskcallsArchive ( ID, [Date Opened], Priority, [User Name], [User Name1], Department, [Contact Number], [Call Description], [Allocated To], Email, [Date Closed], [Days Open], [Additional Coments], [Call Resolution], Category, Organisation, [Route received] )
SELECT tblHelpdeskcalls.ID, tblHelpdeskcalls.[Date Opened], tblHelpdeskcalls.Priority, tblHelpdeskcalls.[User Name], tblHelpdeskcalls.[User Name1], tblHelpdeskcalls.Department, tblHelpdeskcalls.[Contact Number], tblHelpdeskcalls.[Call Description], tblHelpdeskcalls.[Allocated To], tblHelpdeskcalls.Email, tblHelpdeskcalls.[Date Closed], tblHelpdeskcalls.[Days Open], tblHelpdeskcalls.[Additional Coments], tblHelpdeskcalls.[Call Resolution], tblHelpdeskcalls.Category, tblHelpdeskcalls.Organisation, tblHelpdeskcalls.[Route received]
FROM tblHelpdeskcalls
WHERE (((tblHelpdeskcalls.[Date Closed]) Is Not Null And (tblHelpdeskcalls.[Date Closed])<[Archive Date]));


PARAMETERS [Archive Date] DateTime;
DELETE tblHelpdeskcalls.*, tblHelpdeskcalls.[Date Closed]
FROM tblHelpdeskcalls
WHERE (((tblHelpdeskcalls.[Date Closed]) Is Not Null And (tblHelpdeskcalls.[Date Closed])<[Archive Date]));


Is there any way I can get the 2 queries to just accept one Archive Date as a parameter initially when doing the append then pass this value to the delete query. I created the 2 queries first seperately then created the code to run them, but they both are working at the moment in isolation from one another.

 

[Mile-O]

Put the date on a (hidden) form and refer to the control. That way the parameter will just take the value from the control and won't ask you for a date.

If you can automate it, then automate it.