Sometimes when inserting records programmatically you want to create a parent record first, then add records into related tables where the Foreign Key is on an autonumber field. Therefore you need to know the new primary key value for the new parent records. When using DAO against a SQL Server table, a recordset behaves slightly differently from MDB/ACCDB tables. Here’s the easiest way to get the ID of a newly inserted record.
1: Dim rs As Recordset, newID As Long
2:
3: …
4:
5: Set rs = CurrentDb.OpenRecordset("Memory", dbOpenDynaset, dbSeeChanges)
6:
7: rs.AddNew
8: …populate fields
9: rs.Update
10:
11: ' the record has been committed, but the cursor has jumped to absoluteposition 1
12: ' move to the new record and get the ID (my autonumber field is called ID)
13: rs.Move 0, rs.LastModified
14: newID = rs("ID")
15: ' now use this ID in creating related records
16:
17: …