Autonumber not generating unique number

[ds_8805]

Hello everyone. I have a table called payee table. It has the following fields:
payeeID
payeeName
payeeAddress
Postal Code
accountNo
type

The thing is I have created a form such that it allows the user to add a record into this table. PayeeID is actually autonumber and primary key for this table and the rest of text datatype. The problem here is that my autonumber payeeid seems to be generating non- unique( ids which are already existing). Thus, I cannot add data into the table manually or thru the form.

Someone please help!

Thanks

 

[gemma-the-husky]

it can happen - its documented on MS knowledge base

use an insert query to insert a new value above all the existing values

this should reset the autonumber seed to work correctly.


MS Reference
http://support.microsoft.com/kb/291162

 

[HiTechCoach]

there are several way that can cause the seed value for the next autonumber to get set incorrect.

Have you tried compacting the database with the table?

 

[ds_8805]

hey guys thks for ur replies. What do u mean by compacting database with table? The table currently has alot of records and I have to ensure that none of the data is being lost!

 

[gemma-the-husky]

tools/utilities/compact database

effectively defrags the database and supposedly resets autonumbers to the next highest number

but this may not work - see the MS article I referenced in my last post

 

[ds_8805]

hey i tried to insert a new value into the table using the append query. However the same problem continues to exist

 

[HiTechCoach]

tools/utilities/compact database

effectively defrags the database and supposedly resets autonumbers to the next highest number

but this may not work - see the MS article I referenced in my last post

it work at least with 2003. Just did it.

 

[ds_8805]

just to double confirm with u.. this would not cause any loss of data right?

 

[vbaInet]

Try this on a copy of your database.

Delete al the data from that table (if it exists), run the Compact & Repair utitlity. Then create a blank new database and import all your objects into that.

 

[ds_8805]

hey I tried it but its still not working I really need this to work! Please help!

 

[vbaInet]

Are you sure that's the only primary key in your table and you're positive that it's an autonumber field? Is it linked to another table?

 

[ds_8805]

hey! yes that is the only primary key and its an autonumber field. Yup its also linked to another table.

 

[vbaInet]

So it is looking up its value from the other table?

 

[ds_8805]

the thing is this is the table with the primary key.. the other table is foreign key.. so it is supposed to create the record here in this table first. This problem particularly started like after i created a form to allow the user to insert a new record into the table. However, now I have even removed the form and the problem still exists!

 

[vbaInet]

I hope you're doing all this on a copy of your db!!

Remove all relationships to this Payee table.

 

[ds_8805]

hey yup doing all this on a copy of the db. I have tried that too. Another problem is that this table is already a link table to an existing db. Do u think it wld be much better if i change this autonumber field to number field?

 

[vbaInet]

That may be your problem then. It might be better but you would have to control the unique ID in code.

 

[ds_8805]

control the unique id in code? do u have any code to start me off?

 

[vbaInet]

Have a look at the DMax() function. That would help.

Then you add 1 to that.

 

[ds_8805]

ooo cool! I realised that u can actually set even in the format to ensure no duplicates right? would that be enough actually?