Solved Why is ID skipping numbers on fast entries? (1 Viewer)

[mamradzelvy]

Hi,
I have a form I'm testing out and i noticed that since i have null values enabled, i can just click the entry button and it creates a record with merely an ID and DateTime stamp, however upon spam clicking the entry button i started getting records with IDs that skipped several numbers, for instance here are record IDs as they have been created by my clicking "17,23,28,34,35,36,37,39"
Why does this happen?

 

[mamradzelvy]

Just for clarification, the ID is a auto number column within my table and i have made a total of 19 entries with the last being labeled "39"

 

[jdraw]

See this on autonumbers from UA.

What they are NOT

1. Row (record) sequence numbers.
2. An "order of entry into the table" number
3. A "gapless" series of numbers.
4. Editable numbers.
5. A series of (necessarily) always increasing numbers (see note 1 , below).
6. Intended to be viewed/used by end users of the application.
7. Predictable (as to what the previous or next one in the table is/or will be).
8. Reassigned, once deleted or discarded (exception: see note 2 , below).
9. A predictor/indicator of the number of rows in a table.
10. Intended to be used to "rank" or "sort" or "number" rows returned from the table.
11. Necessarily used to determine the default order the rows may be returned from the table .
12. Indicative of or related to any TimeStamp field that may also be in the table row .

What they ARE

1. Unique numbers used to identify individual rows in a table.
2. Automatically created by Access when a new row is "instanced" by Access (see note 3 , below).
3. Great/Outstanding/Essential for use as the Primary Key of a table.
4. Great/Outstanding/Essential for use as "link points" by Foreign Keys in other tables.
5. Unchanging, once assigned to a particular table row

 

[Gasman]

What is your setting for the autonumber?

Here is one of mine

 

[mamradzelvy]

What is your setting for the autonumber?

Here is one of mine

View attachment 81536

Identical to yours, it only happens when i click really fast.

 

[mamradzelvy]

Now i tried clicking the entry button with 1s delay for several seconds, still resulting in gaps like 90,94,95,98
I don't understand.

 

[Gasman]

Identical to yours, it only happens when i click really fast.

Well I would not worry about it. They are only there to link records and what the value can be is immaterial AFAIK

 

[mamradzelvy]

Well I would not worry about it. They are only there to link records and what the value can be is immaterial AFAIK

well yes, i know, but it just confuses me, because it makes no logical sense why it would behave like this.
access is really something difficult to wrap your head around.

 

[The_Doc_Man]

@mamradzelvy, the problem with using autonumbers is that they cannot mean anything. If you noticed the skipped values, you have seen the reason that autonumber doesn't work IF the generated ID has ANY OTHER MEANING. For instance, if you were using it as an ID number for an invoice system and have a "continuous numbering" requirement, you cannot use autonumber. You must "roll your own" number. Before we send you down the "rabbit hole" here, what is the requirement? Or was this merely an academic question?

EDIT: Your post crossed over mine.

The reason for that behavior is that there is a sequence number slot in the table definition and it gets increment if you start to make a new record. But if you then cancel the record, that increment CANNOT backstep because there is no way to be sure that someone else has not created a new record as well. In that case, a backstep would cause a number to be re-used making it no longer unique.

 

[Gasman]

Try a new database.?
It *might* be corruption. I cannot offer any reasonable suggestion as to why.

 

[Micron]

The answer to the first question might be that something about your form code is cancelling the record creation. If you're starting out with the form loaded with any default or calculated fields, you're starting a record. That uses an id number. Cancelling the record does not make that id value available again - it is lost. That might be explained in the autonumber link you were given but I don't know.

 

[mamradzelvy]

Before we send you down the "rabbit hole" here, what is the requirement? Or was this merely an academic question?

I'm learning access for a administrative job and i'm trying to learn as much as possible, trying to understand it's ins and outs because the company i work for (big truck repair shop chain in my country) is running mainly on access. So yes, just a purely academic question.

 

[mamradzelvy]

I apologize for now, going home now - i will get back to this tomorrow!

 

[mamradzelvy]

Hi,
so i read the material from you guys, I thank you for it.
However it still just baffles me, because I'm sure there is an answer to why this happens, out there somewhere.
For now i'll just go with the conformist answer of: It happens because access is prepared to be recieving data inputs from several sources in rapid succession and it's a feature for future upscaling possibilties and failure reduction in such events.
Think that would about cover it along with your guy's answers.
Thank you all.

 

[The_Doc_Man]

Now i tried clicking the entry button with 1s delay for several seconds, still resulting in gaps like 90,94,95,98

If that is a shared database, the action might come from other users.

If that is a single-user database (user = you) then ask yourself: Is there a process underneath what you are doing that might create another record in the same table?

The reason I am asking is that the speed of your click makes no significant difference. Your machine, if it is typical of modern systems, is running something like 1.6 to 3.2 GHz, which if you look at how the CPU works for the Intel base model means about 0.5 to 1 BILLION instructions per second. You can't manually click something fast enough to outrun that speed unless you are the Flash. So if something is going on, it is incredibly unlikely for "clicking fast" to have any effect whatsoever. BUT stuff happening behind the scenes? There, you can have some kind of effect.