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

AutoNumber filling in gaps

P: n/a
JW
Hi,

This is really weird... I have a database that has an autonumber field.
Records 1-70 were deleted. Various things have changed to the front end and
a few extra fields added to the table.

Now Access has decided to re-use 1-70 again!!! however this went unnoticed
until it tried to have 2 x 71's !!

Has anyone had this before / know a reason / know a workaround??

Thanks, Jon
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Is the Allow Duplicates property for the field set to On?

Pavel

JW wrote:

Hi,

This is really weird... I have a database that has an autonumber field.
Records 1-70 were deleted. Various things have changed to the front end and
a few extra fields added to the table.

Now Access has decided to re-use 1-70 again!!! however this went unnoticed
until it tried to have 2 x 71's !!

Has anyone had this before / know a reason / know a workaround??

Thanks, Jon

Nov 12 '05 #2

P: n/a
JW
Field Size: Long Integer
New Values: Increment
Indexed: Yes (No Duplicates)

Jon

"Pavel Romashkin" <pa*************@hotmail.com> wrote in message
news:3F***************@hotmail.com...
Is the Allow Duplicates property for the field set to On?

Pavel

JW wrote:

Hi,

This is really weird... I have a database that has an autonumber field.
Records 1-70 were deleted. Various things have changed to the front end and a few extra fields added to the table.

Now Access has decided to re-use 1-70 again!!! however this went unnoticed until it tried to have 2 x 71's !!

Has anyone had this before / know a reason / know a workaround??

Thanks, Jon

Nov 12 '05 #3

P: n/a
I had this happen for no apparent reason a few months ago. The
autonumber skipped some numbers, then went back and used the lower
numbers and generated errors when it reached the higher number and
tried to use it twice.

I had not deleted a batch of records, however I do delete records one
at a time fairly often to correct user entry mistakes.

I fixed this by copying the table, deleting and re-inserting the
autonumber ID field, and re-naming the table to the same name as
before. I don't use the autonumber field for anything, it's only for
the database to use internally, so this didn't cause me any problems
with links or othe number applications.

Another approach might be to copy the table into Excel, fix the
numbers, then copy it back into the table.

Julia

"JW" <jon_webb@#NOSPAM#yahoo.com> wrote in message news:<bn*********@sparta.btinternet.com>...
Field Size: Long Integer
New Values: Increment
Indexed: Yes (No Duplicates)

Jon

"Pavel Romashkin" <pa*************@hotmail.com> wrote in message
news:3F***************@hotmail.com...
Is the Allow Duplicates property for the field set to On?

Pavel

JW wrote:

Hi,

This is really weird... I have a database that has an autonumber field.
Records 1-70 were deleted. Various things have changed to the front end and a few extra fields added to the table.

Now Access has decided to re-use 1-70 again!!! however this went unnoticed until it tried to have 2 x 71's !!

Has anyone had this before / know a reason / know a workaround??

Thanks, Jon

Nov 12 '05 #4

P: n/a
JW
I did the same (ish) t ofix it - I just wandered why it happened and how to
prevent it!!

Jon

"Julia Baresch" <jb******@oldrepublic.com> wrote in message
news:50**************************@posting.google.c om...
I had this happen for no apparent reason a few months ago. The
autonumber skipped some numbers, then went back and used the lower
numbers and generated errors when it reached the higher number and
tried to use it twice.

I had not deleted a batch of records, however I do delete records one
at a time fairly often to correct user entry mistakes.

I fixed this by copying the table, deleting and re-inserting the
autonumber ID field, and re-naming the table to the same name as
before. I don't use the autonumber field for anything, it's only for
the database to use internally, so this didn't cause me any problems
with links or othe number applications.

Another approach might be to copy the table into Excel, fix the
numbers, then copy it back into the table.

Julia

"JW" <jon_webb@#NOSPAM#yahoo.com> wrote in message

news:<bn*********@sparta.btinternet.com>...
Field Size: Long Integer
New Values: Increment
Indexed: Yes (No Duplicates)

Jon

"Pavel Romashkin" <pa*************@hotmail.com> wrote in message
news:3F***************@hotmail.com...
Is the Allow Duplicates property for the field set to On?

Pavel

JW wrote:
>
> Hi,
>
> This is really weird... I have a database that has an autonumber field. > Records 1-70 were deleted. Various things have changed to the front
end and
> a few extra fields added to the table.
>
> Now Access has decided to re-use 1-70 again!!! however this went

unnoticed
> until it tried to have 2 x 71's !!
>
> Has anyone had this before / know a reason / know a workaround??
>
> Thanks, Jon

Nov 12 '05 #5

P: n/a
As far as I know it's just random corruption. It happened just before
I implemented a front-end back-end split, and one of the reasons I did
the split was to help prevent corruption. Before the split we had
corrupted records or fields every few weeks, and this was the third
corruption problem that surfaced while I was working on the split.

We haven't had nearly as much corruption since the split, in fact, I
can't remember having any. The split also prevented that annoying
"form has been changed" message when all users were in the same forms.

"JW" <jon_webb@#NOSPAM#yahoo.com> wrote in message news:<bn**********@sparta.btinternet.com>...
I did the same (ish) t ofix it - I just wandered why it happened and how to
prevent it!!

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.