vba 访问:在 DAO 中获取新创建的自动编号

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/8839377/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 14:55:18  来源:igfitidea点击:

Access: Get newly created auto number in DAO

ms-accessvbadao

提问by Rick

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.

我在 DAO 中有一个代码连接到 SQL Server 2008 中的链接表。我需要在.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).

如果我使用rs("AuditTrailID")before rs.Close,它返回 1 (第一个条目)。

回答by Rachel Hettinger

Set the Bookmarkproperty equal to the LastModifiedproperty to go back to the record you just added.

Bookmark属性设置为等于LastModified返回到您刚刚添加的记录的属性。

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

编辑:正如 Conrad Frix 所指出的,dbSeeChanges在打开记录集时使用该选项:

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

回答by ChrisPadgham

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

如果它是您要插入的 SQL Server 数据库,那么数据库上的触发器不是更好的解决方案。