Getting the New autonumber right after an update

[jase2006]

The "ID" field is an autonumber field. I am trying to get the new "ID" number right after a record is added. I manage some info online saying that you can read the field right after an recordset update and it should on the update row. However it seem to reset the position back to the first row? I can't use the @@IDENTITY or SCOPE_IDENTITY because I am using ACCESS 97. Any Suggestion is much appeciated.

*************** VBA CODE ******************

Set rst = dbs.OpenRecordset("tResourceRequest", dbOpenDynaset, dbSeeChanges)

pos = 1
With rst
.AddNew

Do While pos <= 44

If tResReq(pos) = "" Then
.Fields(pos).Value = Null
Else
.Fields(pos).Value = tResReq(pos)

End If

pos = pos + 1

Loop

.Update
strReqId = .Fields("ID")

.close
End With

*******************************

 

[missinglinq]

If you're asking how to get the ID number of the last record after it is saved, the simpliest way, I think, would be to use DMax, where your filed's name is ID:

Dim LastID As Long
LastID = DMax("[ID]", "YourTableName")


The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[jase2006]

The problem is the system allows multi access. So if another person created a record right just before the code is excuted, then the ID would be for the others person's code. I need to the ID of the record I created.

 

[missinglinq]

That is a problem, and you should have included this info in your original post. The immediate question that coems to mind is "Why do you need it?" The fact of the matter is, most people here and on every other forum I frequent will tell you that you shouldn't be using an autonumber as an ID, per se, i.e an account ID or membership ID or patient ID, but rather generate this kind of ID number yourself. Maybe somewhere else here can come up with an anser.

Good luck!

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[jase2006]

I actually need to copy records from a table to a new record on the same table. And on the Form, I have subforms link to other seperate tables. These tables uses the "ID" as Foreign Keys so I need create new record on these seperate tables with the same "ID" as the parent record. Can you post me a some VBA to generate autonumber? perferable in sequence instead of random. Thanks

 

[JerryKlmns]

Say you add a new record like

with rst
.CursorLockation=adUseServer
.CursorType=adOpenForwardOnly
.LockType= adLockOptimistic
.Source="Select * From Table1 Where 0=1"
.Open
.AddNew
.Fields(1) ="Blahblah1"
.Fields(2) ="Blahblah2"
.Fields(3) ="Blahblah3"
.Update
'Now try to find that record ID
.Close
.LockType= adLockReadOnly
.Source = "Select ID From Table1 Where " & _
"Field1 = 'Blahblah1' AND " & _
"Field2 = 'Blahblah2' AND " & _
"Field3 = 'Blahblah3'"
.Open
MsgBox .Fields(0)
.Close
End With

It looks like you use an "alternative" PK to lock down your true one!