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

Senin, 23 November 2015

Re: [MS_AccessPros] Primary key value for new record

Duh. That's what happens when I try to reply from my iPad!

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 9:42 AM, Steve thaw5@suddenlink.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

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