re-enter deleted record

[CDSCait]

Ok, somehow three records were deleted from my database. In the ID column,
which is an autonumber field and is the primary key, it literally skips three
numbers -- 1, 30 and 214 -- and the corresponding records are missing. (No
snide remarks about the "Are yoy sure you want to PERMANENTLY DELETE this
record?" warning, please.)

Is there any way I can re-enter the information for these records and --
here's the tough part -- restore the records' original ID numbers, so they
appear sequentially in the table, where they belong (with no skipped
numbers)?
Meaning, rather than being positioned as, say, 232, 233 and 234, they can be
1, 30 and 214?

I tried (1) entering the three records, then (2) sorting my records the way
I want by sorting according to the values in a different field, then (3)
deleting the ID field in "design view", then (3) re-creating a new ID field
in "design view". But when I went back to the table view it still just
re-sorted the records, and assigned ID numbers to them according to the order
in which they were entered -- meaning these three were repositioned at the
end of the table, instead of in rows 1, 30 and 214, (and numbered
correspondingly) where I want them.

As you can tell, I could use some help. Thanks in advance.

 

[UpRider]

This happens a lot. The autonumber ID field should not be used for DATA.
It is meant to be an unique identifier ONLY.
Here's a solution.
1. Add a long integer field to your table and call it perhaps MyID.
2. Create and run a one-time update query to make MyID = ID.
3. Add your deleted records, and assign the missing numbers to MyID.
4. Change any existing code in your database to use MyID instead of ID for
this table.
5. When you add a new record from the form (I hope you are using a form to
enter and edit your data), make MyID = dmax("MyID","MyTableName")+1

hth, UpRider

 

[CDSCait]

I guess I should have been more specific -- this database was set up so the
data is entered and edited directly into the table, not into a form. (It was
set up this way before I got here.)

Hopefully I can figure out most of those steps anyway. Thanks. Any other
suggestions would be appreciated.