Reseting autonumber to 1 1

[rotschreck]

I need a field that is a mix of 3 other fields.<br>
<br>
1st part (1st letter of division)- I got<br>
2nd part (autonumber that resets every year) - ???<br>
3rd part (year) - I got<br>
<br>
These are examples of the # I need to produce automatically:<br>
K199<br>
K299<br>
K100<br>
K200<br>
K300<br>
K101<br>
<br>
The seperation are: K 1 99<br>
<br>
Yes, I know about the problem of when it gets to 2099, but I'll worry about that after i figure how to reset the autonum every time the year changes.<br>
<br>
Any ideas? <p> <br><a href=mailto: > </a><br><a href=

http://www.geocities.com/siltharin>Rotschrck's

Eclectic Page</a><br>

 

[DougP]

Yes<br>
Of course it can't be done with one autonumber field.<br>
But I have this routine to keep Track of numbers.<br>
<br>
'open table and get the last RA number used<br>
Dim MyDB As Database, MySet As Recordset<br>
Set MyDB = DBEngine.Workspaces(0).Databases(0)<br>
Set MySet = MyDB.OpenRecordset(&quot;Last-RA-Number&quot<br>
MySet.MoveFirst<br>
MySet.Edit<br>
Temp1 = MySet.Fields(&quot;Last_RA_Number&quot<br>
Temp2 = Temp1 + 1<br>
MySet.Fields(&quot;Last_RA_Number&quot = Temp2<br>
MySet.Update<br>
MySet.Close<br>
MyDB.Close<br>
<br>
So you have a table with 2 or 3 fields and only one record.<br>
The number is updated and written over itself everytime a new number is needed.<br>
<br>
Of course this example is just for a number.<br>
But you can have another field to track the letters.<br>
So you open the database get number part, get the letter part and add them together.<br>
<br>
MyNumber = LetterPortion & Temp1 & Temp3<br>
Which will end up K101<br>
<br>
Now if you need to incremant a letter use convert it to it's ASCII value increment it one the convert it back to Alpha and put it back in the table.<br>
<br>
like so<br>
MyLetter = Chr(Asc(rst.Fields(&quot;LetterField&quot) + 1)<br>
<br>
OK<br>
<br>
<p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>

 

[rotschreck]

Couple of questions about your answer..<br>
<br>
Where would I put this code? In a module or straight into a table...and where in the table?<br>
<br>
I think I must have written my original question wrong...<br>
The letter must depend on the division (another field in the record)<br>
The last 2 numbers are the year (which can be taken from the date field in the record)<br>
<br>
does this help?<br>
<br>
thanks <p> <br><a href=mailto: > </a><br><a href=

http://www.geocities.com/siltharin>Rotschrck's

Eclectic Page</a><br>