Return ID for newly appended record

[Dmak]

How can I return the ID for a newly appended record without using a LastRecord type function? I'm not even aware of exactly how this is done yet.

I feel if I request the ID of the last record added it could cause problems later down the line with multiple users and I would like something robust.

I have a complex form at the moment but I'd like to have a simple one that can add records to a number of tables and relate them accordingly.

There is in fact 5 tables I'd like to add data to in one go!

Any help much appreciated, thank you!

 

[John Big Booty]

If your ID is a sequential number you could use the DMax() function using the UserID as Criteria.

However if your ID is being assigned by the AutoNumber function, you do need to be aware that it can misbehave which might cause you trouble down the track.

 

[Dmak]

If your ID is a sequential number you could use the DMax() function using the UserID as Criteria.

However if your ID is being assigned by the AutoNumber function, you do need to be aware that it can misbehave which might cause you trouble down the track.

Ahh of course. Thanks. I've been struggling today. It's an autonumber at the moment. Should I make it a priority to change this?

 

[jdraw]

No. The autonumber is for Access' use. If you need something, you could use a Date_TimeStamp. Doing a DMAX on the date_time stamp will give you the last assigned autonumber (ID in your case),

Good luck with your project.

You may wish to read this: Better to do it now, because you will read it at some point in dealing with Access and autonumbers.
http://www.utteraccess.com/wiki/index.php/Autonumbers

 

[StarGrabber]

You may also succeed with this:

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

rst.Open "SELECT @@IDENTITY As ID", CurrentProject.AccessConnection

Dim lngMyLastAutoNumber As Long
lngMyLastAutoNumber = rst!ID

rst.Close

This method returns the last generated autonumber in your database, independently of the table in which it was created.

 

[jdraw]

Maybe in sql server??? But not in Access 2010.

This code worked for me, but I have seen references to AVOID DLast because of inconsistent results.

Sub Testautonum()
'Dim rst As ADODB.Recordset
'Set rst = New ADODB.Recordset
'rst.Open "SELECT dlast("details_id) from event_details", CurrentProject.AccessConnection
'rst.Open "SELECT @@IDENTITY As ID", CurrentProject.AccessConnection

Dim lngMyLastAutoNumber As Long
lngMyLastAutoNumber = DLast("details_id", "event_details")
msgbox " Last autonumber assigned to details_id in table Event_Details was " & lngMyLastAutoNumber
'rst.Close
End Sub

 

[StarGrabber]

Re: Maybe in sql server???

Dear jdraw, you are mistaken. It works in Access 2010!

First: test. Then: post reply.

 

[jdraw]

There are 7 tables in this database -Access 2010. Autonumbers are assigned on a per Table basis. How would your code identify with which Table the autonumber and value are associated?
Please expand on " It works in Access 2010!"
Does "works " mean doesn't give an error? If so, then it works.
But it doesn't give you the last assigned autonumber value for any specific table.

I did test your code (see attached jpg). I added the debug.print to show the value.
It gave a 0. How did you interpret that?
What part of this am I missing?

 

[Dmak]

Thanks jdraw, much appreciated.

 

[jdraw]

Glad to help. Good luck with your project.

 

[Dmak]

Just got back round to this.

Can anyone help me get the resulting value into the SQL string? The below is not working, ED it requests that the value is input manually.

'Gets CompanyID of newly appended company
Dim NewCompanyID As Long

NewCompanyID = DMax("CompanyID", "[tblCompanies]", "UserID = [TempVars]![CurrentUserID]")

'2, Add a new contact at that company
strSQL = "INSERT INTO tblContacts ( CompanyID, FirstName, LastName, Email, Tel, UserID )"
strSQL = strSQL & " VALUES ( NewCompanyID, '" & Me.[tboAddContactFirstName] & "', '" & Me.[tboAddContactLastName] & "', '" & Me.[tboAddContactEmail] & "', '" & Me.[tboAddContactTel] & "', " & [TempVars]![CurrentUserID] & "); "

 

[Dmak]

It's OK, got it!

'" & NewCompanyID & "'

 

[Pat Hartman]

If you are using the .AddNew method, you can just reference the autonumber field. If the table is linked to SQL Server et al, you can't reference the identity until AFTER the .Update