All geek questions in one place

Access: get the newly created auto number in the DAO

I have code in the DAO that connects to a linked table in SQL Server 2008. I need to get the newly created auto number on .AddNew .

 Set db = CurrentDb Set rs = db.OpenRecordset("AuditTrail") rs.AddNew rs("ActionID") = actionAdd rs("dtDateTime") = Now() rs("FormName") = frmName rs("TableName") = tblName rs("RecordID") = actionAdd rs("Comment") = Nz(comment, "") rs("UserID") = UserIDName rs("UsernamePC") = VBA.Environ("USERDOMAIN") rs("DomainPC") = VBA.Environ("USERDOMAIN") rs("ComputerNamePC") = VBA.Environ("COMPUTERNAME") rs.Update rs.Close

If I use rs("AuditTrailID") before rs.Close , it returns 1 (first record).

+4
source share
2 answers

Set the Bookmark property to LastModified to return to the record you just added.

Edit: As Conrad Frix noted, use the dbSeeChanges parameter when opening the recordset:

 Set db = CurrentDb Set rs = db.OpenRecordset(Name:="AuditTrail", Options:=dbSeeChanges) rs.AddNew rs("ActionID") = actionAdd ' ... update additional fields rs.Update rs.Bookmark = rs.LastModified Debug.Print rs("ID") rs.Close
+11
source

If this is the SQL Server database you are inserting into, is not the best run in the database.

0
source

Source: https://habr.com/ru/post/1390687/

More articles:


All Articles