Determine Autonumber of Record Inserted 1

[wtmckown]

In a multiuser environment what is the best method to find out what autonumber was assigned to a record that you just inserted into a table? I have added a date field to a record and insert the current time/date (from Now() function) and then query back on the table using the insert date/time as criteria. This will not work however when inserting records in a code loop because the now() function only has precision to the second and many records can be inserted within one second. The only thing that I have come up with is to put a delay of more than one second inside the code loop. This is a backassward way of achieving the desired result because it slows code execution. Any ideas out there?

 

[JeremyNYC]

Well, I'm not sure how you're doing this now, but it can't be done using db.execute of an INSERT INTO statement, you have to AddNew to a recordset. Here's some code that I've got where I realized I needed to grab the autonumber for the new record only after writing it with an INSERT INTO statement (there's lots of line-wrap that you'll have to watch out for):

'Insert the new account record
'strSql = "INSERT INTO tblAccount (BankID, AccountName, Status, EmployeeID, StatementNeeded, " _
& " FullStatus, unitID, StndCmplx, BankAccountNumber, GLNumber, LastStatusChangeDate) " _
& " VALUES (14, 'NOT ASSIGNED: " & rst("Account&quot & "', 'Open', " & 2 & ", Yes, 1, 3, 1, '" _
& rst("Account&quot & "', '" & rst("Account&quot & "', Now())"
'(Bank 14 is "Not Assigned".)
'Call db.Execute(strSql, dbFailOnError)
strSql = "SELECT AccountID, BankID, AccountName, Status, EmployeeID, StatementNeeded, " _
& " FullStatus, unitID, StndCmplx, BankAccountNumber, GLNumber, LastStatusChangeDate " _
& " FROM tblAccount"
Set rst2 = db.OpenRecordset(strSql, dbOpenDynaset, dbAppendOnly)
With rst2
.AddNew
!BankID = 14 'NOT ASSIGNED
!AccountName = "NOT ASSIGNED: " & rst("Account&quot
!Status = "Open"
!EmployeeID = 2
!StatementNeeded = True
!FullStatus = 3
!UnitID = 1
!StndCmplx = 1
!BankAccountNumber = rst("Account&quot
!GLNumber = rst("Account&quot
!LastStatusChangeDate = Now()
lngAccountID = !AccountID
.Update
End With

Note the dbAppendOnly parameter--this makes it so that you don't pull over the whole table when you're trying to add a record.

Hope this helps.

Jeremy


==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[wtmckown]

Thanks Jeremy. This is the proper way to accomplish this task. I used the ADO model instead but the concept is the same. Now I need to cull back through code I've written the wrong way for the past ?years and make revisions.

 

[wtmckown]

An important distinction between DAO and ADO (found out through experimentation) is that ADO does not support the dbAppendOnly parameter to prevent pulling over the entire table. A workaround is as simple as adding a where clause to the query that specifies a negative value as criteria on the autonumber field. Thanks again.

 

[cmmrfrds]

You can get the autonumber using the @@IDENTITY variable just like in sql server this was implemented with Access 2000.

see thread701-416720

 

[markgrizzle]

Just be aware when using @@Identity, it returns the last global AutoNumber inserted. In a multiuser environment, this may or not be the row you intended.

Here's how's I ado do it nowadays... credits to RoyVidar

cnn.execute "Insert Into..."
set rs = cnn.execute("select @@identity")
if not rs.eof then lng = rs.fields(0)

On a related note, this isn't working when I load parent and child tables in a transaction... I'm trying to nested transactions now but get an error.