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

Selasa, 24 November 2015

Re: [MS_AccessPros] Primary key value for new record

Thank you Graham. Got it to work, appreciate the advice.

Steve

On 11/23/2015 12:35 PM, 'Graham Mandeno' graham@mandeno.com [MS_Access_Professionals] 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