Autonumber breaking

[Dinger_80]

When I create a new record I am also generating a new Auto Number. This is so I have a sure fire way of returning the records that I want to return. I have read where it is a known issue that when using the Compact and Repair it can reset the Auto Number to a lower number and generate a duplicate Auto Number. That is the problem that I have at this time. I have tried using the Allen code but it doesn't seem to worked on linked tables. I use linked tables because I have multiple users who can access this system at any given point in time. What I am hoping for is that someone can offer advice on how to have the Auto Number field select a number that is unique. Any advice is appreciated.

 

[CJ_London]

It sounds like you are not explaining your problem clearly enough

the autonumber will be unique, if you compact and repair the next autonumber will be the next highest number. It does not renumber records and any missing numbers will not be replaced.

The fact the tables are linked is irrelevant - I presume you appreciate you have to go into the backend and compact there.

To be clear, you are talking about an autonumber field, not a field where you are calculating the next value?

 

[Galaxiom]

I have read where it is a known issue that when using the Compact and Repair it can reset the Auto Number to a lower number and generate a duplicate Auto Number.

I have never heard of that one.

Autonumber will continue from the inserted value if records are inserted using a query with a lower value than the maximum assigned to the autonumber field.

 

[Dinger_80]

I agree the Auto Number should be unique. However, on more than one occasion as of late I have run into an issue where that isn't happening. The Auto Number has started as far back as 20 digits. For example, the next unique number in line should have been 2276. However when a user attempted to create a new record, the Auto Number came up with 2251. That wouldn't be so bad because that number had been passed by either because someone started a record and ditched it or something along those lines. However, the next user who created a New Record, the Auto Number didn't jump back up, it went to 2252, that number had already been used in the Auto Number field. When the user went to save the record an error occurred. The error reads as follows.

[FONT=&quot]3022: The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again. [/FONT]

So it isn't changing any saved records that are in the table. The Auto Number at times simply doesn't pick up where it left off. When a user creates a new record, is is on a form and the information is saved to the table when they are done. I am not using a query to update the table.

I haven't gone into the back end to compact and repair as I set up the file on the server to do this on close. That is the only file that is compact and repaired. Just in case there is a question about how I generate the new record, I simply have it as

DoCmd.OpenForm "TestRequestForm"
DoCmd.GoToRecord , , acNewRec

Though, if I tried to create a new record directly in the table, the problem with the Auto Number would be there also. I know it sounds like I am not with the program, but this problem has been known to happen in earlier versions of Access, ie 3003, 2007. I am using Access 2010. For some reason it still happens. Here is a link that is from Microsoft talking about the problem. This way its clear that this does happen.

http://support2.microsoft.com/?id=884185

 

[AccessBlaster]

http://www.utteraccess.com/wiki/index.php/Autonumbers

 

[Dinger_80]

Those are all valid points. However, regardless of how it should work, it doesn't. Specifically, point 8 in what they are not. Microsoft has commented on said such issue. The problem is that I have to generate a work around. So I respect the points made and all are exactly what I have come to expect or not expect with Auto Numbers. I wish that access followed those rules all the time. It doesn't and that is the problem I am having. When I followed the VBA that Microsoft has suggested in the link I posted, I had an error because the tables are linked.

 

[essaytee]

Those are all valid points. However, regardless of how it should work, it doesn't. Specifically, point 8 in what they are not. Microsoft has commented on said such issue. The problem is that I have to generate a work around. So I respect the points made and all are exactly what I have come to expect or not expect with Auto Numbers. I wish that access followed those rules all the time. It doesn't and that is the problem I am having. When I followed the VBA that Microsoft has suggested in the link I posted, I had an error because the tables are linked.

If you're like me, how many times have you attempted to change the design view of a linked table only to be told that you can't make changes to a linked table. That is your problem at the moment.

You have two options.

1. Simply open the data file directly and apply your VBA code from there. Then it is not considered a linked table.

2. You can do the same from your frontend file but you will have to programmatically open the database (not Current Database) and refer the code to that.

I've never come across this problem, hope my suggestion works.

Steve.

 

[CJ_London]

if you want a workaround, try changing the fieldsize from Long Integer to ReplicationID and/or New Values from increment to Random

 

[smig]

AutoNumber can't have a duplication as the Allow Duplication is set to No.
If you try to put a duplicate number you will get an error message.

AutoNumber can go crazy if you try to enforce something into it, either by query or by RecordSet.

It will start from the last point it was left. It won't go back if you delete records Even if you empty the table completely.

some will not use AutoNumber at all and will use the DMax() function to find the next PK number.

 

[CJ_London]

Even if you empty the table completely.

It will reset to 0 if you empty the table and compact

 

[gemma-the-husky]

compact and repair does NOT generate problems with autonumber seeds.

very rarely the seed can get corrupted, causing the problem you describe.

You need to append an item with an autonumber value BIGGER than the highest autonumber in the table. This will reset the seed value.

eg - this fix offered by Allen Browne

http://allenbrowne.com/ser-40.html

 

[smig]

It will reset to 0 if you empty the table and compact

Might be if you compact. Never tried that.

 

[Galaxiom]

It will reset to 0 if you empty the table and compact

This was true in earlier versions of Access but it has not been reliable since A2003. Microsoft has acknowledged this.

The recommended solution to reset the autonumber seed is to copy the table structure and insert the records from the old table.