Get AutoNumber After Insert

[Austral]

Hi,

I'm doing an INSERT into a Access Jet table in a VB 6.0 program and I need the AutoNumber value from the record just inserted so I can later do an update. This will be a MultiUser environment so I don't want to just read the last record I just inserted in case someone else inserted one after the one I just inserted and I then get the wrong one.

Is there a way to get the AutoNumber for the record I just inserted into a table without reading the last record? Here is my code.

Dim sQuery As string

sQuery = "INSERT INTO tblLogon " _
& "(strLogonId, LogonTime)" _
& VALUES('" & txtLogonId & "'," _
& "'" & Now() & "')"
cn.Execute sQuery

I also have the following 2 fields in the table and I want to update the LogOffTime field later but I don't want to wait and just do the Insert at the end so I will have to do an update.

lngAutoId - the autonumber
LogOffTime - another field

I also need to capture the lngAutoId value after the insert so I can later use it's value as a foreign key for an insert into another table.

Does anyone know how to get the AutoNumber after an insert?

Thanks,

Austral

 

[VBOldTimer]

Use

Select @@Identity

 

[Austral]

Ok, I'll try that. Thanks for your advice.

Austral

 

[Austral]

Hi VBOldTimer,

I got it working. Thanks. One more question if you don't mind!

If I'm in a multi-user environment will it still always get the correct AutoNumber from the record I just inserted or is there a possibility that, if someone else inserted another record before I issue my SELECT @@Identity would I then get the AutoNumber for that record instead of mine.

Thanks,

Austral

 

[johnwm]

I believe that @@Identity will be correct if you don't do any intervening db processes on that particular connection


________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[harebrain]

This is an oft-discussed topic, including a lengthy and worthy exchange last week. See thread thread222-524989 and do a search for others.