We help IT Professionals succeed at work.

how to Recycle unused Numbes in Device tracking Data base (inventory)

ok im so im not an access guru. but i have made and inventory tracking system specifically for desktops and printers using auto numbering and a naming convention of WLHWS"000" so i have everything i need my data base to do but one thing. i hope you can help. i have set up a macro ( i think its called ) that when the device status changes to Decommissioned, to delete record.. but we want that name in the auto numbering sequence to select the next available name/number. example Device names 001-010 is active. i decommission 005. new device comes in and i enter it in the data base., but.. next avaliable name is 011 when i need it to be 005. i hope it make sense. anything would help thanks.
Watch Question

Dave BaldwinFixer of Problems
Most Valuable Expert 2014

Standard procedure for autonumber / autoincrement functions in database is to never reuse numbers.  The next number is always one more than the current highest number.  Another standard practice is to never use an autonumber for a 'serial number'.  The purpose of autonumbers is to uniquely identify a row, nothing more.


any recommendation on how to make a system like this function?
Dave BaldwinFixer of Problems
Most Valuable Expert 2014

Use a different column for your count or don't use autonumber.  You can't change the behavior of the autonumber function.
I absolutely agree with Dave, but it is possible. Look at sample. Add some text to DNAME field and press Add button. Delete any record, change DNAME and press button again. It is result of this function:
Public Function empty_num(sTable As String, sField As String) As Long
' sTable - table name, sField - autonumber field name
Dim SQL As String, i As Long
Dim rst As Recordset
SQL = "Select [" & sField & "] from [" & sTable & "] Order by [" & sField & "]"
Set rst = CurrentDb.OpenRecordset(SQL)
i = 1
Do While Not rst.EOF
    If rst(sField) > i Then
        empty_num = i
        Exit Function
    End If
    i = i + 1
empty_num = i
End Function

Open in new window

You can use it as following:
Dim SQL As String
Dim r_num As Long
r_num = empty_num("Table1", "ID")
If Not IsNull(r_num) Then
    SQL = "INSERT INTO Table1 ( ID, DNAME ) SELECT " & r_num & ", " & Chr(34) & Me.Text18 & Chr(34) & "FROM Table1"
    DoCmd.SetWarnings False
    DoCmd.RunSQL SQL
    DoCmd.SetWarnings True
End If

Open in new window

Distinguished Expert 2017

This really has nothing to do with Access.  It has to do with proper database design.  Do you ever print reports? do you ever export data?  If you  decommission an item, do you recall all printed documents that refer to the decommissioned item?  Do you recall all exported files?  Do you ever do any historical reporting?

It makes no sense in the real world to reuse numbers like this and doing so will only lead to confusion in the future.


thanks guys for you input. this is just a simple data base we are using to  track multiple devices and their naming scheme. no reports not printing. just a tracker. thank you for your time.
Most Valuable Expert 2015
Distinguished Expert 2018
Susan Harkins and I once wrote an article on this including code and a demo:

Find and Generate Missing Values in an Access Table

It demonstrates using a Listbox for this, and the code is quite simple:
Private Sub btnSearch_Click()

' Read table/query sequentially to 
' record all missing IDs.
' Fill a ListBox with missing values.
' A reference to Microsoft DAO must be 
' present.

  ' Define search table or query.
  Const cstrTable As String = "Orders"
  Const cstrField As String = "OrderID"
  Dim dbs As DAO.Database
  Dim rst As DAO.Recordset
  Dim lst As ListBox
  Dim col As Collection
  Dim strSQL As String
  Dim strList As String
  Dim lngLast As Long
  Dim lngNext As Long
  Dim lngMiss As Long
  strSQL = "Select " & cstrField & "" _
   & " From " & cstrTable & " _
   & " Order By 1;"  
  Set lst = Me!lstMissing
  Set col = New Collection
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset(strSQL)
  If rst.RecordCount = 0 Then
    'The recordset is empty.
    'Nothing to do.
    lngLast = rst(cstrField).Value
    While rst.EOF = False
      lngNext = rst(cstrField).Value
      For lngMiss = lngLast + 1 To _
       lngNext - 1
        col.Add (lngMiss)
      lngLast = lngNext
      'Generate next value in sequence.
      'Discard if collecting only 
      'missing values.
      col.Add (lngLast + 1)
  End If
  'Populate list box from collection.
  For lngMiss = 1 To col.Count
    If Len(strList) > 0 Then 
      strList = strList & ";"
    End If
    strList = strList & col(lngMiss)
    Debug.Print col(lngMiss)
  lst.RowSource = strList
  Debug.Print strList
  Set rst = Nothing
  Set dbs = Nothing
  Set col = Nothing
  Set lst = Nothing
End Sub

Open in new window

Most Valuable Expert 2015
Distinguished Expert 2018

Answers provided.