Loop to fill in deleted consecutive numbers 1

[r0nniem]

This is probably really basic, but I'm new to "behind the scenes" side of things.

I have a field "ID" listing numbers. Say 1, 2, 3, 5, 6.
I want the next record to be 4 - filling in the blank.

I know it's a Loop thingy, but now sure how to go about it yet. If you could get me on the right track that would be great

 

[TheAceMan1]

How are ya r0nniem . . .

I don't know if [blue]ID[/blue] is autonumber or not, but have a look below . . . [blue]its the principle you need to grasp![/blue]

Use Autonumbers properly

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[r0nniem]

Thanks for your response TheAceMan1 ... btw I'm fine U?

It's not an autonumber but thanks for that info.

I'll explain a bit more. The number is an ID number relating to a person in a call centre so only each person know's their number for the reports. When someone leaves, we don't want to report their details anymore and want the new person to have the lowest ID number.

I suggested to give them a different ID (such as 3 digit number) but my manager doesn't want to have the numbers get high to show high staff turnover.

Does that make sense?

 

[TheAceMan1]

[blue] . . . but my manager doesn't want to have the numbers get high to show high staff turnover.[/blue]

A good reason indeed! . . .

In a module in the modules window, copy/paste the following function (The code requires [purple]Microsoft DAO 3.6 Object Library[/purple] to run. To [blue]check/install[/blue] the library, in any code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Then using the up arrow, [purple]push it up as high in priority as it will go[/purple]. Click OK.:

[blue]Public Function  As Long
   Dim db As DAO.Database, rst As DAO.Recordset
   Dim Idx As Long, Max As String, CaughtID As Long
   
   Set db = CurrentDb
   Set rst = db.OpenRecordset("[purple][b]TableName[/b][/purple]", dbOpenDynaset)
   Max = DMax("[ID]", "[purple][b]TableName[/b][/purple]")
   Idx = 1
   
   DoCmd.Hourglass True
   
   Do
      rst.FindFirst "[ID] = " & Idx
      
      If rst.NoMatch Then
         CaughtID = Idx
         Exit Do
      End If
      
      Idx = Idx + 1
   Loop Until Idx > Max
   
   If CaughtID Then
      NextID = CaughtID
   Else
      NextID = Max + 1
   End If
   
   DoCmd.Hourglass False
   
End Function[/blue]

From anywhere:

[blue]   Call NextID()[/blue]

To get the next ID . . .

[blue]Your Thoughts? . . . [/blue]

See Ya! . . . . . .

 

[r0nniem]

That's done the trick, thank's heaps. I've just got to work out how to get it to apply to the last record on the form now

 

[TheAceMan1]

. . . of course the first line should be:

[blue]Public Function NextID() As Long[/blue]

See Ya! . . . . . .

 

[barny2006]

the best way to do it, would be to load the id into a table with the subscript being the id. array(id).
then scan the array to see what the missing values are and plug in the id using id - 1 (id minus 1).

 

[Golom]

You could use an SQL approach

Private Function NextNumber() As Long
Dim SQL    As String
Dim rs     As DAO.Recordset

SQL = "Select TOP 1 A.[ID] + 1 As [NextNumber] " & _
      "From myTable A LEFT JOIN myTable B " & _
      "     ON A.[ID] + 1 = B.[ID] " & _
      "Where B.[ID] IS NULL " & _
      "Order By A.[ID] + 1 ASC "

Set rs = CurrentDb.Openrecordset(SQL)
If Not rs.EOF Then NextNumber = rs![NextNumber]

Set rs = Nothing
Exit Function

It should be faster than looping through records in a recordset.