Unique Order Numbers

[Barry]

Greetings:
I would like to create unique order numbers with the format WO (WorkOrder to
identify the type of order), OrderDate (ie 020210), and a suffix number being
the sequential order number for the day (01,02,03 etc). So the resultant
Order number would be something like WO020210-01. Obvioiusly, on the next
day I would like the sequence numbers to start over at 01 and the date to be
correct. What tables need to be set up to hold the date and sequence numbers
and how do I check when to start the sequence over again? Your input is most
appreciated.
Thanks,
Barry

 

[Marshall Barton]

I would like to create unique order numbers with the format WO (WorkOrder to
identify the type of order), OrderDate (ie 020210), and a suffix number being
the sequential order number for the day (01,02,03 etc). So the resultant
Order number would be something like WO020210-01. Obvioiusly, on the next
day I would like the sequence numbers to start over at 01 and the date to be
correct. What tables need to be set up to hold the date and sequence numbers
and how do I check when to start the sequence over again?

Add three fielfs to the orders table.
OrderType
OrderDate
SeqNum

Assuming the OrderType and OrderDate have been specified,
the order form's BeforeUpdate event might look something
like:

Me.SeqNum = Nz(DMax("SeqNum", "Orders", _
"OrderType = 'WO' And OrderDate = " _
& Format(Me.OrderDate. "\#yyyy-m-d\#")), 0) + 1

Whenever you want to display your particular style order id
on a form or report, use a text box with an expression like:
=OrderType & Format(OrderDate, "mmddyy") & Format(SeqNum,
"\-00")

 

[Barry]

Marsh:
If I am understanding this, the Nz function is checking for the maximum
sequence number for a given date(OrderDate) and increments. If the date is
not found then the sequence number starts again at 1. A rather elegant
solution to a problem I thought would take considerably more work to solve.
Thanks much for the help.
Sincerely,
Barry

 

[BruceM via AccessMonster.com]

DMax locates the highest number for the day. If it is the first order for
the day there is no number, so DMax returns Null. Nz converts Null to a
value. In this case it converts Null to 0, which is the value just before
the closing parentheses. The incrementing is a simple math function of
adding 1 to either the highest number if there is one, or to 0 (the Nz value)
if it is the day's first order.

If this is a multi-user environment you need to take precautions against two
users entering an order at about the same time. Until a record (Record 1) is
saved, if another user starts a record it will have the same number as Record
1. The user who finishes first will have the number in their record. The
second person to finish will receive an error message if you have set up a
unique table index on the combination of fields that make up the order number
(or have performed form-level validation), or will produce a duplicate number
if you have not taken precautions against producing a duplicate number.

One way of guarding against duplicate numbers uses the form's Error event as
shown in the multi-user example in this sample database:
http://www.rogersaccesslibrary.com/...?TID=395&SID=d86f5d3e9a76z815e9c74d34c8ef334c


That link is all supposed to be on one line. Alternatively, go here (again,
all one line):
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=1
and look for AutonumberProblem.mdb

This is just one of several possible strategies, but I won't go into more
detail unless there is a need.

Marsh:
If I am understanding this, the Nz function is checking for the maximum
sequence number for a given date(OrderDate) and increments. If the date is
not found then the sequence number starts again at 1. A rather elegant
solution to a problem I thought would take considerably more work to solve.
Thanks much for the help.
Sincerely,
Barry
[quoted text clipped - 21 lines]

=OrderType & Format(OrderDate, "mmddyy") & Format(SeqNum,
"\-00")

 

[Barry]

Bruce:
Thanks for the more detailed explanation. I have taken a quick look at
Roger's example and appreciate the need to check for the error in a
multi-user environment. I will play around with this as I believe that it is
a fairly easy implementation and should provide the necessary result.
However, having said this, I would like to know about checking for duplicate
numbers and/or alowing the user to have a visual indication of the current
order number. Thanks again for the help. It is much appreciated.
Sincerely,
Barry

DMax locates the highest number for the day. If it is the first order for
the day there is no number, so DMax returns Null. Nz converts Null to a
value. In this case it converts Null to 0, which is the value just before
the closing parentheses. The incrementing is a simple math function of
adding 1 to either the highest number if there is one, or to 0 (the Nz value)
if it is the day's first order.

If this is a multi-user environment you need to take precautions against two
users entering an order at about the same time. Until a record (Record 1) is
saved, if another user starts a record it will have the same number as Record
1. The user who finishes first will have the number in their record. The
second person to finish will receive an error message if you have set up a
unique table index on the combination of fields that make up the order number
(or have performed form-level validation), or will produce a duplicate number
if you have not taken precautions against producing a duplicate number.

