Sequential Autonumber IDs are missing!

[fhs]

I have a db with over 87,000 records. Each record has an Autonumber ID. I do not believe any records are missing from the db, but there are some "skipped" or missing Autonumbers. How can I query this table (the only table with Autonumbers) to identify the missing numbers? Thanks ahead of time.

 

[spikepl]

There are ways and means, but the question is why? The reason why I ask is that normally the IDs should have no significance other than as a tool for relations, in which case the discontinuity in numbering (due to deleted records, probably) is irrelevant. So therefore - why?

 

[John Big Booty]

If you are worried about what is being held in an autonumber field you are probably misusing it. An autonumber field should be relied upon only to provide a unique identifying number for each record and nothing more, it is not for human consumption. As you have discovered it is possible under certain circumstance for numbers to be skipped, it is also possible that it might generate negative numbers.

If it matters that your numbers are sequential, for example an invoice number you should use the Dmax() function plus one.

You can use something like;

    If Me.Counter = 0 Then
        Me.Counter = Nz(DMax("Counter", "Table1"), 9910079) + 1  [COLOR="Green"]' Replace 9910079 with you seed number ie. you desired first number less one[/COLOR]
    End If

See the attached for a practical example.

 

[fhs]

I agree with your point. I really did a crummy job explaining the situation. Here it is, my department has used an Access '97 db over ten years, but recently converted to '07. The Autonumber field which provided the record number has skipped a number of times thoughout the use of the application. There are large blocks of numbers that were skipped and never used. Now there are over 87,000 records from 100000 to 987654. Without scrolling through all the records, is there a method to ferret out the missing or "unused" records so that they cann be used? Please let me know if this makes sense...

 

[spikepl]

I agree with your point. ...the missing or "unused" records so that they cann be used? Please let me know if this makes sense...

You intent was clear from your first post. But the purpose of doing what you are attempting to do still evades me. What is the point of trying to create sequential AutonumberIDs? It is an internal variable, whose value is - or should be - immaterial, as JBB explained.

 

[fhs]

The 'six-digit' Autonumber (incremental) is the actual "Stock number" of the item for which there is a record. For whatever reason that the application has skipped several series of Autonumbers, I would like to identify them and develop a way by which they can be used before we get to the end of the six-digit format. I have great hope that this application can be replaced for a variety of other reasons, but that consideration is not on the horizon. Therefore, I am placed in a preservation mode to get the most out of this app. So far, I have manually found over 110 thousand unused Autonumbers (example: 697873 to 718849, that's 20975 unused record possibilities!) Anyway, I appreciate your response and I hope you can offer a suggestion...

 

[boblarson]

You can probably make this better but I threw this together quickly for you. Put this function into a standard module and then you can call it from code or just using it from the VBA Immediate Window (You will need to create a table quickly with a single field of type Long Integer to capture the numbers).

Function GetMissingIDs(strTableName As String, strIDFieldName As String, strToTable As String, lngStartNumber As Long, lngEndNumber As Long)
    Dim rst As DAO.Recordset
    Dim lngHold As Long
    Dim strSQL As String
    
strSQL = "Select [" & strIDFieldName & "] FROM [" & strTableName & "] ORDER BY " & strIDFieldName

    CurrentDb.Execute "DELETE * FROM [" & strToTable & "]"

    Set rst = CurrentDb.OpenRecordset(strSQL)
 
    lngHold = lngStartNumber
 
    Do Until lngHold = lngEndNumber
        If lngHold = rst(0) Then
            If Not rst.EOF Then
                rst.MoveNext
            End If
        Else
            CurrentDb.Execute "INSERT INTO [" & strToTable & "] Values(" & lngHold & ")", dbFailOnError
        End If

        lngHold = lngHold + 1
 
    Loop
 
    rst.Close
    Set rst = Nothing
 
    MsgBox "Complete"
 
End Function

Then you can call it like this from the immediate window:

GetMissingIDs "tblAutonumberSeqTest","TableID","tblMissingID",1,DMax("TableID","tblAutonumberSeqTest")

My tables were this: tblAutonumberSeqTest is the table where I wanted to find the missing values. TableID was my Fieldname of the autonumber in that table. tblMissingIDs was my table which I wanted the missing ID's put into and 1 is the start number and then I used the DMax to get the last ID value of the table where I wanted to find the missing values from. I have it set up this way so it is more generic so you can call it with a specific set of numbers as well.

Hope that helps (it may take a while with so many records but it should work for you) and then you can change your table's autonumber to Long Integer in order to move on from here.

 

[gemma-the-husky]

if you are using SQL then you may not be able to fill in the gaps anyway. if you are using Access tables then you may be able to, but as pointed out, it will not fit in with the current method you are using to generate numbers - so you will need a rewrite, and generally theres is no good reason.

you ought to look at some of the posts on here, and understand the different methods of managing the key values