ACCESS FEED: Re: [MS_AccessPros] Primary key value for new record

Senin, 23 November 2015

Re: [MS_AccessPros] Primary key value for new record

Good info, Graham. I knew there was a way to do it, but had no way to look it up. I knew using .AddNew (and .UPDATE) would let you grab the AutoNumber value. Note that you have to be careful to use the same database object that you used to do the INSERT.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Nov 23, 2015, at 11:35 AM, 'Graham Mandeno' graham@mandeno.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hi Steve

Yes – .Update, not .Save

However, there is another way that won't involve changing any of your existing code. I imagine you already have something like this:

Dim db as DAO.Database
Set db = CurrentDb
db.Execute "INSERT INTO MyTable (Field1, Field2…) VALUES (Value1, Value2…);", dbFailOnError

Simply add this:

LastID = db.OpenRecordset("SELECT @@IDENTITY")(0)

It will return the last autonumber value created on that Database instance.

Best wishes,
Graham Mandeno [Access MVP since 1996]

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Tuesday, 24 November 2015 04:42
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Primary key value for new record

Thank you John. I have stumbled around a good bit in my attempts to adapt the code to my particular situation, eventually got it to work. Seems that ".Save" should be ".Update", right?

Steve

On 11/23/2015 8:16 AM, John Viescas JohnV@msn.com [MS_Access_Professionals] wrote:

Ooops.

Should be:

With rst

😃

John

On Nov 23, 2015, at 07:15, John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Steve-

I think there's a property of the form's recordset you can grab, but I'm not at home right now where it would be easier to look up.

If you're inserting only one record, using AddNew on a recordset is a bit more coding work, but runs just as fast as a single row Insert.

Dim rst As DAO.Recordset, lngNum As Long

Set rst = Me.Recordset
With Me.Recordset
.AddNew
!Field1 = Me.Field1
' Save the generated AutoNumber
lngNum = .PrimaryKey
' ..... Do rest of fields
.Save
End With

John Viescas

Sent from my iPad

On Nov 23, 2015, at 05:31, Steve thaw5@suddenlink.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
I do an INSERT INTO with a list of field names and a list of values. My first attempt to get the AutoNumber value is to make a recordset of the table, do a MoveLast and then retrieve the AutoNumber value with the recordset Fields method. Got that working after several false starts. But I see what you're suggesting and I'll try it. Is one method preferred over the other for any reason? Thank you.

Steve
On 11/22/2015 8:00 PM, John Viescas JohnV@msn.com [MS_Access_Professionals] wrote:

Steve-

What is the code you're using to do the insert? If you use a recordset and the AddNew method, you can grab the new Autonumber value as soon as you set the value of the first field.

John Viescas

Sent from my iPad

On Nov 22, 2015, at 13:58, Steve thaw5@suddenlink.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I have a form procedure triggered by a command button which inserts a
new record into a table. The primary key for that table is an
AutoNumber. Later in the same procedure I need the value of the new
record's key. How can I get the value of the primary key? Thanks.

Steve

------------------------------------
Posted by: Steve <thaw5@suddenlink.net>
------------------------------------

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

Yahoo Groups Links