ACCESS FEED: RE: [MS_AccessPros] Setting variable = ID for inserted record with Currentdb.Execute

Rabu, 26 Oktober 2011

RE: [MS_AccessPros] Setting variable = ID for inserted record with Currentdb.Execute

Connie-

I could swear there's a database or other property that will tell you the value
of the last key added, but I can't come up with it at the moment. The surefire
way is to use a Recordset (if we're talking about an AutoNumber field).

Dim db As DAO.Database, rst As DAO.Recordset, lngID As Long

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM MyTable", dbOpenDynaset,
dbAppendOnly)
' Start a new record
rst.AddNew
' Set some field to "dirty" the record
rst!SomeField = "Gorp"
' Now grab the new AutoNumber value
lngID = rst!ID
' Save the record
rst.Update

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
Sent: Wednesday, October 26, 2011 5:43 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Setting variable = ID for inserted record with
Currentdb.Execute

I have inserted a new record using CurrentDb.Execute INSERT.
I would like to create a variable with the ID for that new record so that I can
open the form to that record. I've done it with recordsets but how would I do
in this situation?

Thanks!
Connie

------------------------------------

Yahoo! Groups Links