By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,233 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

How to renummer ID (now contains holes), even when table is full of records.

P: n/a
I have a huge list of records and deleted a few. This means that the
ID-nummering contains holes. How to get it renummered? With Compact and
Repair Database no success.

Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Access will always use the next sequentially availible number for
autonumber.

Create a new autonumber field - this will number them all sequentially
in order of input. You can then delete the old autonum field and call
the new field the same name as the old one.

But do NOT do this if you have any foreign keys linked to this field
from other tables. In fact if the field is being used as a primary key,
it's probably not a good idea full stop. If it's just something that's
irritating you I suggest you learn to live with it. Why do you need to
do this?

Nov 13 '05 #2

P: n/a
On 15 Jul 2005 03:52:08 -0700, AA Arens wrote:
I have a huge list of records and deleted a few. This means that the
ID-nummering contains holes. How to get it renummered? With Compact and
Repair Database no success.


Don't. The AutoNumber datatype field should be used solely to generate
unique Id's. If you use a number for any other purpose you are using
the wrong method to do so. There will always be holes in the numbering
sequence. So what?

Besides, it is the ID field that is used to identify records. As soon
as you renumber, that record might no longer be associated with the
correct child (or parent) record.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
Nov 13 '05 #3

P: n/a
Then it is better to have filed "record number", copied from the
standard record field below each form.
OK, I can see the record number there. But I want to have it on my form
as well.
The ID is then actually an Access internal rerord number.

Nov 13 '05 #4

P: n/a
Then it is better to have filed "record number", copied from the
standard record field below each form.
OK, I can see the record number there. But I want to have it on my form
as well.
The ID is then actually an Access internal rerord number.

Nov 13 '05 #5

P: n/a
The record number in the nav box is generated on the fly - so if you
apply a filter, what was record 10 may now be record 2, or if you sort
the records by a field, they will all change. If you want each record
to have a fixed, unique number that is displayed on the form and has no
"holes" in the sequence, then you will have to create a new field in
your table and have it filled with code. you can write a module to find
the first availible number greated than 1 and apply it to the field.
something like:

Dim i: i = 1
do while isnull(dlookup("yourField","yourTable","Field =" & i)
i = i +1
loop
me.myfield.value = i

this would be done more efficiently with a recordset, but the above
code would work and it is simple enough to apply.

Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.