Insert record into an autonumber field

[gregorg]

hi, i have a database with a primary key that is autonumber. my client deleted a record so the autonumber field is not contiguious. ie it goes 166,167,169, so record 168 has been deleted. this is throughing out the number system she has.

i am trying to insert a record 168 into the table but can't because it is autonummber and a key field, can anyone advice how to do this ?

gregorg

 

[neileg]

Well yes you can but you shouldn't. An autonumber field should never be used for a field with meaning to the user. You can't guarantee that the numbering will be contiguous as you have found, and it can leap about, too. You can find it goes 166,167, 32451, 32452 etc. All you can be sure of is that it is unique.

If you want a truly sequential number, you'll need to create it yourself. Been covered many times in these forums and there more than one approach depending on your setup.

If you want a quick fix for now, you can append a record into a table with an autonumber field and specify the number. So if you create a temporary table with the data you want you can then run an append query to add it to your main table.

But your numbering will go wrong again, sooner or later.

 

[gregorg]

can i use an insert into sql statement in VBA ?

how would this look ?

gregorg