One way of guarding against duplicate numbers uses the form's Error event as
shown in the multi-user example in this sample database:
http://www.rogersaccesslibrary.com/...?TID=395&SID=d86f5d3e9a76z815e9c74d34c8ef334c


That link is all supposed to be on one line. Alternatively, go here (again,
all one line):
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=1
and look for AutonumberProblem.mdb

This is just one of several possible strategies, but I won't go into more
detail unless there is a need.

Marsh:
If I am understanding this, the Nz function is checking for the maximum
sequence number for a given date(OrderDate) and increments. If the date is
not found then the sequence number starts again at 1. A rather elegant
solution to a problem I thought would take considerably more work to solve.
Thanks much for the help.
Sincerely,
Barry

I would like to create unique order numbers with the format WO (WorkOrder to
identify the type of order), OrderDate (ie 020210), and a suffix number being

[quoted text clipped - 21 lines]

=OrderType & Format(OrderDate, "mmddyy") & Format(SeqNum,
"\-00")

--



.

 

[BruceM via AccessMonster.com]

At the end of Marshall's posting is a suggestion for an expression to be used
as the Control Source of an unbound text box. The same expression could be
used in the form's Record Source query with slightly different syntax (it's
all on one line):

FullNumber: OrderType & Format(OrderDate, "mmddyy") & Format(SeqNum,"\-00")

Just use FullNumber as the Control Source of a control or in an expression.

As for checking duplicates you could set a unique index in the table on the
combination of OrderType, OrderDate, and SeqNum (to use Marshall's field
names). Help has more about setting up a table index. This multi-field
index is what will cause the error if two records try to use the same number,
and then the form's Error event can take over.

Bruce:
Thanks for the more detailed explanation. I have taken a quick look at
Roger's example and appreciate the need to check for the error in a
multi-user environment. I will play around with this as I believe that it is
a fairly easy implementation and should provide the necessary result.
However, having said this, I would like to know about checking for duplicate
numbers and/or alowing the user to have a visual indication of the current
order number. Thanks again for the help. It is much appreciated.
Sincerely,
Barry

DMax locates the highest number for the day. If it is the first order for
the day there is no number, so DMax returns Null. Nz converts Null to a

[quoted text clipped - 38 lines]

 

[Marshall Barton]

If you use the form's BeforeUpdate event to do this, the
odds of two users getting the same number are vanishingly
small. The time between the BeforeUpdate event and when the
record is saved is a fraction of a millisecond so it's near
impossible for two users to save at same time.

You really only need to guard against two users getting the
same number if you must display the number while the
record's data is being entered. If that's a serious
requirement, then you also would have to save the partially
completed record as soon as the number is calculated. Since
that is usually a bad idea, it would be best to have a
separate table with a field for the order type, current date
and the last used number for the type and date. This
approach also has a messy complication that is a user
decides they don't want to enter save a record after
starting to enter it, the sequence numbers will have a gap
for the record that was not saved. If a gap is not
acceptable, you will have to deal with a whole bunch of
other complications.

IMO, this is just not worth it and I recommend using the
BeforeUpdate event and don't try to display the number until
after the record is saved.

 

[BruceM via AccessMonster.com]

I have been known to place the calculated number in an unbound text box so
the user can see it. In the Before Update event I generate the number again,
and compare it to the unbound text box value. If they are different I
display a message box advising the user. This introduces some lag time, as
the user needs to respond to the message box. This brief lag time could
probably be overcome by generating the number one more time after the message
box code, or maybe waiting until the After Update event to compare the values.

 

[Marshall Barton]

That's one way to do it, but IMO displaying the sequence
number before the record is saved is not worth the effort
and user interuption when you get a duplicate.

Each to their own though.

 

[BruceM via AccessMonster.com]

Actually, that's my opinion too, but my opinion doesn't always carry the day.

That's one way to do it, but IMO displaying the sequence
number before the record is saved is not worth the effort
and user interuption when you get a duplicate.

Each to their own though.

I have been known to place the calculated number in an unbound text box so
the user can see it. In the Before Update event I generate the number again,

[quoted text clipped - 28 lines]

 

[Barry]

Gentlemen:
Wow! This is spectacular. I truly appreciate the comments and the
professional nature in which this has been discussed. I have gleaned much
needed information that will most certainly help me to design and create a
much better product. Again, much thanks for all of your help.
Sincerely,
Barry

Actually, that's my opinion too, but my opinion doesn't always carry the day.

That's one way to do it, but IMO displaying the sequence
number before the record is saved is not worth the effort
and user interuption when you get a duplicate.

Each to their own though.

I have been known to place the calculated number in an unbound text box so
the user can see it. In the Before Update event I generate the number again,

[quoted text clipped - 28 lines]

BeforeUpdate event and don't try to display the number until
after the record is saved.