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.
jacob wallaceAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
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.
1
jacob wallaceAuthor Commented:
any recommendation on how to make a system like this function?
0
Dave BaldwinFixer of ProblemsCommented:
Use a different column for your count or don't use autonumber.  You can't change the behavior of the autonumber function.
1
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

als315Commented:
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
        rst.Close
        Exit Function
    End If
    i = i + 1
    rst.MoveNext
Loop
empty_num = i
rst.Close
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
    Me.Requery
End If

Open in new window

DBAuto.accdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
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.
0
jacob wallaceAuthor Commented:
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.
0
Gustav BrockCIOCommented:
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.
  Else
    lngLast = rst(cstrField).Value
    rst.MoveNext
    While rst.EOF = False
      lngNext = rst(cstrField).Value
      For lngMiss = lngLast + 1 To _
       lngNext - 1
        col.Add (lngMiss)
      Next
      lngLast = lngNext
      rst.MoveNext
    Wend
      'Generate next value in sequence.
      'Discard if collecting only 
      'missing values.
      col.Add (lngLast + 1)
  End If
  rst.Close
  
  '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)
  Next
  lst.RowSource = strList
  Debug.Print strList
  
  Set rst = Nothing
  Set dbs = Nothing
  Set col = Nothing
  Set lst = Nothing
  
End Sub

Open in new window

/gustav
0
Gustav BrockCIOCommented:
Answers provided.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Printers and Scanners

From novice to tech pro — start learning today.