Discussion Locked

MS Access AutoNumber Macro

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
Does anyone know how to create a autonumbering macro that reuses numbers that have been deleted in the past so that no gaps exist in the number list?

5 replies

by john_wills

This is not a macro but should do what you want.

Private Sub number_the_new_Umbrella()
Dim IDCounter As Integer
Dim navCounter As Long
IDCounter = 1
Do While DCount("[UmbrellaID]", "Umbrella", "UmbrellaID = '" & IDCounter & "'") > 0 _
And IDCounter < 100000
IDCounter = IDCounter + 1
Loop
If IDCounter = 100000 _
Then
MsgBox "There is no more room in the table of Umbrella Organizations - please tell I.S."
ElseMe.UmbrellaID = IDCounter
lastUpdated = Date
MsgBox Me![name] & " will have Umbrella ID " & Me.UmbrellaID
Me.DataEntry = False
navCounter = DCount("[UmbrellaID]", "Umbrella", "UmbrellaID <= '" & IDCounter & "'") DoCmd.GoToRecord , , acGoTo, navCounter
End If
End Sub

by john_wills

This is not a macro but should do what you want.

Private Sub number_the_new_Umbrella()
Dim IDCounter As Integer
Dim navCounter As Long
IDCounter = 1
Do While DCount("[UmbrellaID]", "Umbrella", "UmbrellaID = '" & IDCounter & "'") > 0 _
And IDCounter < 100000
IDCounter = IDCounter + 1
Loop
If IDCounter = 100000 _
Then
MsgBox "There is no more room in the table of Umbrella Organizations - please tell I.S."
ElseMe.UmbrellaID = IDCounter
lastUpdated = Date
MsgBox Me![name] & " will have Umbrella ID " & Me.UmbrellaID
Me.DataEntry = False
navCounter = DCount("[UmbrellaID]", "Umbrella", "UmbrellaID <= '" & IDCounter & "'") DoCmd.GoToRecord , , acGoTo, navCounter
End If
End Sub

This discussion is closed to new replies.