Gaps in Form "Record Number Boxes"

[EricRod]

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

------
:

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
-------

 

[Rick B]

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

 

[EricRod]

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.

Rick B

 

[gls858]

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.

Rick B

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

 

[George Nicholson]

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.

--
George Nicholson

Remove 'Junk' from return address.

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?

 

[EricRod]

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