AutoNumber but reset when it gets to 10,000??

[natemclain]

How can I setup a field so that it acts like a autonumber that increments by 1 but resets itself when it gets to 10,000?? What this is for are labels that get used to schedule for part runs. And I have to setup a field that is a labelnumber that has info like CompanyName, Partnumber, DueDate, etc. But I need to have it so that when I go to schedule the parts for a run. I would just put in the labelnumber until I had the right weight to make a full run.
So basically what Im looking for is code I can use that will count from 1-10,000 and reset to 1 again. And just increment by 1 each time.

 

[Skeptik]

hi!!
i had to do something like that once!!
i had to buil up a table, that i named "Counter" with a number type field that i also named "Counter".
create a command button and put the following code in it, on the "On Click" event.needless is to say that the command button should be on the form in wich you are working. you should know that this code was written to save the data after you insert it!

dim db as database
dim rs as recordset

set db = currentdb()
set rs = db.openrecordset("counter", dbopendynaset)

with rs
.lockedits = true
.edit
!counter = !counter + 1
If !counter > 9999 then
!counter = 1
end if
fieldname = !counter
.update
.lockedits = false
end with

PS: fieldname is the name of the field on the Form!

hope it works!! Good Luck!