Accesing the Autonumber in an Access Table

I need to access the next number available in an autonumber field of an access table.  I could look for the last number used and incremente it by one, but if a record has been added and then deleted, my solution will not be valid.  Does anyone knows how to do this?
Who is Participating?
watyConnect With a Mentor Commented:
One solution is the following :

With Data1.Recordset
  MsgBox !ID
End With
Another solution is the following :

Public Function GetMaxSequence() As Long
   ' *** Get the las sequence
   Dim record        As Recordset
   Dim sSQL          As String
   On Error GoTo ERROR_GetMaxSequence
   sSQL = "Select Max(Counter) "
   sSQL = sSQL & "From Spool "
   Set record = DB.OpenRecordset(sSQL, DAO.dbOpenDynaset, DAO.dbSQLPassThrough + DAO.dbReadOnly)
   If (record.RecordCount <> 0) Then
      ' *** Infos found
      GetMaxSequence = CLng(ReadRecordSetField(record, 0))
      GetMaxSequence = 0
   End If
   Set record = Nothing
   Exit Function

   LogString "Error in GetMaxSequence " & Error, LOG_TYPE_ERROR
   GetMaxSequence = 0
   Exit Function

End Function
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.