AutoNumber

[Guest]

I have an AutoNumber field in my database but Access skipped some numbers;
how do I fix this? I want all the numbers to increment by "1"...instead, it
goes from 1-43 and 154-208. I tried to change the starting number of an
AutoNumber field but it didn't work for this particular case.

Any help would be appreciated.

 

[John Vinson]

I have an AutoNumber field in my database but Access skipped some numbers;
how do I fix this? I want all the numbers to increment by "1"...instead, it
goes from 1-43 and 154-208. I tried to change the starting number of an
AutoNumber field but it didn't work for this particular case.

Any help would be appreciated.

This is in the nature of autonumbers. An Autonumber has one purpose,
and one purpose ONLY: to provide a unique key. It is not designed or
implemented to be sequential, continuous, or gap-free. Deleting a
record will leave a gap; hitting <Esc> after starting to enter a
record will leave a gap; running an Append query (as you may have
done) will leave a gap, sometimes a very large one.

If the values of the numbers are intended for human consumption and
gaps are unwanted, *don't use Autonumber*. Instead use a Long Integer
field and VBA code in the Form that you use to update the table to
increment the number. If the ID is the only required field you can use
code like this in the form's BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtID = NZ(DMax("[ID]", "[tablename]")) + 1
Me.Dirty = False ' write the new record to disk immediately
' to prevent another user from getting the same number
End Sub

John W. Vinson[MVP]

 

[Jeff Boyce]

You don't. Access autonumbers are system-generated unique row identifiers.
They have no inherent meaning, may suffer "gaps", may even be random in
order, and are unfit for human consumption.

There are special circumstances and special users that would make displaying
Autonumbers to those users less risky, but rarely! Instead, if you need a
number to show a user, use a "custom autonumber" (not a real Autonumber,
just plays one on TV). You can find out more about how to do these via a
Google.com search on this phrase.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Amy,
It is best not to use autonumber, but if you want the numbers to go back to
1, 2, 3, ect... then you must copy the table (right click the table, select
copy then right click a blank area, select paste and paste structure only.
Make the name different than the original table) then open both old table and
new table. Copy and paste the original table's information (selecting the
entire field) into the structure only table. This will reset the autonumber
sequence. Then remove the old table and rename the structure only table to
the original tables name.
Tony M

 

[Jeff Boyce]

Tony

Given the nature of relational databases (e.g., Access), "resetting" an
autonumber field in Table1 risks making related rows in Table2 into
"orphans" (i.e., their "parent"/autonumber is now missing from Table1).
Even worse, "resetting" autonumbers in Table1 can have the same effect on
related Table2 rows as switching birth certificates ... the kids have
parents, but NOT THEIRS!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

I'm having a similar problem. I want to have autonumbers so that I can later
use those numbers to put only a designated number of records into a query.

For example, if I had a list of cars on sale, and I wanted cars made after
2000, if the 2000 cars started at record 12500, my criteria for this column
could read > 12500.

I have a make table query, but I'm not sure how to autonumber it. Is there a
better way?


Thank you,
Joshua

I have an AutoNumber field in my database but Access skipped some numbers;
how do I fix this? I want all the numbers to increment by "1"...instead, it
goes from 1-43 and 154-208. I tried to change the starting number of an
AutoNumber field but it didn't work for this particular case.

Any help would be appreciated.

This is in the nature of autonumbers. An Autonumber has one purpose,
and one purpose ONLY: to provide a unique key. It is not designed or
implemented to be sequential, continuous, or gap-free. Deleting a
record will leave a gap; hitting <Esc> after starting to enter a
record will leave a gap; running an Append query (as you may have
done) will leave a gap, sometimes a very large one.

If the values of the numbers are intended for human consumption and
gaps are unwanted, *don't use Autonumber*. Instead use a Long Integer
field and VBA code in the Form that you use to update the table to
increment the number. If the ID is the only required field you can use
code like this in the form's BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtID = NZ(DMax("[ID]", "[tablename]")) + 1
Me.Dirty = False ' write the new record to disk immediately
' to prevent another user from getting the same number
End Sub

John W. Vinson[MVP]

 

[Jeff Boyce]

Joshua

If you have "cars made after 2000" in your database, do you have a
"ManufactureDate" field? Use a query to find all those with a
ManufactureDate>12/31/1999. If you don't have a [ManufactureDate] field,
but want to know about when cars were manufactured, just add the new field!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm having a similar problem. I want to have autonumbers so that I can
later
use those numbers to put only a designated number of records into a query.

For example, if I had a list of cars on sale, and I wanted cars made after
2000, if the 2000 cars started at record 12500, my criteria for this
column
could read > 12500.

I have a make table query, but I'm not sure how to autonumber it. Is there
a
better way?


Thank you,
Joshua

I have an AutoNumber field in my database but Access skipped some
numbers;
how do I fix this? I want all the numbers to increment by
"1"...instead, it
goes from 1-43 and 154-208. I tried to change the starting number of an
AutoNumber field but it didn't work for this particular case.

Any help would be appreciated.

This is in the nature of autonumbers. An Autonumber has one purpose,
and one purpose ONLY: to provide a unique key. It is not designed or
implemented to be sequential, continuous, or gap-free. Deleting a
record will leave a gap; hitting <Esc> after starting to enter a
record will leave a gap; running an Append query (as you may have
done) will leave a gap, sometimes a very large one.

If the values of the numbers are intended for human consumption and
gaps are unwanted, *don't use Autonumber*. Instead use a Long Integer
field and VBA code in the Form that you use to update the table to
increment the number. If the ID is the only required field you can use
code like this in the form's BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtID = NZ(DMax("[ID]", "[tablename]")) + 1
Me.Dirty = False ' write the new record to disk immediately
' to prevent another user from getting the same number
End Sub

John W. Vinson[MVP]

 

[Guest]

I apologize; I used the example about cars to illustrate the problem that I’m
having in an easier manner. The actual issue I’m having is with a mail
parsing system. I have a query table that eliminates customers based on
certain criterion and leaves me with 209,000 records. Because the people
using the program are not familiar with access, I want to have a user
interface where they can just type in the number of entries they want, hit
another button, and have that information sent to an excel file for them.
I’ve already got the macro sending the information to excel, but I’m not sure
how to 1) make sure they only get the first “X†entries of that query table
and 2) Make sure they don’t use the same “X†people over and over.

If you can help me with this I would VERY greatly appreciate it.

Joshua

 

[Jeff Boyce]

To get the "first" ### rows, you need to tell Access how to sort! Then you
use the TOP property to get the first ### rows.

If you need to know which ones have been selected already, you'll need to
add a field to record that fact. A Yes/No field would be enough, unless you
need to know WHEN a row was selected (in which case use a Date/Time field).

You will need to write a procedure that:
selects the top ## records
exports those to Excel
updates those records in Access to show that they were selected

Potential issues ... if the routine breaks (e.g., Excel fails for some
reason), you will want to prevent the update. You'll need to work through
your error handling routines to make sure everything worked properly.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thanks to everyone for their help. What I ended up doing, because I wasn't
too far into my creation, was just delete the column (from the table) that
used the AutoNumber. Then I went into the design view of the table and
created the same Autonumbered column again. This got rid of the gap in
numbers.

 

[Guest]

That worked perfectly. Thanks for your help.

Joshua Francis

 

[William Hindman]

Amy

....that solved your problem THIS time ...but it will most certainly reoccur
....if you need those numbers to remain sequential you need to use one of the
other solutions provided in the replies.

William Hindman