I have a form which saves the data into two tables. I need to catch the primary key from the first table because I save it as foreign key in the second table. I use Access Counter as the primary key so when I save my table by the record set I do not know its value. Any help?
How do you do the save? Do you use and Append query or a recordset? The only reliable way I know of is to use a recordset to do the save. When you use Addnew to add a record, as soon as you add any data to the record you should have access to the ID that was reserved for the record.
I recently found out that if you are using ADO with a SQL Server backend you need a KeySet cursor and you need to get the value of the ID after you do the Update. Before, I just added some data and the ID was created before I did the Update. I think this has to do with the database (Access versus SQL Server)