Create Auto Increment field?

[got_access:]]

Hi All,
I'm building a new database and one of the tables I originally created with the ID field as an autonumber. However, it became obvious as I continued to refine the DB and add data to it - that the autonumbers are now out of sequence and there are some missing numbers.

So I changed the autonumber field type to simply number and manually fixed them to ensure they increment correctly.

But it would be nice if I could figure out a validation rule to ensure the next record number is Max(field) + 1. In other words the next number would represent the hightest ID number plus 1.

But Access doesn't seem to have the MAX function available in the validation rule builder. Is there a way to do this?

Thanks!

 

[theDBguy]

Hi. Before we try to answer the immediate question of validating the sequence number, may I please ask why it matters your table does not have any missing sequence numbers? What happens if a record in the middle of the sequence is deleted?

 

[June7]

Don't use ValidationRule, use DefaultValue.

Generating a custom unique identifier is a common topic but not sure you really need to do this. Is this a multi-user split db? Run the risk of simultaneous users generating the same ID.

An autonumber field should not have meaning to users so sequence and gaps should be irrelevant.

 

[got_access:]]

Thanks
I think the both of you are kind of responding to the same thing.
Does it really matter if an auto number is missing sequence or there are missing numbers.

I guess it is true that to an end user in most cases an auto id may not even be visible so it won't matter to them.

I guess the issue is how it bugs me to see out of sequence or missing id numbers.
So perhaps I'm being to anl

 

[theDBguy]

Hi. The problem is, you will not completely eliminate gaps. Like I said, what happens if a record is deleted? Would you be okay with a gap then?

 

[June7]

I know what you mean about not liking the gaps. Went through the same issue. Even developed code to manage this (not allowing delete and instead blanking out fields and reusing the record).

Deleting records should be a rare occurrence but certainly not impossible, then there will be gaps, unless managed as I describe.

 

[got_access:]]

Thanks very much!

I can live with gaps and such in the IDs

But I am curious about what you mentioned about generating a custom unique identifier as a common topic.

What is the most common way of implementing that?

Thanks

 

[deletedT]

I don’t really suggest to do this.
But as some additional info you can reset auto number field if you don't like gaps.

https://support.microsoft.com/en-ca/help/812718/how-to-reset-an-autonumber-field-value-in-access

 

[June7]

Review http://www.accessforums.net/showthread.php?t=23329

 

[Mark_]

One way to avoid some gaps is by having a flag for "Deleted" in your record. If a user deletes the record, the record is not really deleted but your system will mark it as such. This way you can undelete records that were accidentally deleted AND you know what was deleted.

 

[got_access:]]

I don’t really suggest to do this.
But as some additional info you can reset auto number field if you don't like gaps.

https://support.microsoft.com/en-ca/help/812718/how-to-reset-an-autonumber-field-value-in-access

Hi Tera and thank you.
I am familiar with creating a new table from the template of the current one and then loading that table and adding the autonumber - which would reset them.
I've done that before - but only after the dataset has matured enough to ensure I wouldn't have to do it more than once.

 

[theDBguy]

Thanks very much!

I can live with gaps and such in the IDs

But I am curious about what you mentioned about generating a custom unique identifier as a common topic.

What is the most common way of implementing that?

Thanks

Not sure if it will help but take a look at this Custom Autonumbers. This link will take you to a topic where you can download a working version and a good discussion of whether this is a good idea or not. Cheers!

 

[got_access:]]

Review http://www.accessforums.net/showthread.php?t=23329

Thanks June7 - I'll take a look at that!