Add three new records

[HelpMe!]

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

 

[Steve Schapel]

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.