Resetting/formatting an AutoNumber

[Guest]

Hi guys,
Sorry if i have posted this to the wrong group, i am new here so dont shoot me, but i thought my question was a lil advances for "new users".

I run a service department and have "inherrited" a database from the previous manager that has been in use for 5yrs+, has 50,000+ records and is over 100mb is size. It works fine and i want to keep it. I deleted all the records, but the damn counter is at the current value and i cannot reset it. I had a brain child to start the counter (autonumber) with the Year. ie. Starting Jan1 2004, the first job booked in will be say: 20040001. My question, where is this variable stored, i have tried editing it in it's properties. Is it just as simple as adding 20040000 to the number and resetting it to 1 ?

 

[Guest]

Hey guys

A lil research and sweat.... i am ½ way there...

Ok, i have managed to reset the number, but how do i add 2004000 to the beginning of the AutoNumber ?

 

[Tom Wickerath]

Hi Taras -

John Vinson recently posted an excellent answer to a similar question:
Re: When deleting address and wanted the ID to be reduce too
Date: Friday, December 19, 2003 6:54 PM

I have copied his post below. There are ways of implementing your brain child idea, but
not with an autonumber data type.

Tom
*************************
John Vinson's post:

You're apparently confusing an Autonumber primary key with a count. IT
ISN'T. An Autonumber has one purpose and one purpose ONLY - to provide
an almost-guaranteed unique key.

For this reason, once a record is assigned an autonumber, that number
is "used up" and will never be reassigned. Similarly, the value in a
record will remain the same no matter how many other records are
deleted or changed.

Consider a typical multitable database: if you had an address table
linked to thirteen other tables, and instead of 131 addresses you had
224981, your suggestion would require that when Address 2 was deleted,
you would edit every single one of the other 224978 records... and all
the records in all the linked tables, and all the printouts which have
those numbers on paper, and all the pencil-written Post-It notes, and
all the brains containing a memory "I've got to go fix that mistake in
Address 33128 tomorrow".

If you want to count records, use a Totals query and count records.
Don't attempt to use an Autonumber for this purpose!



_______________________________________


Hi guys,
Sorry if i have posted this to the wrong group, i am new here so dont shoot me, but i
thought my question was a lil advances for "new users".

I run a service department and have "inherrited" a database from the previous manager that
has been in use for 5yrs+, has 50,000+ records and is over 100mb is size. It works fine
and i want to keep it. I deleted all the records, but the damn counter is at the current
value and i cannot reset it. I had a brain child to start the counter (autonumber) with
the Year. ie. Starting Jan1 2004, the first job booked in will be say: 20040001. My
question, where is this variable stored, i have tried editing it in it's properties. Is it
just as simple as adding 20040000 to the number and resetting it to 1 ?

 

[Tom Wickerath]

Hi,

Anybody can help me?
I need a table to generate autonumber with specific format of number but not
using access default increment autonumber, for example: 0311-0001 and next
no. 0311-0002 where 0301 mean year month and behind is running no.

Thk,

Pat

This is called an "Intelligent Key" - and unfortunately that's not a
compliment. Storing data, such as a date, in a field along with other
data is generally considered unwise; fields should be "atomic", i.e.
have only one indivisible value. This kind of key should ONLY be used
for compatibility with an existing manual system.

If you are going to use this key, I'd suggest using *two* fields - a
four byte text field for the month and year (I'll call it KeyYYMM) and
an Integer for the sequential portion (KeyN). You'll need to do all
your data entry using a Form - table datasheets don't have any usable
events. In the Form's BeforeInsert event you'll want code like this:

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim strYYMM As String
strYYMM = Format(Date(), "yymm") ' generate 0311, frex
Me!txtKeyYYMM = strYYMM
Me!txtKeyN = 1 + NZ(DMax("KeyN", "yourtable", "[KeyYYMM] = '" & _
strYYMM & "'"))
End Sub

This will sometimes run into problems if two users are simultaneously
creating new records in a shared database; more elaborate schemes can
be devised to avoid such problems.



_______________________________________


Hey guys,

A lil research and sweat.... i am ½ way there....

Ok, i have managed to reset the number, but how do i add 2004000 to the beginning of the
AutoNumber ?

 

[Guest]

Sorry to trouble you guys, worked it all out !!!

But i do kow where to go next time i have a problem. I am slo willing to help where i can

Thanx

 

[Victor Delgadillo]

There's no decent way to 'manage' the autonumbers. If you really want to
have control over the numbering, use your own numbering. A simple one-line
function such as:

Public MyAutoNumber(field as Long, table as String) as Long
MyAutoNumber = DMax("[field]","table") + 1
End Function

This can be called from any form =MyAutoNumber ([field],"TableName") and it
will return one number above the highest number previously used.

Autonumber function is just supposed to issue a 'unique' number with no
repeats for indexing purposes, but never to be under the control of the user
of the db.

--
Victor Delgadillo MS-MVP Access
Miami, Florida

Mensajes a los grupos de noticia, asi todos nos beneficiamos!

Hi guys,
Sorry if i have posted this to the wrong group, i am new here so dont

shoot me, but i thought my question was a lil advances for "new users".

I run a service department and have "inherrited" a database from the

previous manager that has been in use for 5yrs+, has 50,000+ records and is
over 100mb is size. It works fine and i want to keep it. I deleted all the
records, but the damn counter is at the current value and i cannot reset it.
I had a brain child to start the counter (autonumber) with the Year. ie.
Starting Jan1 2004, the first job booked in will be say: 20040001. My
question, where is this variable stored, i have tried editing it in it's
properties. Is it just as simple as adding 20040000 to the number and
resetting it to 1 ?

 

[Elsa]

If you have deleted all the information in the table, you
can just compact and repair the database and the numbers
would start at 1 again.

-----Original Message-----
Hi Taras -

John Vinson recently posted an excellent answer to a similar question:
Re: When deleting address and wanted the ID to be reduce too
Date: Friday, December 19, 2003 6:54 PM

I have copied his post below. There are ways of

implementing your brain child idea, but

not with an autonumber data type.

Tom
*************************
John Vinson's post:

You're apparently confusing an Autonumber primary key with a count. IT
ISN'T. An Autonumber has one purpose and one purpose ONLY - to provide
an almost-guaranteed unique key.

For this reason, once a record is assigned an autonumber, that number
is "used up" and will never be reassigned. Similarly, the value in a
record will remain the same no matter how many other records are
deleted or changed.

Consider a typical multitable database: if you had an address table
linked to thirteen other tables, and instead of 131 addresses you had
224981, your suggestion would require that when Address 2 was deleted,
you would edit every single one of the other 224978 records... and all
the records in all the linked tables, and all the printouts which have
those numbers on paper, and all the pencil-written Post- It notes, and
all the brains containing a memory "I've got to go fix that mistake in
Address 33128 tomorrow".

If you want to count records, use a Totals query and count records.
Don't attempt to use an Autonumber for this purpose!



_______________________________________


Hi guys,
Sorry if i have posted this to the wrong group, i am new here so dont shoot me, but i
thought my question was a lil advances for "new users".

I run a service department and have "inherrited" a

database from the previous manager that

has been in use for 5yrs+, has 50,000+ records and is

over 100mb is size. It works fine

and i want to keep it. I deleted all the records, but the damn counter is at the current
value and i cannot reset it. I had a brain child to

start the counter (autonumber) with

the Year. ie. Starting Jan1 2004, the first job booked in will be say: 20040001. My
question, where is this variable stored, i have tried

editing it in it's properties. Is it