Fixing autonumbering

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to fix an autonumbering system that used to be the standard
increment as numbers are added. Somehow these dingbats rearranged it by name.

I tried things on a test table. Deleting the autonumbering field. Sorting the
table by date start. When I saved and reopened it was still sorted by date
start. Then I added a new autonumber field. But the strange thing is it sorted
back to name. I moved the date start field to the far left before doing this.
Why doesn't it let me add autonumbers with a date start sort?
(e-mail address removed)
 
I'm trying to fix an autonumbering system that used to be the standard
increment as numbers are added. Somehow these dingbats rearranged it by name.

I tried things on a test table. Deleting the autonumbering field. Sorting the
table by date start. When I saved and reopened it was still sorted by date
start. Then I added a new autonumber field. But the strange thing is it sorted
back to name. I moved the date start field to the far left before doing this.
Why doesn't it let me add autonumbers with a date start sort?
(e-mail address removed)
Click to expand...

You have a very basic misconception here. A table HAS NO ORDER. It's
an unordered "bag" of data. Sorting the table just sorts the DISPLAY
of the table, not what's stored in the table.

Secondly, you're misusing autonumbers. An Autonumber has one purpose
ONLY - to provide a (almost) guaranteed unique ID. Autonumbers are not
guaranteed to be sequential, or gapless, or even strictly ascending -
they can become random, and after you add two billion odd they will
become negative.

If you want to sort your data by date, create a Query sorting it by
date. If you want to sort by name, sort the Query by name. Base your
report or form on the Query and don't worry about "how the table is
sorted" - because it's arbitrary anyway.
 
Well usally it is based on data entry. A new record gets a higher number. Now
there are about 6400 alphabetical records by lasat name and the rest in order
of entry. It would be nice to have it all based on order of entry so there
would be some consistency.
 
As John explained, the order you enter has little/nothing to do with the
order Access uses. If you need to keep track of order of entry, consider
either creating your own "custom autonumber" (a misnomer, but you get the
drift), or a datetime field that gets Now() as its default value.

Better still, use a form for data entry and set the datetime for this field
in the BeforeUpdate event.

Good luck

Jeff Boyce
<Access MVP>
 
Well usally it is based on data entry. A new record gets a higher number. Now
there are about 6400 alphabetical records by lasat name and the rest in order
of entry. It would be nice to have it all based on order of entry so there
would be some consistency.
Click to expand...

Access will add the next new record to your table *wherever there
happens to be room*. This will often be at the end of the table, *but
you have NO guarantee that it will*!!!

Again - a table HAS NO ORDER. An autonumber HAS NO MEANING.

If you want to see records sorted in order of data entry, put a Custom
Counter field in the table, using VBA code in the data entry form to
increment it; or put a Date/Time field in the table defaulting to
Now(), and create a Query sorting on this field to display your data.
If you want to see the same records in alphabetical order, create a
Query sorting by lastname and then firstname. If you want to look at
the table datasheet, you must - no choice!! - simply abandon any
expectation of the order of the records.
 
Back
Top