Gaps in Form "Record Number Boxes"

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

EricRod - 04 Mar 2005 18:51 GMT

I don't mind the gaps in the record number sequences that happen when I
delete old records, except that it makes it difficult to lookup records, when
using a Data Entry form. For example, say I currently have a sequence of
records numbered like this:

1, 2, 3, 4, 5, 10, 11, 12...

And say I have deleted old records # 6, 7, 8, and 9.

The problem now is that when I use the VCR buttons at the bottom of my Data
Entry Form, Access 2003 won't let me scroll past record #5 using the "Down
One Record" arrow button (>). In the Form, it is as if records 10, 11, 12...
don't exist. Of course, they really do, and I can see the data if I run a
report.

But it would be very convenient if I could quickly scroll through *all* the
records in my database, using my Data Entry Form. It's an easy way for me to
quickly find an email address or a telephone number for a specific customer.

What are my alternatives? How do I adjust my Data Entry Form so that the
VCR Buttons at the bottom of the screen, jump past the "gaps" in the record
number sequence?

Or, do I just need to generate a report (or examine a table) everytime I
want to view a piece of information?

BTW, this Group is great. Thanks.

-EricRod

------
"Neil www.nwarwick.co.uk" wrote:

This is normal behaviour for Access.

Once an Autonumber entry has been assigned to a record, it can never be used
again for a different record, so as you have found, if a record is deleted
then another one added there is a gap in the Autonumber sequence.

This isn't usually a problem, as the Autonumber field is designed only to
provide a unique key for each record and is not intended to be used by the
user.
However, it is unfortunately quite common to see databases written where
people have used the Autonumber as part of their record data, maybe to
provide a serial number to a paper form etc, and they then have gaps in their
numberiing sequence.

If this is your situation, then I'm afraid you will have to live with gaps
in your sequence until you devise another way to number your forms.

HTH

Neil
www.nwarwick.co.uk
-------
"EricRod" wrote:

> How do you get AutoNumber to reset itself once you delete a record? Say that
> I have a series of records in a table:
>
> 1, 2, 3, 4, 5…
>
> But I then delete record #5 from the table, and later add a new record to
> the table. Shouldn't Access re-use the number "5" ? Instead, Access is
> giving me a record sequence like this:
>
> 1, 2, 3, 4, 6…

Signature

Best regards,
EricRod

Reply to this Message

Rick B - 04 Mar 2005 19:02 GMT

The record numbers in the record navigation box will be sequential and will
include all records in the record set upon which your form is based.
Deleting records will reduce this number, but you will never see gaps there.
This field has nothing to do with a field in your database where you may
have store a 'record number'.

The VCR buttons you are talking about are record navigation buttons and will
scroll through all the records in your form's record set.

Rick B

> I don't mind the gaps in the record number sequences that happen when I
> delete old records, except that it makes it difficult to lookup records, when
[quoted text clipped - 63 lines]
> >
> > 1, 2, 3, 4, 6.

Reply to this Message

EricRod - 04 Mar 2005 20:03 GMT

Thanks, Rick.

For some reason, my VCR buttons *don't* allow me to completely cycle through
all of my available table records. They stop at the point of the first
deleted record in the sequence. I had hoped that the VCR buttons would
"jump" past any deleted records, and allow me to view the entire full table
of records. Strange, huh?

> The VCR buttons you are talking about are record navigation buttons and will
> scroll through all the records in your form's record set.
[quoted text clipped - 79 lines]
> > >
> > > 1, 2, 3, 4, 6.

Reply to this Message

gls858 - 04 Mar 2005 20:15 GMT

> Thanks, Rick.
>
[quoted text clipped - 108 lines]
>>>>
>>>>1, 2, 3, 4, 6.

Have you tried a compact and repair?
Tool > Database Utilities > Compact and repair
I always make sure to have a copy saved before
I do the procedure.

gls858

Reply to this Message

EricRod - 05 Mar 2005 02:01 GMT

That s

That seems to have fixed the problem. What a useful utility. Thank you!
-EricRod

> Have you tried a compact and repair?
> Tool > Database Utilities > Compact and repair
> I always make sure to have a copy saved before
> I do the procedure.
>
> gls858

> > Thanks, Rick.
> >
[quoted text clipped - 115 lines]
>
> gls858

Reply to this Message

George Nicholson - 04 Mar 2005 23:12 GMT

Are you using the built-in navigation buttons or some custom control? If
it's a custom control then it sounds like the code behind it is assuming
sequential record numbers. Very bad assumption. The built-in controls (and
properly built custom controls) won't do this.

Signature

George Nicholson

Remove 'Junk' from return address.

> Thanks, Rick.
>
[quoted text clipped - 105 lines]
>> > >
>> > > 1, 2, 3, 4, 6.

Reply to this Message