Run an action query based on information in a form

[Sarah]

I have a database that holds information regarding employees and jobs. When
an employee leaves or changes jobs, I need to be able to delete the
appropriate information. Currently I have a button on the entry form that
the user pushes and inputs the employee id number. However, since I keep a
copy of the information in an archive database for a little while, two or
four action queries run with the user needing to put in the employee number
and/or job number each time. A macro runs all the queries necessary. The
job information is in a subform, since one employee can have several jobs.

For example:
If you select the delete employee command button, the action queries run to
do the following
1) Copy jobs to archive (employee ID input required)
2) Copy employee information to archive (employee ID input required)
3) Delete jobs from active table (employee ID input required)
3) Delete employee information from active table (employee ID input
required)

What I would like to do is to have the user click on the button and have the
current employee number used by the action queries, rather than the user
needing to input the information. I know how to do this to run a report for
a specific person using VB, but I can't seem to get it to work with action
queries. Is this even possible? If it is, could someone either show me how
to do it or point me in the right direction? I have spent quite a bit of
time searching this community and the web to no end.

Thanks in advance,
Sarah

 

[KARL DEWEY]

Better than having duplicate tables, queries, forms, and reports for archived
data just add an Archived Yes/No field and ArchDate field to the table. Then
have criteria in queries for Archived - No (Criteria will be the numerial
zero.).
That way the data is available if needed.

 

[Sarah]

I have thought of that, but having the duplicate tables works better for me.
Thanks for the suggestion.