AUTONUMBER HELP!!!!!!!!!!!!!!!!!!1

[madetoheal13]

Hello all! Ok I set up my database like 4 months ago and I thought all was going fine until today I tried to add new records to my main table subsequently adding a new record in 7 related tables. It didn't work out as planned. Upon further inspection...it looks like all of the primary keys...which are the table name with "id" after it are autonumbers...but are adding new records "randomly" instead of incremently...I know when I originally set it up...it was on increments...what do I do?

Thanks.
Madetoheal13

 

[alvechurchdata]

Have you got the NewValues property of the AutoNumber field set to Random rather than Increment?

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[jebry]

Hi!

You will also see this affect if there is a lot of records being deleted since Access doesn't reuse deleted numbers.



Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[Pampers]

I think nothing...as long as the tables are linked correctly, the database should work fine...

or make a new autonumbered fields in your table and relink them, but that might be hard to piece the records together.
but do not dispair, there are smarter people then me



Pampers.

You're never too young to learn

 

[madetoheal13]

Hi!!
Yeah alll tables are set at random...but they are not able to be changed to Increment...the random numbers that are being used are huge like 12 digits rather than the 3-5 digits in my database. I tried to redo the autnumber field...and all the info didn't link together.

advice?

Madetoheal13

 

[jebry]

Hi!

In this case Pampers is correct, do nothing as long as the records are linked there is no problem. If your autonumber field needs to be incremental then you shouldn't be using an autonumber, should should instead supply the ID through code.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[madetoheal13]

Hey Jebry!

Ok I can do that but some of the random autonumbers are negative? Is this right?

Madetoheal13

 

[BSman]

If you want to go to incremental numbers rather than random, you need to set up a new table for each existing table. The new table will include all of the old columns, except the old autonumber column will just be a number column (and you might want to call it IDOld). Also have an autonumber column (that is not random) in your new table as your new primary key.

Just append all records from each old table to each new table. Then, for all related tables that include a reference ID to a main table, use the old and new IDs in the new main table to change the values of the reference ID in the related tables. (Use an update query that links the reference ID to the IDOld in the main table.)

After this is done and everything works right with the new tables, you can delete the IDOld column from each table and delete the original version of each table.

Bob

 

[jebry]

Hi again!

To answer your question, yes Access will us negative numbers when the autonumber is set to random.



Jeff Bridgham
Purdue University
Graduate School
Data Analyst