Using the same ID in 3 Access tables. 1

[WeyHey]

I have 3 tables in an access database. The main database pers_details has an autonumber ID that is my main Unique ID. I want to automatically populate this ID number in to 2 other tables NATAuditChecks and NARAdditional when I create a new record in a Form.

I use a query to pull the recordset which works if I only use two tables. I have adjusted the qry for 3 tables but I cannot update the recordset.

The query being used is below.

SELECT Pers_details.*, [NAR additional].*, NATAuditChecks.*, Pers_details.[Area/Qual code]
FROM Pers_details LEFT JOIN ([NAR additional] LEFT JOIN NATAuditChecks ON [NAR additional].[Apex ID] = NATAuditChecks.[Apex ID]) ON Pers_details.[Apex ID] = [NAR additional].[Apex ID]
WHERE (((Pers_details.[Area/Qual code]) Like "NAR&quot);

It is a simple table setup I just want to hit add a record once and populate the ID in the other tables so that I can later edit them in other forms.

If I manually edit the tables I can edit the records by using a (dynaset with inconsistent).

Any suggestions greatly appreciated.

John.

 

[jobo123]

hi WeyHey,

If you use the After Inset event of the form driving the first table to run a make table query to make a temp table to hold the new ID number and two append queries to append a new record to the other two tables and then two update queries to update these numbers to the value of the temp table ID that should do it. All the best jobo123.