@@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)

 

[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)