• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 69
  • Last Modified:

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.
0
jacob wallace
Asked:
jacob wallace
  • 2
  • 2
  • 2
  • +2
2 Solutions
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
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
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.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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