how do I insert new DB records between existing records

[Guest]

I am setting up a DB and it uses auto numbering for the first field. I would
like to add a DB record between existing records. When I click on "add new
record" it automatically sends me to the end of the list of records. How do
I insert a record between 2 existing autonumbering records?

 

[Rick Brandt]

I am setting up a DB and it uses auto numbering for the first field. I would
like to add a DB record between existing records. When I click on "add new
record" it automatically sends me to the end of the list of records. How do
I insert a record between 2 existing autonumbering records?

You can't. Tables actually have no particular order. You could use your own
numbering system instead of using an AutoNumber. In your own system you could
number 10, 20, 30 , etc.. This would allow you to then add a record with the
number 15 if you wanted it to show as the second record in a query, form, or
report that was sorted on that field.

 

[Jeff Boyce]

In addition to Rick's comments, I would add ...

Define "between". Do you mean with a date/time field value between your two
records' values? Or between two "numbering" record values? As Rick points
out, you can't "insert" an Autonumber between 00001 and 00002.

And if you are using the Autonumber field as designed, you would NOT want to
"bump" the 00002 record (and every record above it) up by one to insert a
new record -- that could potential mess with any "child" records you had in
other tables.

There would be no reason you couldn't keep your Autonumber ID field, and add
a "sequence/sort" number field like Rick mentioned.