Record auto numbering

I suppose I shouldn't be concerned, but my record autonumbering jumped from
135 to 432,233 when I was trying to copy and paste common info. Any way to
reset? Access Rookie

If the numbers don't have "meaning" nor need to be sequential, no problem.
Actually if they needed to be sequential or have meaing, there would be a
problem as autonumbers often aren't as you just found out.

Still that is quite a jump. Copying and pasting can be a problem. It's
better to import instead.

You could empty the table, like exporting to Excel, then do a compact and
repair. Next import the records back. But it really isn't worth the effort.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Hondo" wrote:

I suppose I shouldn't be concerned, but my record autonumbering jumped from
135 to 432,233 when I was trying to copy and paste common info. Any way to
reset? Access Rookie

Hello,
Something like :
Public Sub InitNumAuto(A_Table As String, NumAuto As String)
'Ex : InitNumAuto "Table1","Num"
Dim Rst As DAO.Recordset, SqlStr As String
On Error Resume Next
SqlStr = "SELECT * FROM " & A_Table
Set Rst = CurrentDb.OpenRecordset(SqlStr)
With Rst
' Delete all records
.MoveFirst
Do Until .EOF
.Edit
.Delete
.MoveNext
Loop
' Update field "AutoNumber"
.AddNew
.Fields(NumAuto) = 0 'To start at 1, 99 to start at 100, ...
.Update
' Delete first record
.MoveFirst
.Edit
.Delete
.Update
.Close
End With
Set Rst = Nothing
End Sub
Bye
"Hondo" a écrit dans le message de news:
...
I suppose I shouldn't be concerned, but my record autonumbering jumped from
135 to 432,233 when I was trying to copy and paste common info. Any way
to
reset? Access Rookie

On Tue, 23 Feb 2010 08:00:01 -0800, Hondo
wrote:

I suppose I shouldn't be concerned, but my record autonumbering jumped from
135 to 432,233 when I was trying to copy and paste common info. Any way to
reset? Access Rookie

Don't bother.

Autonumbers have one purpose, and one purpose only: to provide a meaningless
unique identifier for a record. They'll always have gaps. They have a range up
to two billion odd (2^31 - 1) and will then pick up at negative two billion
odd and count up toward zero.

Copy and paste can introduce large gaps, as you have seen; for this and for
other reasons, it's often preferable to use Append queries or other techniques
to add new records. Where are you copying and pasting from?

For what it's worth, the only way to get back to gapless sequential numbers is
to create a new table, with an autonumber ID; and run an Append query
appending all the fields except for the ID. If your table is related to other
tables this can get complicated because you'll need to be sure that the
records link up.
--

John W. Vinson [MVP]

On Tue, 23 Feb 2010 17:23:18 +0100, "jero" wrote:

Hello,
Something like :
Public Sub InitNumAuto(A_Table As String, NumAuto As String)
'Ex : InitNumAuto "Table1","Num"
Dim Rst As DAO.Recordset, SqlStr As String
On Error Resume Next
SqlStr = "SELECT * FROM " & A_Table
Set Rst = CurrentDb.OpenRecordset(SqlStr)
With Rst
' Delete all records
.MoveFirst
Do Until .EOF
.Edit
.Delete
.MoveNext
Loop
' Update field "AutoNumber"
.AddNew
.Fields(NumAuto) = 0 'To start at 1, 99 to start at 100, ...
.Update
' Delete first record
.MoveFirst
.Edit
.Delete
.Update
.Close
End With
Set Rst = Nothing
End Sub
By

Hondo, just note that Jero's code will a) completely destroy all the data
currently in your table and b) will fail since Autonumber fields are not
updateable.
--

John W. Vinson [MVP]

Why does this matter?

Who's looking at the Autonumbers?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Hondo" wrote in message
news
I suppose I shouldn't be concerned, but my record autonumbering jumped from
135 to 432,233 when I was trying to copy and paste common info. Any way
to
reset? Access Rookie

"Jeff Boyce" wrote in message
...
Why does this matter?

Who's looking at the Autonumbers?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Hondo" wrote in message
news
I suppose I shouldn't be concerned, but my record autonumbering jumped
from
135 to 432,233 when I was trying to copy and paste common info. Any way
to
reset? Access Rookie