Fetching the last ID

[sheila11]

Hi all,

I am doing an insert into an Access table, and then trying to fetch the ID
of the record that was just inserted. But my code is not able to detect the
record just inserted. Could someone please help?

TIA,
Sheila

My code is:
'first insert the new record into tblEmployee
'---------------------------------------------------------------------
ssql = " INSERT INTO tblEmployee " & _
" ( " & _
" emp_first ," & _
" emp_last " & _
" ) " & _
" VALUES ('" & strFirst & "', " & _
" '" & strLast & "') "
Set objRecSet = ExecuteSQL(ssql)

'-------------------------------------------------------------------
'the previous insertion generated a new emp_id so we can now get it...
'-------------------------------------------------------------------
ssql = "SELECT emp_id " & _
"FROM tblEmployee " & _
"WHERE emp_first = '" & strFirst & "' " & _
"AND emp_last = '" & strLast & "' "
Set objRecSet = ExecuteSQL(ssql)
if (NOT objRecSet.BOF) AND (NOT objRecSet.EOF) Then
intEmpID = objRecSet("emp_id&quot '****It never gets here! ****
Response.Write "intEmpID= " & intEmpID
end if

 

[WaltLukeIII]

Set a variable equal to the value that is being inserted as it is being inserted. Then display this variable. If you are going through a loop and you just want to see the last value inserted set the variable directly before you insert and don't display until after you have finished the loop. The variable will continually be reset until the loop finishes leaving you with the last value.

Walt III
SAElukewl@netscape.net

http://www.muc.edu/~lukewl

 

[sheila11]

I am not able to understand this. Could you please show it in code?

 

[WaltLukeIII]

ssql = "INSERT INTO tblEmployee ( emp_first, emp_last) VALUES ('" & _ strFirst & "', '" & strLast & "')"
Set ObjRecSet = ExecuteSQL(ssql)

ssql = "SELECT emp_id FROM tblEmployee WHERE emp_first = '"
& strFirst & "' AND emp_last = '" & strLast & "' "
Set objRecSet = ExecuteSQL(ssql)
if NOT objRecSet.EOF Then
intEmpID = objRecSet("emp_id&quot
end if
Response.Write "intEmpID= " & intEmpID

I did not fully comprehend your problem until just now You where getting just one record with your Select statement and since that one record is the BOF then you never got any further than that. This should work just eliminate the BOF. statement.

Walt III
SAElukewl@netscape.net

http://www.muc.edu/~lukewl

 

[frOblivion]

You can also execute this statement once the record has been inserted. "SELECT Max(emp_id) FROM tblEmployee ...
If your id field is a numerical incrementing field.

Make sure to lock any other transactions so no one slips a new record in between the time of your INSERT and SELECT.