I need a type of auto number

[jackrobyn1]

i need some sort of accurate/ consecutive number assigned to each record in
my table/form these are accident reference numbers .... so important, i don't
mind how but its got to be consecutive and start from a number i decide. i
need the format to be as follows:-
RRY/08/245
the RRY should not change, the 08 represents the current year, and the 245
is the part of the whole thing i need to change record to record eg
RRY/08/245 = record 1
RRY/08/246 = record 2
RRY/08/247 = record 3
etc etc

The table is called accidents, the field i want the unique number for is
called Ref_No
When it gets to the 1st of Jan 2009 i need the records to start with:-
RRY/09/001

i hope someone can help my solve this

thanks

 

[Klatuu]

This is not that hard to do. The question is whether you have a date field
in your table that will tell you the year. If you don't have that, you need
a field to at least keep track of the year the record belongs to. The year
and the incremental number should be in different fields.

The basic technique is to use a DMax to locate the current highest number
and add one to it. This can be done in the Form Current event for new
records or if you use a command button to start a new record, it can be done
there.

NextNumber = Nz(DMax("[RefNum]","MyTable","Year([RefDate]) = " &
Year(Date)),0) + 1

Now, there are two issues to address and your business rules will determine
how you handle them. First is the Multi User situation. If there are very
few users and they are not usually entering new records concurrently, it is
not a problem, but in a heavy data entry environment, it will be a problem.
That is that one user will get the number and begin entering data. Another
user will get the same number if she begins a new record before the first
user has completed entering the data.

One way around that is to immediately save the record as soon as the number
is assigned, but that creates the second issue. The scenerio is a user
begins a new record and the number is immediately assigned and she begins
entering the data. A second user begins a new record and the next sequential
number is assigned. Now the first user determines this record should not be
completed and deletes it. You now have a gap in the sequence.

 

[Larry Linson]

What is the purpose of the value? If it is to display or print for human
readability, you do not need "a number" (which, by the way, with punctuation
and alphabetic characters, it is not). If "RRY" and the "/" also are
always constant, you can create that part and concatenate it when needed in
Reports or on Forms; thus you only need the monotonically increasing
consecutive number.

If you store it separately, you can use a DMAX domain aggregate function to
retrieve the largest and add one before storing it with the current record.
You could also use VBA DAO (Visual Basic for Applications, Data Access
Objects) code, with appropriate record locking.

A search on the archives of this, and of the USENET newsgroup
comp.databases.ms-access, at http://groups.google.com should turn up several
examples. Just one example... very similar to your request... found just by
searching on "DMAX"... is at
http://groups.google.com/group/comp...e/2f93038da344e25f?lnk=st&q=#2f93038da344e25f.
CAUTION/CAVEAT: No, I did not test this, nor read it in great detail. You
will need to do both, but if it doesn't work for you, search again, or
search on slightly more restricted search words.

You canNOT use Autonumber for this, because for various reasons, numbers may
be skipped in an Autonumber sequence... they are not guaranteed, even in
"normal use", to be monotonically increasing. A brief summary is at the
Access Web FAQ site at http://www.mvps.org/access/general/gen0025.htm --
that site, by the way, is packed chock-full of excellent advice and
information, compiled over time from "the best in the Access business".

Larry Linson
Microsoft Office Access MVP



Larry Linson
Microsoft Office Access MVP

 

[jackrobyn1]

I've altered the field names to suite my table and field names etc, but as
i'm a complete novice where exactly do i paste this formula??
NextNumber = Nz(DMax("[Ref_num]","Accidents","Year([Date_form_returned]) = "
&
Year(Date)),0) + 1

Thanks

This is not that hard to do. The question is whether you have a date field
in your table that will tell you the year. If you don't have that, you need
a field to at least keep track of the year the record belongs to. The year
and the incremental number should be in different fields.

The basic technique is to use a DMax to locate the current highest number
and add one to it. This can be done in the Form Current event for new
records or if you use a command button to start a new record, it can be done
there.

NextNumber = Nz(DMax("[RefNum]","MyTable","Year([RefDate]) = " &
Year(Date)),0) + 1

Now, there are two issues to address and your business rules will determine
how you handle them. First is the Multi User situation. If there are very
few users and they are not usually entering new records concurrently, it is
not a problem, but in a heavy data entry environment, it will be a problem.
That is that one user will get the number and begin entering data. Another
user will get the same number if she begins a new record before the first
user has completed entering the data.

One way around that is to immediately save the record as soon as the number
is assigned, but that creates the second issue. The scenerio is a user
begins a new record and the number is immediately assigned and she begins
entering the data. A second user begins a new record and the next sequential
number is assigned. Now the first user determines this record should not be
completed and deletes it. You now have a gap in the sequence.
--
Dave Hargis, Microsoft Access MVP

i need some sort of accurate/ consecutive number assigned to each record in
my table/form these are accident reference numbers .... so important, i don't
mind how but its got to be consecutive and start from a number i decide. i
need the format to be as follows:-
RRY/08/245
the RRY should not change, the 08 represents the current year, and the 245
is the part of the whole thing i need to change record to record eg
RRY/08/245 = record 1
RRY/08/246 = record 2
RRY/08/247 = record 3
etc etc

The table is called accidents, the field i want the unique number for is
called Ref_No
When it gets to the 1st of Jan 2009 i need the records to start with:-
RRY/09/001

i hope someone can help my solve this

thanks