Autonumber Problem

[shadow9449]

A client of mine has been using his database for just over 2 years and encountered an error I've never seen before.

His table contains under 10,000 records. When he tried to create a new record, it assigned the ID (which is an autonumber) as 42. Of course that caused the error that there is duplication in the primary key and the changes could not be saved.

I compacted the database and the problem was not solved. What I did was copy and paste the table under a temporary name, delete the table, and then rename the temporary table with the original table's name.

My solution worked fine and saved the day. But I'm wondering if anyone has seen this problem before or knows what caused it. I'm concerned that it might reoccur.

Thanks

SHADOW

 

[Liv Manto]

Are you using the autonumber ID as a meaningful data? - like it presents a primary key?

Why 42? Check the code behind if its copying a data of 42 somewhere, maybe the current record or something else.

 

[shadow9449]

Yes, the autonumbered field is a primary key.

There is no code behind it. He was using the entry form, but I went into the table itelf and added a new record, and it autonumbered the record as 42.

SHADOW

 

[Liv Manto]

One of the commandments of Access:

Thou shalt not use Autonumber if the field is meant to have meaning for thy users.

I have experienced that problem before. Believe me it always come back.

I dont know if the commandment was the reason but i fixed it following the commandment, so far, everything is ok.

Create another field, MyTableID, make that as the primary key.
So you have to add a code, wherein everytime you add a new record, you get the maximum MytableID then add 1 to it for the new record.

I use Dmax.



(I dont know how other guys do this, so please post your solutions)

 

[shadow9449]

Thou shalt not use Autonumber if the field is meant to have meaning for thy users.

I agree with you, but in this case, the primary key has no meaning to users. In fact, I never show the primary key to the user on the information screen for this reason. It's only for internal indexing and reference.

This being the case, I see no reason to have a different ID as the primary key.

SHADOW

 

[shadow9449]

So do I rebuild (or restore from a previous, known working copy of) the form, or tell the client to get the service pack?

If there's corruption, would that imply that there's faulty equipment on his network (the most common cause of corruption), or is it just "one of those things that happen"?

Thanks, Pat

SHADOW

 

[shadow9449]

Ok, will do

Thanks again, Pat

SHADOW

 

[David Wallis]

Duplicate values in AutoNumber field

The 291162 fix does not work, in my experience; the problem remains even with msjet40.dll version 4.0.8618.0.

I understand that MS recognises the adverse effect of compact/repair on AutoNumber fields as a bug.

To continue with AutoNumber fields, two methods to add to the list:
1) Copy and paste the offending table, and use the copy
2) Create a new database and import all tables and their realtionships.

Method #1 means twiddling with table names and re-establishing realtionships. In my testing, the table has withstood one compact/repair after applying this method.

Method #2 seems to fix the problem without further attention, but I need to do some more testing before I'm fully convinced.

Must admit I am running scared of AutoNumber fields.

 

[thart21]

A couple of days ago I was working in my form updating records and a "Do you want to save this record?" message came up. If I selected No I couldn't leave the record, so I selected Yes and many of the fields for this record changed to Chinese. I decided to just get the info again and fix the data, but, before I could do that, I was working in my table adding new records when suddenly the autonumber field started using duplicate numbers from previous records.

It did not start with the corrupted records' id number, just some other record. I have had Service Pack 8 installed for some time. I was not able to delete the record yesterday, but this morning it let me, still no change in the duplicate autonumbers when adding a record.

The problem I have is that this "projectid" autonumber is the foreign key tying together data from other tables to my main table (tblProjectDetails) (which I now know is bad for multi-user db's) I could export the table to Excel, create a new table and import but I would lose the correlation with the other tables.

Am I stuck? These seem to be my options:

1) New table, new autonumber field, import old data and manually change the foreign keys to match (1600+ records) Problem could happen again.

2) Not sure if this would work-could I do the above, importing back in the autonumbers to a non-autonumber field, keeping the correlation between tables, then add my own autonumber function starting with the last id # generated?

I am constantly doing compact and repair as each time I even open my form, my database grows by about 1MB.

Thanks for any suggestions

Toni

 

[thart21]

Thanks Pat, I created a new db, imported everything but the table and did what you suggested and am now able to create new records with the next correct autonumber.

I have seen many posts both ways regarding auto-number, so, to help ensure this won't happen again, should I take the time to create my own number system so my tables are not dependent on an autonumber?

Toni

 

[The_Doc_Man]

As most of you should know, 42 is the answer to "What is the meaning of life?" (According to Douglas Adams....)