Solved Starting Appended Data at 1

[jwcolby54]

Here's a simple illustration of the method with one table and one append query.

@GPGeorge can you test what happens if you add a single record into a multi record hole? IOW delete 5 records, then fill in the first hole, then add another record manually. IIRC the autonumber picks up after your append and adds one to whatever that PK you just placed in with a query.

PK:

01 to 100 exists
101-105 are a hole
106 - 110 exists.

Use your query to add 101 into the hole. Add a new record allowing the autonumber to do whatever it wants. What is the autonumber value it places into the table? Is it 102, the number after yours? Or is it 111, the number it would have been if you had not inserted your record?

I seem to remember that it adds 102, 103, 104 and 105. When it runs into 106, it "jumps" to the last (highest) value plus 1. In this case it would go from 105 to 111.

Is that clear as mud? I haven't actually done any of this for 15 years so maybe my memory is hazy.

 

[jwcolby54]

Here's a simple illustration of the method with one table and one append query.

 

[jwcolby54]

 

[jwcolby54]

I used George's little demo to run his query, inserting the PK number 2. I then entered G and H successfully. Notice that the PK continued to increment the counter from 2 -
G: 3,
H: 4
However when I tried to enter I : 5, it stopped me from entering that record because the autonumber was trying to put 5 in the PK, creating a duplicate key.

 

[jwcolby54]

In #62 I said that it would do this except that I thought the PK mechanism was smart enough to go find the last PK value and increment that. IOW I thought when I entered I: 5 it would know it was "out of room" and go find the highest PK... 6, increment that, and start with 7. Instead it tried to continue in the sequence with 5. When it tried to do the save, it "discovered" that 5 was already used and simply threw an error.

 

[jwcolby54]

Sometimes it is important to know stuff just because. If you ever want to insert records to "fill in a hole", recover archived records etc, you probably want to run another query that finds the highest PKID, increments by one, and inserts that record as well. This allows the autonumber to continue on its merry way.