@@IDENTITY keeps returning 0

[Valerie]

I need to get the Autonumber (GUID) of the last record just entered into the table called Farmers. This is the code that I am using, but every time instead of getting the last AutoNo I keep getting 0. The Farmers table is a linked table in a separate Access database. I am working in Access 2000

Function FindGUID()
Dim SQL As String, conn As ADODB.Connection, LastId As Integer, RS As ADODB.Recordset
Set conn = CurrentProject.Connection

SQL = "INSERT INTO Farmers ( Surname ) SELECT 'Jones' AS Surname "
conn.Execute (SQL)
SQL = "SELECT @@IDENTITY"
Set RS = conn.Execute(SQL)
LastId = RS(0)
Debug.Print LastId

RS.Close
Set RS = Nothing
conn.Close
Set conn = Nothing

FindGUID = LastId

End Function​

Thanks

 

[gemma-the-husky]

wouldn't

dmax("idfield","farmers") gove you the last entered value

incidentally an autonumber is a long

 

[Valerie]

No DMax doesn't work because the Autonumber is set as ReplicationID (which gives you a GUID)

 

[Pat Hartman]

I don't think you can get the identity column when you run a query since a query can affect multiple records. You need to use the .addnew method. You then need to get the identity value before you move off the current record.

gemma,
DMax() wouldn't be reliable in a multi-user environment and it wouldn't work at all if the autonumber increment was random as it is with a GUID.

 

[Valerie]

Thanks for your reply Pat.

I already tried using the AddNew method and that didn't work either.

I actually now done a 'work around' - I've added an extra field called timestamp set as Now() and I've used that with another field to search for the record just added - from that I can then find the ID just added. Here is the code that I used

SQLText = "SELECT FarmerID FROM Farmers WHERE (Surname='" & Me.Surname & "') AND (TimeStamp=" & "#" & Month(CurrentTime) & "/" & Day(CurrentTime) & "/" & Year(CurrentTime) & " " & Hour(CurrentTime) & ":" & Minute(CurrentTime) & ":" & Second(CurrentTime) & "#);"
Set MySet = MyDB.OpenRecordset(SQLText)
GSFarmerID = StringFromGUID(MySet!FarmerID)

 

[Pat Hartman]

@@Identity works with the .addnew method. You may have had the instruction in the wrong place. Remember, it only works if you don't move the record pointer.