Return Row Identity after Insert Fails

[rukie]

Hi guys,
I'm writing a database in access 2007 but I'm trying to maintain its ability to be moved from access/sharepoint/sql server/etc. Currently I need to return the row ID of a field I've just inserted. However, each time I run SELECT @@IDENTITY it returns a value of 0. I'd really appreciate any thoughts on this. I understand its a bad idea to use max when you have multiple users. Scope_Identity() isn't a part of the access db.. Please help lol

First I call a public function in a module to open the connection:


[QUOTE][Public Function cnADOAXS()
Public cnAXS As New ADODB.Connection
Public rsAXS As New ADODB.Recordset
Public cmdAXS As New Command

Public Function cnADOAXS
Set cnAXS = CurrentProject.Connection
Set rsAXS = New ADODB.Recordset
cmdAXS.ActiveConnection = cnAXS
end Function
End Function/QUOTE]

then I execute the following:

' Lets create the issue first:
strTitle = Me.txtTitle.Value
strDesc = Me.txtDescription.Value
strImportance = Me.cmbImportance.Value
strSQLIssue = "INSERT INTO tblIssue ([Title], [Description], [Importance], [CIR]) " & _
"VALUES ('" & strTitle & "', '" & strDesc & "', '" & strImportance & "', '" & CIRExist & "');"
' Lets insert the issue
Debug.Print strSQLIssue
Call cnADOAXS
Set rsAXS = cnAXS.Execute(strSQLIssue, lngRecs)

Debug.Print lngRecs
' Now lets retrieve the ID for that row
Set rsAXS = cnAXS.Execute("SELECT @@IDENTITY")
'If Not rsAXS.EOF Then
' Display the drawing description in the form
MsgBox (rsAXS.Fields(0).Value)
'End If
Debug.Print rsAXS.Fields(0).Value
rsAXS.Close
cnAXS.Close

[pkstormy]

When I run a function/code which updates a recordset and I want to grab the 'newly' created autonumber value, I'll use code like this:

...
rs!SomeField = 20
rs.update
dim RID as variant
RID = rs!MyAutonumberField 'RID will now equal the lastest update autonumber value
rs.close
set rs = nothing
.....