Restarting AutoNumber

[KyleL]

Hi all --
i have an AutoNumber field in a form to keep track of the number of enteries
per year, however, i've created a few test forms, and they've been
AutoNumber'ed, but, even after i delete the records out of the table, the
autonumber stays the same, and now the next record that will be entered will
be AutoNumbered '5'. How do i reset the AutoNumber so that when the first
'real' report is added it's numbered '1'??

 

[Paolo]

Hullo again,

Right click the table name, choose copy.
Right click in an empty space and choose paste.
In the window that'll appear choose structure only and give a name to the
new table.

In this way the autonumber counter of the new table is resetted.

HTH Paolo

 

[Klatuu]

You should not be using Autonumbers for meaningful data. Autonumbers are not
for human consumption. They should be used only for primary keys and
relating tables. If you need to have sequential numbers in your table that
are meaningful, you should create your own sequencing. Here is a routine
that will increment a number based on the year.

Me.txtEntryNumber = Nz(DMax("[EntryNumber]", "MyTableName", "[EntryYear
= " & Me.txtEntryYear), 0) +1

It finds the highest number in the table for the selected year and adds 1 to
it. The Nz function is so that for the first entry of a year, there will be
no matching year and the DMax will return null. The Nz converts the Null to
0. Then it adds the 1 and becomes number 1.

 

[XP]

I think the following will reset the counter, change the table and field names:

Dim sSQL as String
sSQL = "ALTER TABLE myTable ALTER COLUMN myField COUNTER(1,1);"
CurrentProject.Connection.Execute sSQL

HTH

 

[KyleL]

okay, just some help on the application,
i have a table called Incident Report, and the form that goes along with it
is named alike
so this is the code i would enter?

Me.txtEntryNumber = Nz(DMax("[EntryNumber]", "Incident Report", "[EntryYear
= " & Me.txtEntryYear), 0) +1

or do i need to enter values for EntryNumber and Entry Year
also, i obviously need to create a field in the table, is that what the
Entrynumber above is?

You should not be using Autonumbers for meaningful data. Autonumbers are not
for human consumption. They should be used only for primary keys and
relating tables. If you need to have sequential numbers in your table that
are meaningful, you should create your own sequencing. Here is a routine
that will increment a number based on the year.

Me.txtEntryNumber = Nz(DMax("[EntryNumber]", "MyTableName", "[EntryYear
= " & Me.txtEntryYear), 0) +1

It finds the highest number in the table for the selected year and adds 1 to
it. The Nz function is so that for the first entry of a year, there will be
no matching year and the DMax will return null. The Nz converts the Null to
0. Then it adds the 1 and becomes number 1.
--
Dave Hargis, Microsoft Access MVP

Hi all --
i have an AutoNumber field in a form to keep track of the number of enteries
per year, however, i've created a few test forms, and they've been
AutoNumber'ed, but, even after i delete the records out of the table, the
autonumber stays the same, and now the next record that will be entered will
be AutoNumbered '5'. How do i reset the AutoNumber so that when the first
'real' report is added it's numbered '1'??

 

[Dirk Goldgar]

Hi all --
i have an AutoNumber field in a form to keep track of the number of
enteries
per year, however, i've created a few test forms, and they've been
AutoNumber'ed, but, even after i delete the records out of the table, the
autonumber stays the same, and now the next record that will be entered
will
be AutoNumbered '5'. How do i reset the AutoNumber so that when the first
'real' report is added it's numbered '1'??

It shouldn't matter, because as Klatuu has said, autonumbers shouldn't be
used for meaningful information. If you really want to, out of obsessive
neatness, maybe (and I'm not immune to that), XP has posted a way to reset
the autonumber from code.

As an alternative, in most versions of Access, emptying the table and then
compacting the database has this same effect. It won't work if there are
any records in the table, though.