Avatar of Murray Brown
Murray Brown
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Access Create a new record then read it

Hi

What is the best way to create a new record in a table then get the ID number created from this?
Microsoft Access

Avatar of undefined
Last Comment
Murray Brown

8/22/2022 - Mon
mbizup

Try using a recordset:

Dim rs as DAO.Recordset
Set rs = CurrentDB.OpenRecordset("YourTable")

rs.AddNew
rs!SomeField = "Some value"
rs.Update

MsgBox "New record ID = " & rs!ID

rs.close
Set rs = nothing

Open in new window

ASKER CERTIFIED SOLUTION
mbizup

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Murray Brown

ASKER
Thanks
mbizup

You're welcome!

Just as a note, one advantage to this method as opposed to other methods (Such as DMax or DLast) is that in a multi user database, it ensures that the ID returned is that of the record that was created by the current user and not someone else.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Murray Brown

ASKER
Hi

Thanks very much. I am creating this for a multi user environment and have been looking for a method like this for around a year, so you have done me a great service.
Thanks for that!