Hi, Just after a quick answer on something if someone can help please.
I have a recordset with an identity column called RECORD_ID etc ...
On one page I update the recordset with a new record, but then i need to retrieve the value of RECORD_ID thats been automatically assigned by SQL7 to use as an index for a write to another database, which is where I'm having some problems.
I've written some pretty nasty code to get it back i.e
raiddetails.requery() raid_id1 = (raiddetails.fields.item("record_id").value) raiddetails.MoveNext While (Not raiddetails.EOF) raid_id= (raiddetails.fields.item("record_id").value) If raid_id > raid_id1 Then Raid_id1 = raid_id End If raiddetails.MoveNext Wend raid_id = raid_id1
This code works based on the assumption that the record just allocated will have the highest number. Of course this wont work too well with multiple people potentially updating the database simultaneously.
if you are using SQL (and possibly Jet4) you can do the following
SELECT @@IDENTITY AS LASTID
Also, another way is to always have a field which references the user doing the insert, then you could do SELECT MAX(RECORD_ID) FROM TABLE WHERE USER_ID = CURRENTUSER