get last inserted ID

[spinkung]

Hi all,

i am running some sql code that inserts records into my table from a form. I first create a record which is the header, i then want to add some records to the lines table using the unique id created in the header.

I'm sure it must be possible, can anyone help/provide an example.

Here's what i've got i just need the last line...

sql = "insert into tbl_PIR_Header (orderNo,reasonCode, customer, date,  createdBy) " & _
"values ('" & ord & "','" & rc & "','" & cus & "','" & dt & "','" & user & "')"
[COLOR="Red"]"GET THE LAST ID HERE...[/COLOR]

thanks.

 

[gemma-the-husky]

if you are looking for an autonumber, then the last id will be retrieved by

lastid = dmax("idnumber","tablename")

However, if multiple users are inputting, its just possible that you pick up ANOTHER users input, and not your own.

One way round this is to use a recordset to do the update, then you can examine the autonumber allocated by Access.

 

[spinkung]

thanks for the reply.

there is going to be multiple users at the same time so the max option is not really going to work. I thought in SQL there was a SCOPE_IDENTITY(), is there an access equivalent??

can you point me in the direction of how to make an update with a recordset and then find the number? i have worked with recordsets so i know a bit about them but not much.


many thanks.

 

[gemma-the-husky]

a few alternatives

--------------
Using a bound form, but reread the item

you could do a belt and braces, which I do sometimes

the actual chance of multiple users saving records at the same time is minuscule

so you could do a dmax to get the recordid, then readsomething else you know about the record to verify it is the correct record (eg your name, or a sequence number, to verify its the right one)

so
recordid = dmax(etc)

then
checkinfo = dlookup("theitem", "thetable","recordid = " & recordid)
if checkinfo was whatyou expected, then you DO have the correct recordid

however if you do this, and dont retrieve the correct item, your code will have ot have a way to report this, and not set up the subrecords, which you will have to do manually somehow, which is not ideal

-----------
using recordsets is more of a technique to use with unbound forms, so that when you have collected all the data you need you can then do

set rst = currentdb.openrecordset("thetable")

rst.addnew 'creates a new record

'populate the data
rst!field1 = whatever
rst!field2 = whatever
...

rst.update 'this saves it

'now having saved it you can THEN say
recordid = rst!recordid 'this is definitley the autonumber of the record you created

---------
another way of doing it is not to use autonumbers, but to manually obtain the new recordnumber, (the recommended technique is to store the next record number in a separate file, and read/write this LOCKED when you need it, which prevents two users getting the same reference number)

alternatively, you could just use a dmax directly on the table to get the last number used, but this is still technically suspect because of the dual update possibility, although in pratice, its rarely an issue - even so, if you do this to retrieve a number, and then the insert fails becuase another user has beat you to it, and used the same number, your code can just go back to the top of the loop, and try to get another number.

hope this all helps

 

[spinkung]

thank you,

i went for the recordset option. it works a treat.

 

[gemma-the-husky]

glad it worked OK