Access custom autonumber

[buzcut]

How do I code for a custom autonumber in Access where the first two digits represent the year and the last five digits are the file number, for example: 00-00123. Also, When the year changes to say 2001, the counter should start over like this: 01-00001. Thanks for all the help!

Ed Almeida
buzcut

 

[JimHorton]

Ed,
You need to first set the field to Text, of course, and a tblNumber table, with 2 fields, ID and LastID. Set ID as Primary Key, and then set validation to be = 1, create one record with ID = 1. Now only one record is possible for the table. Initialize LastID to 0, then create this function:

Public Function GetNewID() as String
dim strYr as string, strNum as string,rst as recordset,ln as integer
strYr = format(date(),"YY&quot
set rst = currentdb.openrecordset("select Lastid from tblNumber",dbopendynaset)
rst.edit
strNum = cstr(rst!lastid + 1)
rst!lastid = strNum
rst.update
ln = len(strNum)
GetNewID = strYr & "-" & format(strNum,"00000&quot
End Function

 

[JimHorton]

Ed,
In my response, I left 'dead code', the ln=Len(strNum) should be for validation--
post an error if ln > 5...sorry,
--Jim

 

[MichaelRed]

See the FAQ

In Ms. Access SEarch for "AutoNumber". It does what yoiur'e asking for. Read the explination which accompanys it.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over