When i add a new record to my "EmployeeInfo" table, the "EmployeeID" field
adds a new autonumber. I would like to create a macro that, when i push my
"Add a New Employee" button, also adds the new "EmployeeID" to my "Probation"
table three times. Each time, i would also like the macro to enter a
different number in the "Period" field.

Example of Probation table after Macro:

EmployeeID Period
2233 1
2233 2
2233 3

HelpMe,

1. Make a new simple table (let's call it ProbabtionPeriods) with 1
Number/Integer field [Period] and 3 records: 1, 2, 3.

2. Make a query (let's call it NoProbabtion) to return the EmployeeID where
there are no related Probation records. The SQL view of such a query will
look something like this:
SELECT EmployeeInfo.EmployeeID
FROM EmployeeInfo LEFT JOIN Probation ON EmployeeInfo.EmployeeID =
Probation.EmployeeID
WHERE Probation.EmployeeID Is Null

3. Make another query, based on the table from point 1 above, and the query
from point 2 above. Make this into an Append Query to write the records to
the Probation table. The SQL view of such a query will look like this...
INSERT INTO Probation ( EmployeeID, Period )
SELECT NoProbabtion.EmployeeID, ProbationPeriods.Period
FROM NoProbation, ProbationPeriods

4. Add an OpenQuery action to your macro, to run the append query from
point 3 above.

--
Steve Schapel, Microsoft Access MVP


"HelpMe!" <(E-Mail Removed)> wrote in message
news:16FE945C-97B0-44E6-BF54-(E-Mail Removed)...
> When i add a new record to my "EmployeeInfo" table, the "EmployeeID" field
> adds a new autonumber. I would like to create a macro that, when i push my
> "Add a New Employee" button, also adds the new "EmployeeID" to my
> "Probation"
> table three times. Each time, i would also like the macro to enter a
> different number in the "Period" field.
>
> Example of Probation table after Macro:
>
> EmployeeID Period
> 2233 1
> 2233 2
> 2233 3
>
>
>