AutoNumber Question and Problem 1

[roaml]

Hello,

I have two questions.

First, why are records resorted when an AutoNumber is deleted and replaced. There have been occasions when I have deleted an AutoNumber and reinserted a new one to synchronize the numbers. But, by doing that, the original records are resorted. Can someone tell me why this occurs?

Second, can someone give me alternative suggestion on how to replace an AutoNumber and stop my records from resorting. I use the AutoNumber as a work order id and need to retain the same id associated with the work order.

I appreciate any assistance.

Thank you

 

[dynamictiger]

Do not use the autonumber to store a required work order number. As you have found out this could cause problems later when you want to upsize the table, change design and so on. You can still autoassign a work order number in code behind the form or what have you.

The Access generated Autonumber is normally the record ID field, this is indexed and set to unique. By deleting a value then force entry a different value Access by design must reassign all other ID numbers.

 

[browntrout]

The following code (originally downloaded from Candice Tripp's website as "Autonumber without autonumber" - but the link now seems dead, although the reference is still there) has worked excellently for me.

Using the Nz function, and a form's "Current" property, it automatically generates a new ID for each new record based on the highest existing ID number.

Private Sub Form_Current()
If Me.NewRecord Then
On Error Resume Next 'It should never occur, just to be sure...
Me!MailingListID.DefaultValue = Nz(DMax("[MailingListID]", "tblMailingList&quot, 0) + 1
End If
End Sub

 

[roaml]

EXCELLENT! I will try the suggestions of implementing an auto-increment function through the code provided.

Thank you both for your kind response and suggestions.