Reseting autonumber to start again from 1

[marystewart78]

Hi Folks,

I'm looking to create an autonumber field which will work normally (ie increase 1 number at a time) but at the beginning of a new year I'd like it to automatically return to the number 1 again and start increasing 1 at a time again.

Any help on this would be appreciated!

Many Thanks,

Mary

 

[richary]

Autonumber is not suitable for this. Use an ordinary Integer field and use the DMax function with the current year in the criteria string to assign a number.

 

[jsanders]

You can’t make it go back to 1 but you can make it go to something like
20051, 2006001, or something of that nature.

A lot of people here will tell you not to use an autonumber for anything else but keeping referential integrity. And they have valid points.

http://www.access-programmers.co.uk/forums/showthread.php?t=89864&highlight=paste

 

[wkalo]

you Can Copy And Paste The Table And Set copy Structure Only. So If you Try To Input A New Field It Will Start At 1

Try ...... I Did And It Works
Good Luck !!!

 

[Rich]

Mary doesn't want a new field, she wants to keep the existing one

 

[halem2]

I followed WKALO's suggestion and it does work like a charm. Thanks!

 

[wkalo]

Glad To Help Good Luck With Your Project

 

[Mile-O]

You copied and pasted the table? So now you have, what, two identical tables? Madness!

 

[halem2]

You copied and pasted the table? So now you have, what, two identical tables? Madness!

No...I copy and pasted the table structure...

 

[Mile-O]

Yes, so two identical tables...in structure. The content is irrelevant. Still madness!

 

[halem2]

Lol !!!!!!!!!!!!!!!!!

 

[durexlw]

What you did is a good example of making yourself work for your PC...

Just write some code that operates on this idea and save yourself a headache:

|-----------|
| Testtable |
|-----------|
| ID        |
| Counter   |
| Year      |
|-----------|

Counter is a number
Year is a date
ID is a custom key that operates on this idea:

if Newinsert.Year <> Lastinsert.Year then
    [Counter] = 1
    [ID] = Newinsert.Year & [Counter]
Else
    [Counter] = Lastinsert.Counter + 1
    [ID] = Newinsert.Year & [Counter]
End If

(With 'Lastinsert.' I mean, lookup the value of the last record in your table.
'Newinsert.' are the values the user is trying to insert.)

Obviously you can optimize this code, even write it in a single line as a default value rule, but you'll probably get the idea... make the PC work for you... not the other way around.