Get new autonumber field after insert

Hello,

I have the following sal insert that I am performing and I can correctly get the new autonumber field that is assigned after the insert, so I can use that value to do inserts to child tables.  My problem is the "SELECT * FROM tblRefundDetailReporting" is taking too long on a table that has many rows.  

Is there a more efficient way about getting the new autonumber value inserted?  I'm not interesed in selecting max after insert, because I'm not sure how accurate that is.

Thanks.

Here is my code:

The rsInsert!RefundDetailID is the autonumber field.

            strSQL = "SELECT * FROM tblRefundDetailReporting"
            Set rsInsert = New ADODB.Recordset
            rsInsert.CursorLocation = adUseClient
            rsInsert.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic
            With rsInsert
                .AddNew
                !RefundID = IIf(IsNull(lngRefundID), "NULL", lngRefundID)
                !TrackingNumber = Trim(rs![Express or Ground Tracking ID])
                !ShipmentAmount = IIf(IsNull(rs![Transportation Charge Amount]), "NULL", rs![Transportation Charge Amount])
                !ShipmentDate = IIf(IsNull(dtShipmentDate), "NULL", dtShipmentDate)
                !ChargedAmount = IIf(IsNull(rs![Transportation Charge Amount]), "NULL", rs![Transportation Charge Amount])
                !CreatedDate = Now()
            End With
            rsInsert.Update
            bookmark = rsInsert.AbsolutePosition
            rsInsert.Requery
            rsInsert.AbsolutePosition = bookmark
            lngRefundDetailID = rsInsert!RefundDetailID
LVL 2
michael1174Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gplanaCommented:
Try to change "SELECT * ..." by "SELECT TOP 1 * ..."
This way you will get just first row of the table.
0
gplanaCommented:
And you are right: SELECT MAX() won't be a good idea because you can get a wrong number if someone else is inserting on the table meanwhile.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Why did you set the Cursor Location to adUseClient? Just curious, as there are some legit reasons to do so, but in general it's not something you typically do with Access.

Issue the Update command and then immediately check the value of your AN field. For example:

Dim rst As New ADODB.Recordset

rst.CursorLocation = adUseClient
rst.Open "SELECT * FROM Table1", CurrentProject.Connection, adOpenStatic, adLockOptimistic

With rst
  .AddNew
  rst("txt1") = "tttsss"
  .Update
  '/ Field named "ID" is my AutoNumber field in Table1
  MsgBox rst("ID")
End With

Open in new window

The above code sample will show the AutoNumber value of the record that was inserted.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Dale FyeCommented:
No points!

I do it the way LSM recommended.  It avoids potential conflicts in a multi user environment.
0
gplanaCommented:
Sorry, can you explain this please? I think my solution doesn't have any multi user environment conflict too.

Thanks.
0
Dale FyeCommented:
The method that LSM recommends, is:

After the new record is inserted into the table, but before the recordset is closed, you simply refer to the autonumber field with either:

rsInsert("RefundDetialID")

or

rsInsert!RefundDetailID

You did this properly, but you don't need the three lines bolded below, delete those lines and you should be good to go.

rsInsert.Update
bookmark = rsInsert.AbsolutePosition
rsInsert.Requery
rsInsert.AbsolutePosition = bookmark

lngRefundDetailID = rsInsert!RefundDetailID
0
gplanaCommented:
Ok, I didn't know this way. Thank you.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Sorry, had to go help the kids with some renovations ...

Your full code segment should look like this:
strSQL = "SELECT * FROM tblRefundDetailReporting"
            Set rsInsert = New ADODB.Recordset
            rsInsert.CursorLocation = adUseClient
            rsInsert.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic
            
			With rsInsert
                .AddNew
                !RefundID = IIf(IsNull(lngRefundID), "NULL", lngRefundID)
                !TrackingNumber = Trim(rs![Express or Ground Tracking ID])
                !ShipmentAmount = IIf(IsNull(rs![Transportation Charge Amount]), "NULL", rs![Transportation Charge Amount])
                !ShipmentDate = IIf(IsNull(dtShipmentDate), "NULL", dtShipmentDate)
                !ChargedAmount = IIf(IsNull(rs![Transportation Charge Amount]), "NULL", rs![Transportation Charge Amount])
                !CreatedDate = Now()
            End With
			
            rsInsert.Update
            lngRefundDetailID = rsInsert!RefundDetailID

Open in new window


On a side note, if you do NOT use a Clientside cursor, you can refer to that value as soon as you issue the AddNew. For example:

strSQL = "SELECT * FROM tblRefundDetailReporting"
Set rsInsert = New ADODB.Recordset
rsInsert.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic
           
With rsInsert
     .AddNew
     Msgbox rst("RefundDetailID")

The Msgbox would show you the new Autonumber value ...
0
Dale FyeCommented:
Just a note of caution,  LSM is correct in his statement:

"On a side note, if you do NOT use a Clientside cursor, you can refer to that value as soon as you issue the AddNew"

as long as your data is in an Access database.  But if you are using SQL Server, and I would assume most of the other "Enterprise level RDMS", the autonumber will not be generated until the record is written.  Took me a while to figure this out the first time I used SQL as a back end.
0
Armen Stein - Microsoft Access MVP since 2006PresidentCommented:
We don't use the technique of grabbing the new key value inside the .Add.  Instead, we use
rec.Bookmark = rec.LastModified after the .Update.  Then you are reliably pointed at the new record, even in a multi-user environment, and can retrieve the key.

The nice thing is that this works with both Access and SQL Server back-ends.

See slide 10 in Best of Both Worlds at www.JStreetTech.com/downloads.

Cheers,
Armen Stein
0
michael1174Author Commented:
Thanks everyone, LSM gave me excellent code and gplana recommended the "SELECT TOP 1 *" for a large table.

Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.