How do I renumber a table?

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

Guest

Hi, A newbie question.
Have been working on a Db for some time and have a bast table that I am
using for testing. I have imported data from EXCEL on several occasions and
deleted random files. My autonumber sequence is now not linear. For example
record numbers 1 to 35 are numbered correctly but then it jumps to 149
through 160 then 203 to 299 etc.

How can I renumber these back to 1 through 96 (the total records in the
table).

I want to use DoCmd.GoToRecord , , acGoTo, RecNumb where RecNumb is a record
number based on the Me.ID of this table.

BTW the csGoTo, n appears to jump 'n' records rather than to a specific
record. Am I suing this right?

Any help is much appreciated. I have learnt a lot from the newsgroups
already so thanks for all of that.

Cheers

Chas Large
 
Hi Chas.

The primary key of the table is a unique identifier that is designed to stay
constant, even if records are added or deleted. That means you MUST use a
design and code that copes with non-contiguous numbers.

Jumping to a particular 'record number' is a poor approach because:
a) It fails as soon as any record is deleted.
b) The records might be sorted differently (e.g. right-click any field to
sort by that field.)
c) The records might be filtered, in which case there will be missing
numbers.
d) The records might be joined to other tables, which yields duplicates
(related table) or missing numbers (lookup table.)

So, how do you work in Access without using record numbers like that? Use
FindFirst instead of RecNumb.

For an explanation and code examples, see:
What, no record numbers?
at:
http://allenbrowne.com/xbase-03.html
 
Dear Allen,
Many thanks for the help. I have got my form to work with the
=[Form].[CurrentRecord] command. I know it's bad practice to refer to record
numbers but it's a special case and this has provided a complete fix.
Thanks again.

PS Sent my love to Armadale, I have rellies there.
Chas
 
Back
Top