Access: Get newly created auto number in DAO

Question

I have a code in 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 (the first entry).

If you include the autonumber in your recordset then can you read the value after calling Update ? — Tim Williams, Jan 12 '12 at 17:33
The record set is pointing at the lnked table which, in this case includes the autonumber field ([AuditTrailID]). After the rs.update command it returns 1 always. — Rick, Jan 12 '12 at 17:55
As a last resort you could query the updated table for the Id, using the record values you just inserted. I'm sure that's what you're trying to avoid doing though... — Tim Williams, Jan 12 '12 at 18:40
Just out of curiosity is this the exact code you're using? I ask because you'll get an error if you don't use `RecordsetOptionEnum.dbSeeChanges` option with a SQL table that has an Indentity field. If it is the exact same code that would suggest either, `AuditTrail` isn't a SQL table perhaps its `dbo_AuditTrail`. or it doesn't have an Indentity Column — Conrad Frix, Jan 12 '12 at 19:37
AuditTrail IS a linked table (in Access) to SQL Server 2008 table. AuditTrailID is set to Identity Specification. I have't modified the code just left out the variable declarations. Where exactly do I add `RecordsetOptionEnum.dbSeeChanges`? — Rick, Jan 12 '12 at 20:01
@Rick see my updated code example for where to add the ``dbSeeChanges`` option. — Rachel Hettinger, Jan 12 '12 at 21:07
@Rick hmm I was going to ask why you didn't get the error ["You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column. "](http://office.microsoft.com/en-us/access-help/HV080760974.aspx) but when I tried to reproduce it I don't get the error anymore so it shows you what I know. — Conrad Frix, Jan 12 '12 at 21:46

score 12 · Accepted Answer · edited Mar 23 '12 at 22:16

12

Set the Bookmark property equal to the LastModified property to go back to the record you just added.

Edit: As Conrad Frix noted, use the dbSeeChanges option 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

edited Mar 23 '12 at 22:16

Doug Porter

7,721
4
40
55

answered Jan 12 '12 at 17:45

Rachel Hettinger

7,927
2
21
31

@RachelHettinger I tested it and it worked fine for me. So it does work for SQL tables even if the OP is having trouble. – Conrad Frix Jan 12 '12 at 19:34
@RachelHettinger I get the same error message. with dbSeeChanges option set. – Rick Jan 13 '12 at 18:18
It works and we all used this for years with SQL server. The issue likly here is what is the pk autonumber? In the example code we using "ID", but perhaps your autonumber PK is differnt, somthing like "AuditTrailID" or whatever. – Albert D. Kallal Jan 14 '12 at 17:53
My autonumber primary key field is AuditTrailID and I am adjusting the name accordingly, otherwise I would of gotten a "member is not part of the collection" error message – Rick Jan 17 '12 at 13:30
1

Found the problem! I had created the primary key with datatype bigint. Access apparently doesn't recognize big int. Noticed this when I went into the table design [in Access] and saw the field with text datatype. I changed it to int, which should be fine. Thanks! – Rick Jan 18 '12 at 13:10

score 0 · Answer 2 · answered Jan 12 '12 at 22:12

0

If it is a SQL Server database you are inserting into, would not a trigger on the database be a better solution.

answered Jan 12 '12 at 22:12

ChrisPadgham

860
5
4

Never really setup a trigger. But I still would need some info from the program to fill the table. – Rick Jan 13 '12 at 14:48

Access: Get newly created auto number in DAO

2 Answers2

Linked