incrementing autonumber - does it repeat?

[Buddy]

Hi everyone,

I have a very quick question. I have an Access 2003 database that
generates serial numbers for products using multiple fields (facility,
date, shift, line #, etc). Included in this serial number is a 4
digit incrementing autonumber called UnitNumber:
Field Size: Long Integer
New Values: Increment
Format: 0000
Indexed: Yes (Duplicates OK)

Duplicates are allowed because, on any given day, the factory will not
come close to producing more than 9999 products on the same shift and
/ or packing area. Therefore there will be no repeating serial
numbers.

My question is this... When the autonumber progresses to 9997, 9998,
and then 9999, what will the next number be? I'm hoping it will reset
itself to 0000 or 0001 and start all over again. It it doesn't, then
I have a rather big problem and would appreciate any help or advice.

The autonumber is up to about 9700 and will probably reach 9999 by the
end of the week. This is probably a rather simple question, but I've
never used an autonumber like this before!

Thanks in advance,
Buddy
(e-mail address removed)

 

[Tim Ferguson]

(e-mail address removed) (Buddy) wrote in

Duplicates are allowed because, on any given day, the factory will not
come close to producing more than 9999 products on the same shift and
/ or packing area. Therefore there will be no repeating serial
numbers.

Huh? You want a unique index if and only if there is a rule that the
numbers _must_ not repeat. If the number is not going to be used for
looking up records, then I wonder if it needs to be indexed at all. How
often do you SELECT .... WHERE SerialNumber = 205 for example?

My question is this... When the autonumber progresses to 9997, 9998,
and then 9999, what will the next number be? I'm hoping it will reset
itself to 0000 or 0001 and start all over again. It it doesn't, then
I have a rather big problem and would appreciate any help or advice.

The next number will be 10000 -- and a little testing in the immediate
window reveals this:

? format(10000, "0000")
10000

which is what you would expect if you read help on the format property.

If you really need to wrap the numbers round, then you have to forget about
using Autonumber and write your own allocation routine in VBA. There is
plenty of example code; try googling for "Access Custom Autonumbers".

On the other hand, autonumbers are primarily designed to provide a
(practically) everlasting supply of unique numbers, so that you may find
you don't need to mess about with the other fields as identifiers.

Hope that helps


Tim F

 

[tina]

an autonumber field is like the energizer bunny - it keeps going and going
and going....to 2 billion +. formatting the field to 0000 has no effect on
the data stored in the field, only its' appearance - until you reach 10,000.
you'll have to forget the autonumber field for inclusion in your serial
number, if you need it to be a consistent four digits. instead, generate
that part of the serial number using code.

hth

 

[Buddy]

Can anyone help me with some code to restrict this incrementing number
to four digits? ...so it repeats itself after getting to 9999? Or do
you know where I can find code that will help me? This 4 digit number
is a separate field in a table that creates a serial number based on
multiple fields (including the 4 digit number).

Thanks again

 

[Tim Ferguson]

(e-mail address removed) (Buddy) wrote in

Can anyone help me with some code to restrict this incrementing number
to four digits? ...so it repeats itself after getting to 9999? Or do
you know where I can find code that will help me?

Well, first there are some basic questions. For example, whether the
database is going to be used concurrently by multiple users; whether they
always use forms to generate the numbers (rather than file imports, Excel
sheets, etc. etc); whether gaps in the "sequence" matter or not. Some
specifics -- what _exactly_ happens after record 9999? Is there another
field to change or what? How do you tell the difference between this record
number 1045 and that record number 1045?

As I indicated in my earlier post, you have not given us much encouragement
that you have really thought out your needs. It is not enough to say you
have a quick question -- the impression is that it was the thought that
preceded it that was quick.

But maybe that was just my reading of the post...


B Wishes

Tim F

 

[tina]

yes, i can probably help. but i don't have a lot of time to spend doing a Q
and A back and forth. if your db is Acess97 or newer, and if you want me to
look at your db and see if i can set up the code, do the following:
make a copy of your db. do everything in the copy.
delete all proprietary data. if necessary, delete objects that don't impact
the issue (such as reports and forms that are in no way related to the "add
a record" process).
*compact the db*
zip to less than 1 MB. i have WinZip to unzip at this end.
copy the chain of posts into the email message, so i remember what the
situation is.
reference the newsgroup in the Subject line.
attach the zip file and email to me at ttacc_kill_all_spam_ess1 at
att_deletethis dot net, removing the underscores and words between them.
i'll set up the code, and send the db back so you can see how i did it.

 

[Buddy]

Tim;

I apologize for not giving more detailed information at first. Here's
some more info...
The database is used by multiple users, with a front end / back end.
The users will always use forms to generate the numbers (they are
using touch screens on a factory floor). Gaps in the sequence do not
necessarily matter, although it would be nice if they remained in
sequential order.

Each serial number is in this format:
FYYMMDDSLL-UUUU
where
F = facility
YY = 2 digit year
MM = 2 digit month
DD = 2 digit day
S = shift number
LL = line number (or packing area)
UUUU = unit number

Here's an example of a serial number that might be generated today:
1040415103-2710

The unit number is the 4 digit incrementing number I would like to
have repeat after 9999. Presently, the factory does not produce even
close to 9999 products each day in a single facility during a single
shift in one packing line, so there will be no repeating numbers.
This can change if production increases greatly. However, this format
should work for a long time.

The unit number is now 5 digits, which is tolerable because the serial
number labels are just long enough to hold an extra digit. But I will
need to change the field so it is only 4 digits before the this number
gets too large.

Please let me know if you need more detail. Thanks again for your
help - it is greatly appreciated.

 

[Tim Ferguson]

(e-mail address removed) (Buddy) wrote in

The unit number is the 4 digit incrementing number I would like to
have repeat after 9999. Presently, the factory does not produce even
close to 9999 products each day in a single facility during a single
shift in one packing line, so there will be no repeating numbers.

What I am wondering is whether you really want to go all the way up to 9999
and then back to 0000; or whether you want to restart with the beginning of
each new shift and each new day. The latter is probably the more common way
of doing it, and may make more sense to your users.

The unit number is now 5 digits, which is tolerable because the serial
number labels are just long enough to hold an extra digit.

Aha - a real-world limitation: doncha just hate them?


An autonumber is not going to work here: it'll keep going up to about 8
digit (2^31) and then wraps around to negatives. You will have to code your
own number, which is fine as long as:

All record creation is carried out using Access forms that you can code/
control, or at least by other processes when you can ensure that new
numbers are allocated properly;

In other words no access to table datasheets, SQL, excel etc etc.

If you have concurrent users, that is okay but makes the code significantly
more complex, because you have to avoid the chance of two users getting the
same number at the same time.

The simple code looks like this:

' construct the filter: note the correct delimiters
'
strWhere = "(FacilityCode = """ & GetCurrentFacility() & """) AND " & _
"(ProductDate = " & Format(GetActiveDate(), "\#yyyy\-mm\-dd\#") & _
") AND " & _
"(ShiftNumber = " & GetShiftNumber() & ") AND " & _
"(LineNumber = " & GetLineNumber() & ")"

' remove this once it's working
'
MsgBox strWhere

' now get the last number
'
varNext = DMax("SerialNumber", "MyTable", strWhere)

' if it's a new shift, start at one
If IsNull(varNext) Then
varNext = 1

Else
varNext = varNext + 1

End If

NewNumber = CInt(varNext)


The key is where to put this code. One option is in the BeforeUpdate event
of the form, checking first to make sure that there isn't already a
SerialNumber value (i.e. updating an old record). Another option is to
allocate the number as soon as the user requests a new record -- i.e before
opening the form. More details if you google on "Access Custom
Autonumbers", or check out Steve Lebans's website, or Dev Ashish's.

As I indicated above, this approach is not safe for multi-user
environments, unless you can guarantee that they will never be entering the
same facility and line number etc.

Hope that helps.


Best wishes


Tim F

 

[Buddy]

Tina;

I appreciate the offer greatly. I may take you up on it if I can't
find a way to figure it out soon. Thanks again!!

-----------

Tim;

I apologize for not giving more detailed information at first. Here's
some more info...
The database is used by multiple users, with a front end / back end.
The users will always use forms to generate the numbers (they are
using touch screens on a factory floor). Gaps in the sequence do not
necessarily matter, although it would be nice if they remained in
sequential order.

Each serial number is in this format:
FYYMMDDSLL-UUUU
where
F = facility
YY = 2 digit year
MM = 2 digit month
DD = 2 digit day
S = shift number
LL = line number (or packing area)
UUUU = unit number

Here's an example of a serial number that might be generated today:
1040415103-2710

The unit number is the 4 digit incrementing number I would like to
have repeat after 9999. Presently, the factory does not produce even
close to 9999 products each day in a single facility during a single
shift in one packing line, so there will be no repeating numbers.
This can change if production increases greatly. However, this format
should work for a long time.

The unit number is now 5 digits, which is tolerable because the serial
number labels are just long enough to hold an extra digit. But I will
need to change the field so it is only 4 digits before the this number
gets too large.

Please let me know if you need more detail. Thanks again for your
help - it is greatly appreciated.

 

[tina]

no problem. i had to generate a primary key from multiple values in a
similar manner for a database last year. reading over your details below,
i'd need to know if more than one person is entering records for the "same
facility, shift and line number or packing area" - at the same time.

good luck, and if you end up needing to accept my offer, i'll be here.