Autonumber screwed ?

[brianjohnson]

Folks,

For my sins, I maintain a club database running under Access 97. I have some problems with the autonumber used to create new records and link all existing records. Occasionally, a new record is not created but appended to an existing lower level record. I've resolved this in the past by deleting the offending form and loading an old copy - I've also rewritten the code. It's just done it again (!) so I'd like to reset the autonumber from 1 without losing the table relationships that already exist.

Can you advise some suitable code for this purpose please ?

I also looked for a way to unload the database to a file and reload - thinking that this should produce a nice clean copy - couldn't see a simple way .... ?

Thanks and Regards

Brian

 

[ghudson]

Your "sin" is for trying to use the AutoNumber as a sequential numbering system for your records. That is not the purpose of the AutoNumber. You need to create your own numbers if you want to have the next new record "1" number higher than what is in the table.

Search the forum for something like "autonumber" "create autonumber" and you should find a few answers on how to create your own autonumber procedure. I believe that it will involve using the DMax function.

HTH

 

[brianjohnson]

Thanks ghudson for the response.

I actually inherited this database via a number of "owners" back I believe to the original Access product. Assuming that I replace autonumber with a custom numbering system, can I seek some advice on code that would replace the autonumber system without losing links to all the existing records with their associated links across all the tables in the database ?

Some sort of find and replace process running from inside a module ... ? I've a little VB experience to draw on but pondering on how to make a start

Thanks and Regards

Brian