Other suggestions instead of autonumber

[LisaP]

Dear All,

I have a form for data entry with a field ID, I need to have this field automatically insert a number (instead of using autonumber). I guess the best way to do this is to obtain the value from the previous record and paste this into the ID field + 1. However have tried this using the sendkeys function and doesn't work, just returns a value of 0. Any help will be appreciated (I have 1 week left to remove all "bugs" from database!!) Thanks

 

[LisaP]

Pat,

Thanks for your reply. The reason is that unfortunately a user has deleted some records (which should not have happened) and now the ID numbering is out of synch. I just wondered if there was another way instead of autonumber.

Thanks,

 

[Atomic Shrimp]

It is possible to restore an auto number field by deleting it then adding it again so that it runs consecutively BUT (and it's a big BUT) if you're using it as a key field against any other tables, it would seriously mess up your data integrity.

There are ways round this which would involve creating new keys before disposing of the old ones, but that is going to get complex (although probably still less complex than coding an autonumber style thing yourself)

HTH

Mike

 

[Peter D]

I'm going to re-iterate what Pat said above.

My suggestion is to just use autonumber, and do not rely on the value of the autonumber field to be anything meaningful.

Good design would require that you just let your primary key field be a primary key, and that's it.

If you want an ascending number field that starts at 1 and increments by one to the last record, then create a (non-key) field for that purpose.

Hope this helps,

Peter De Baets
Peter's Software - MS Access tools for developers http://www.peterssoftware.com

 

[LisaP]

Thanks Peter,

This is exactly what I want to do, just one last thing, can you tell me the macro for getting the field to increment by 1.

Many thanks

 

[Jack Cowley]

Lisa -

I have sent a demo to your email address. Hope it is what you are after.

Jack