Autonumber based on year

[DKS]

Hi,

PROBLEM DESCRIPTION
----------------------------
For legacy reasons, I have a system that used to create a "unique id" for
each added record as follows:

yyyy-nnn

where yyyy is the year of the record, and nnn is a serial number starting
from 001 and incremented by 1 for each new record.

Of course, this is not necessarily if we build an "autonumber" from Access.
But for legacy reasons this is done, and unfortunately many queries and other
functions are already using this feature and so difficult to replace this
"old" mechanism by a simple AUTONUMBER field.

MY REQUIREMENT
---------------------
This "unique id" is filled in manually on the form for record creation.
Thus the form has an inbuilt query that gives the last number used (nnn) for
the year in question, and the user must manually fill in the new yyyy-nnn
field.

I would like this filling up to be automatic, but so far I have not managed
to make it work. My need is that when the record is saved, this field must
be automatically calculated and filled in (it is saved in the table).

I have no problems to use a "parameters" style table that stores the last
used value for "nnn" to permit an easy lookup. But somehow I cannot get this
to work.

any ideas/suggestions?

Many thanks in anticipation.

 

[Alex Dybenko]

Hi,
you can use entry form's BeforeUpdate event to get last number used and then
set number+1 to a new record.
But I suggest to have autonumber as a primary key anyway, then if business
rules get changed - you don’t need to rebuild queries, etc


--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[John Spencer]

You should be able to use an expression like the following to get the
LAST Uniqueid for any specific year.

NZ(DMAX("Right(UniqueID,3)","YourTable","UniqueID like '" &
Year(Date()) &"*'"),"000")

Using that in a public function like the UNTESTED one below.

Public Function fGetNextID() as String
Dim X as String
X =NZ(DMAX("Right(UniqueID,3)","YourTable", _
"UniqueID like '" & Year(Date()) &"*'"),"000")

fGetNextID = Year(Date()) & "-" & Format(Val(X) + 1,"000")
End Function

You can call the function from whichever form event you want. If you
want the number generated just prior to saving the record that would be
the BEFORE update event or after insert Event of the form - of course
you would only want to call the function if the record DID NOT have a
unique id.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[DKS]

Alex, John

Thanks for your help. I will try it out.

 

[Dale Fye]

The problem with using a single field for this is that because you are using
a string, the values will not sort properly if you end up with more than 999
unique IDs during a given year. When you sort:

2008-100
2008-999
2008-1000

it will sort like:

2008-100
2008-1000
2008-999

Unfortunately, using DMAX on this will return the "999" value every time
(after you reach that value), and you would generate multiple non-unique
UniqueID values that look like "2008-1000"

So instead of using the function that John recommended, I think I would
probably use something like:

PublicFunction fGetNextID() as String

Dim strSQL as string
Dim rs as DAO.Recordset

strSQL = "SELECT Max(Val(Mid([UniqueID],6))) AS MaxVal " _
& "FROM yourTable " _
& "WHERE UniqueID Like " & Year(date()) & "-[0123456789]*"
set rs = currentdb.openrecordset strsql

if rs.eof then
X = 1
else
X = rs("MaxVal") + 1
endif

rs.close
set rs = nothing

fGetNextID = Year(Date, "yyyy-") & Format(X, "000")

End Function

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Dale Fye]

Also, keep in mind that if this is a multi-user application, and more than
one user might be creating a new record at the same time, I would strongly
recommend that you not generate this number until you are about to save the
record.

If you do it before then, then multiple people could generate the same value.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[FF Diz]

How do I place the public function in the database. I have used fOSusername()
as a module and call it up as a default value. But I need more information
inorder to put this and call it up when I need it.

Can you help, I am VB illiterate.