Create Auto Increment field? (1 Viewer)

got_access:] · Feb 19, 2019

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 · Feb 19, 2019

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 · Feb 19, 2019

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:] · Feb 19, 2019

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 · Feb 19, 2019

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 · Feb 19, 2019

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:] · Feb 19, 2019

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 · Feb 19, 2019

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 · Feb 19, 2019

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

Mark_ · Feb 20, 2019

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:] · Feb 20, 2019

Tera said:
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 · Feb 20, 2019

got_access:] said:
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:] · Feb 20, 2019

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

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

Create Auto Increment field? (1 Viewer)

got_access:]

Registered User.

theDBguy

I’m here to help

June7

AWF VIP

got_access:]

Registered User.

theDBguy

I’m here to help

June7

AWF VIP

got_access:]

Registered User.

deletedT

Guest

June7

AWF VIP

Mark_

Longboard on the internet

got_access:]

Registered User.

theDBguy

I’m here to help

got_access:]

Registered User.

Similar threads

Users who are viewing this thread