Numbering in automatic increments

[lm0304]

I need to use a specific numbering scheme, 2002-xxx, where xxx would automatically start with 001 and then everytime a new form was entered, the number would increase by 1 (such as 2002-001, 2002-002, etc.) The user would not type in this number, it would automatically give the user the next available "sequential" number.

 

[smedvid]

First I would recommend seperating the Key field into two fields. I've used intelligent concatonated fields before and from my experience having seperate fields is more managable.

Now, to get an automated counter you can write a simple little routine that finds the Maximum (count). I've taken a couple of approaches in the past to accomplish this task. Here is some psuedo code...


Function GetNextValue(lcYear as String) as String
Dim lvTVar
Dim lnTNum

lnTNum=DMax("[cntfield]","table","[Yearfield] = " & lcYear)
lnTNum = Len(lnTNum)
IF lnTNum = 1 then
GetNextValue = "000" & lnTNum
ElseIf lnTNum = 2 then
GetNextValue = "00" & lnTNum
ElseIf lnTNum = 3 then
GetNextValue = "0" & lnTNum
Else
GetNextValue = lnTNum
Endif
End Function
htwh Steve Medvid
"IT Consultant & Web Master"

http://users.erols.com/medvid/

 

[Taff07]

Are you after the lowest Unused number so far ( If records are delted and free up an old number) or are you just after the next highest number in the table.

Form above i`ll assume it`s the highest. Use the following function to work out the Max Id currently used and generate the next new one.
-----------------------------
function NextId()

dim rst as recordset
dim strSql as string

strSql = "SELECT Max(Table.Id) AS TopID FROM Table"

set rst = currentdb.openrecordset(SQL)

nextid = rst.fields("Topid&quot.value + 1

end function
-----------------------------

This should return the next increment Id from your Id field. You just need to call this from the appropriate place and assign the reutrned value to your control

I have tested the above but as far as I can tell it should work.

Ian