How to insert into two different tables that are linked together in Access

Question

I have two tables like this

First Tasble

colID | Col1 | Col..

Second table

ColID | ColIDFirstTable | Col..

I want that when I click a button first to insert in First table from my form, and after inserting in first table to take the ID of first table that is primary key and to insert in the second table in second column. Something like this

 CurrentDb.Execute " INSERT INTO FirstTable(Col2,vol3)" VALUES  ('" & Txt1.Value & "', '" &Txt2.Value & "')"

  CurrentDb.Execute " INSERT INTO SecondTabkle(Col2,vol3)" VALUES  ('" & IDFirstTbale& "', '" &Txt5.Value & "')"

But I Don't know how to take the ID from First Table and to insert in second Table..

Thanx in advance

please have look at this question https://stackoverflow.com/questions/8533283/how-do-we-get-last-inserted-record-in-msaccess-query — jbud, Nov 19 '20 at 10:22
and this https://stackoverflow.com/questions/5942781/select-identity-not-scoped-by-db-object — jbud, Nov 19 '20 at 10:25

score 0 · Accepted Answer · answered Nov 19 '20 at 10:37

Using the examples I sent in the comments, your code can look like this:

Dim db1 As DAO.Database
Dim id1 As Long

Set db1 = CurrentDb

db1.Execute "INSERT INTO FirstTable(Col2,vol3) VALUES ('" & Txt1.Value & "', '" & Txt2.Value & "')", dbFailOnError

id1 = db1.OpenRecordset("SELECT @@IDENTITY")(0)

db1.Execute "INSERT INTO SecondTable(Col2,vol3) VALUES (" & id1 & ", '" & Txt5.Value & "')", dbFailOnError

How to insert into two different tables that are linked together in Access

1 Answers1