Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 151
  • Last Modified:

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?
0
gawilliam
Asked:
gawilliam
  • 2
1 Solution
 
watyCommented:
One solution is the following :

With Data1.Recordset
  .AddNew
  MsgBox !ID
  .CancelUpdate
End With
0
 
watyCommented:
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))
   
   Else
      GetMaxSequence = 0
     
   End If
   
   record.Close
   Set record = Nothing
   
   Exit Function

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

End Function
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now