New Record - AutoNumber

Sounds like you have it under control. The method you described would work. Is there another question?

With an autonumber (data type) the value will get put in the new record by Access automatically. The number will be unique -- not necessarily continuous/sequential.

If you choose to use your current Max + 1, you will have to place that value in the new record.

The number will be unique -- not necessarily continuous/sequential.

Click to expand...

Actually, I am pretty sure it is - unless you start deleting old records.
At least I have always found it to be this way.

Joe4 said:

Actually, I am pretty sure it is - unless you start deleting old records.
At least I have always found it to be this way.

Click to expand...

Joe - It is not guaranteed to be incremental, even if you have it set to incremental. It only guarantees you a UNIQUE number. While for many people it works as you would think, it doesn't always and it can jump numbers all of a sudden, can go into random mode which will generate negative numbers as well. It is best to NOT use an Autonumber for anything but a number which you need to be unique (such as a surrogate key). Any other use than that will be playing Russian Roulette with your structure. I have yet to find any Access MVP who advocates otherwise.

Bob that is very interesting. I only use it to guarantee uniquesness (don't really count on it for programming) , but have never noticed it behave in that manner. But I don't doubt what you say (Access has a variety of "bugs"). As matter as fact, I seem to recall that we may have discussed something like this before (the old gray matter doesn't retain information like it used to!).

Does there seem to be any rhyme or reason when this autonumber "weirdness" happens?
Or is there some way to reproduce this odd behavior?
I would love to try to "force" it happen, just to see it happen (because I am a curious guy!).

I don't know of a way to auto-populate a field except by autonumber. You *can* query for the max value and increment it when you insert a new record. If your DB is very busy you may need to be careful in case two users try to get the same next available ID number at the same time (it would fail for the second person to try to use it).

I've never (actually) seen a negative AutoID number - has anyone else?

I don't know of a way to auto-populate a field except by autonumber. You *can* query for the max value and increment it when you insert a new record.

Click to expand...

Yes, if you control new entries via Forms, you apply VBA code to the adding of the new record in the Form that would populate this number.

If you are importing the data, you could use some VBA code to loop through the Recordset afterwards and add the values.

As an example, use a public function in a standard module (I've called your table Table1 here):
Is there a better approach? I use something like this in one of my databases to insert new records with incrementing numeric IDs.

Edit:
Ah, just saw Joe's last post. That's a good thought. Reminds me that you could probably just use a DLookup() function on a form or in VBA which would be simpler than my function above:

xenou said:

I've never (actually) seen a negative AutoID number - has anyone else?

Click to expand...

I have and, I've seen it where it WAS set to incremental. But if it is set to RANDOM then it will have negatives. Or if you have it set to incremental and you choose to use replication, then it will revert to random. But many times it is a slight bit of corruption which causes it to go "off the rails" so-to-speak. So it is best to not rely on it for anything except a unique number.

My experiments have concluded that Access always retains the last number generated, so if you delete records -- even just the last one or all of them -- you'll still get one greater than that last number, wherever it is. I've never see an autonumber less than 1 before...

To force it to start at a particular number N, you'd have to 1) delete and re-create the table object, 2) have a make-table query which copies only the top N-1 records into a new table, and then use that new table, or 3) copy only the table's structure into a new table, and then use an append query which has all the fields except the auto-number field. Obviously these are all pretty impractical.

Autonumbers to me are like allocating memory in C/C++/C#: it doesn't matter what the actual address of the memory is, as long as you have the address, you can read and release the memory when you need to.