Two Tables Insert Into with Select @@ Identity

[ecwhite]

Hello,

I need help with the sytax to insert into two tables at the same time with a
select @@ identity statement. I have the step one of the clause working but
need help getting the autoincrement Id and then inserting into the second
table e.g

INSERT INTO table1 ( old_ID, rec_nbr, pin_nbr, birth_date)
VALUES
(Forms!frm1!txt_old_ID,Forms!frm1!txt_rec_nbr,Forms!frm1!txt_pin_nbr,Forms!frm1!birth_date)

I need to Select @@ Identity and then insert it as the unique Id for table2

INSERT INTO table2 ( auto_incrementid,field2, field3, field4)
VALUES
(auto_incrementid,Forms!frm1!field2,Forms!frm1!field3,Forms!frm1!field4);

How do I abort if table1 insert succeds and for some reason table2 insert
fails?


Thanks,
ecwhite.

 

[ecwhite]

Sorry, I should haVe said that i am using MS Access. sorry for that and I
look forward to your helping me with this problem.

Thanks ecwhite.

 

[ecwhite]

My front end is MS Access and the backend in MYSQL. I have the tables in MS
Access as links. I will start applying what you have shown me so far but let
me know please if this little bit of information changes anything.

Thank you very much for your help.