So I've got an old MS Works database I imported into Access. I needed
a primary key so I created a record number field that was just the
record number I manually inserted when I entered it in the database so
I could sort the records by the date at which they were entered.
Well now I've deleted some of those records so its of course causing
gaps in the records. The record number in Access no longer matches my
record number that I entered when created. I was wondering if it would
be possible to somehow set the record so it would automatically assign
that primary key record number field the same number as the actual
record number. So say I deleted record 86, then record 87 would become
record 86, record 88 would become record 87, and so on down the line.
I was thinking AutoNumber format might do that, but for some reason it
doesn't assign the numbers successively down the list, so record 100 is
AutoNumber 100, but record 200 gets assigned AutoNumber 225, and record
1000 gets AutoNumber 200. I'm not sure what method its using to
determine which record to number next, but it doesn't seem to work.
Any functions in Access I could use for Access to just grab the current
record number, and put that into the Record Number field I created?
That would auto update the other record numbers when I deleted a record?
