Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jan 2004
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Display unused ID's (Access 97 SR2)

    We have a small staff database where each member is allocated a three digit number between 001-199. When they leave their record is deleted, freeeing up the number. How do I list the unused numbers on a form, so the ID number can be reused?

    Neville.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Display unused ID's (Access 97 SR2)

    This is a very bad idea. The whole point of unique codes is that they *won't* be reused. I saw a scheme like this at a previous employer's shop and it caused nothing but grief because you couldn't tell whether the data was associated with a current employee or had been entered by or for a previous employee. If this code appeared in time sheet, etc., you would have to check the hire date of the employee to tell whether the info applied to them, and if it didn't you would have no idea of who it actually *did* apply to. If you went back to old data, you would have no record of who that employee was because you had deleted them and replaced them with someone else.

    If you feel you must do this, then add an autonumber key to the employee table and make that your *real* primary key. Replace the current key in any tables, etc., with the autonumber key and simply display the 3 digit code for the employees to see, but don't rely on it as the value in the underlying field. That way, even if you reuse a number, you won't get records tangled, because you will be generating a new autonumber to go with that code.

    The easiest way to get the "unused" numbers is to stick all the valid numbers into a table and then query on your code table with an outer join to the employee table on the 3-digit code where the 3-digit code in the employee table is null, meaning that no matches are found. That will give you all the codes that haven't been assigned.
    Charlotte

  3. #3
    New Lounger
    Join Date
    Jan 2004
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display unused ID's (Access 97 SR2)

    Thanks Charlott. I'll consider your comments.

    There is only one table with about 100 employees. The number is used on an ID card and no where else. Once a person leaves, there is no record kept.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Display unused ID's (Access 97 SR2)

    Then why even bother to deal with it in Access? It sounds like a pretty manual process to me, unless of course, these are security cards/badges of some sort and you want to track what number was assigned to which employee. In that case, you're back to my original suggestion.
    Charlotte

  5. #5
    Lounger
    Join Date
    Apr 2001
    Location
    Khon Kaen, Thailand
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display unused ID's (Access 97 SR2)

    Hi,

    If you insist, here you go! Play with the code below. I use it with On_DblClick event of ID text box. The code will look up for the first unused ID found and fill in the ID text box for you. If no unused ID found, it will prompt you for the next running number.

    Private Sub ID_DblClick(Cancel As Integer)
    If Me.NewRecord Then
    Dim strID As String, intMax As Integer, strMax As String
    strID = FindUnusedID()
    If IsNull(strID) Or strID = "" Then
    If (MsgBox("No more vacant ID left" & vbCrLf & _
    "Do you want to add the next new ID?", vbOKCancel, "No vacant ID")) = vbOK Then
    intMax = Val(DMax("[ID]", "tblID")) + 1
    Select Case intMax
    Case Is < 10
    strMax = "00" & CStr(intMax)
    Case Is < 100
    strMax = "0" & CStr(intMax)
    Case Else
    strMax = CStr(intMax)
    End Select
    Me.ID = strMax
    Else
    Cancel = True
    Exit Sub
    End If
    Else
    Me.ID = strID
    End If
    End If

    End Sub

    Function FindUnusedID()
    Dim dbs As Database, rst As Recordset
    Dim strID As String, intID As Integer

    Set dbs = CurrentDb
    ' You have to open recordset as Dynaset to enable Sort
    Set rst = dbs.OpenRecordset("tblID", dbOpenDynaset)

    With rst
    If Not .EOF Then
    ' You have to sort the recordset beforehand
    .Sort = "ID"
    .MoveLast
    .MoveFirst
    For intID = 1 To .RecordCount
    If Val(!ID) <> intID Then
    Select Case Val(!ID) - 1
    Case Is < 10
    strID = "00" & CStr(intID)
    Case Is < 100
    strID = "0" & CStr(intID)
    Case Else
    strID = CStr(intID)
    End Select

    FindUnusedID = strID
    'Debug.Print strID
    Exit Function

    End If

    .MoveNext
    Next intID
    End If
    End With

    Set rst = Nothing
    Set dbs = Nothing

    End Function

    HTH.
    Tim K.

  6. #6
    New Lounger
    Join Date
    Jan 2004
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display unused ID's (Access 97 SR2)

    Thanks Tim K.

    The database is only used to print ID cards and is used by computer illiterate people. I tried Charlotte's suggestion and it worked, but will keep your code suggestion as I'm sure I will need something like this in the future. People such as yourself and Charlotte do a great job.

    SeaLevel.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •