Multiple table updates in DAO

[Marcel K.]

What is best way to add a new record that requires data to
be added to two tables - where one table is header
reference and the other is detail reference with FK
Relationship between two. I have DAO unbound form
recordset - .addnew works but not certain on best approach
to write the record becuase the foreignKey has to exist,
hence the header record needs to be updated first.

Thanks for your help,

Marcel K.

 

[John Vinson]

What is best way to add a new record that requires data to
be added to two tables - where one table is header
reference and the other is detail reference with FK
Relationship between two. I have DAO unbound form
recordset - .addnew works but not certain on best approach
to write the record becuase the foreignKey has to exist,
hence the header record needs to be updated first.

Well... yes. If you want to update two tables you need two updates;
and if you want to maintain relational integrity, you must do so in a
way which maintains relational integrity!

You can open a Transaction to do this, and ensure that both records
get committed or both get rolled back - but I'd really suggest just
opening two recordsets and doing two addnews.