Creating Serial Numbers based on inserted qty

Bryan, your original post stated:

but what I am trying to accomplish is to
have the user fill in a few fields, then Quantity. Once Qty is added I
need
to have a list of serial numbers displayed in my subform.

and that is what my solution does.

It was not intended to serve as the completed code for your project, but to
show how to accomplish that specific task.
"The perils of requirements creep lurk at every project meeting".

UpRider

"Bryan" wrote in message
...
Looks cool when I open the form, however it has some problems I don't know
how to overcome.
1. It is using 3 tables just to create the serial number and then it
displays it in two fields. The amount of steps and code it took just to
get
the Serial Number is like going around the block just to get next door! I
had already created the serial number and combined it into one eight digit
number without having to manually enter the DateCode or ever see it or the
four digit serial.
2. I don't know how to make use of the serial Numbers once I have them as
they are isolated from the rest of my data and don't seem to tie to
anything.
3. Because the SNs are isolated, they just keep on sequencing irrespective
of the Product into one long list and I cannot separate them so that I
might
print labels or documents based on the group. If I enter a product with a
five piece order, then that is what I need to print. If next I enter
another
product with a three piece order, then I want to print those three. Plus
there are several other fields associated with that serial number that
will
be used and related at a later time.
I hope I am making sense here as I know you are trying hard to answer my
question.

"UpRider" wrote:

Bryan, download http://www.dbtc.org/zipmdb/serialdemo.zip
It's a form/subform setup that demos what you want to see your db do.

UpRider

"UpRider" wrote in message
...
OK. If we go with a Mainform/subform design, how do you want it to
work?
How many products can you put on the main form at one time (One I
hope)?
Then the mainform would be based on the PRODUCTS table or perhaps an
ORDERS table.
Then the subform would be based on the table below. (Both forms cannot
be
based on the same table)
When you create a new ORDER for the PRODUCT, the subform is empty until
the QTY is filled in.
Then the subform populates with the same number of records in QTY. The
serial numbers start with the next higher number existing for this
week,
or if none this week, last week.

What is the name of the below table?

UpRider

"Bryan" wrote in message
...
Yes to the first paragraph.
The last four digits continually increment, after 9999, then back to
0000.
The first four digits are a four digit DateCode, ie, 0733, the first
two
the
year and the second two the week. This is generated by VBA in a
module
and
sits in an invisible field on the form along with the four digit
SerialNumber. I then combine them:
SN = Format([DateCode], "0000") & Format([SerialNumber], "0000")
That means that today it could be 07330001 and tomorrow, 07340002 as
Monday
is the start of the new week. Serial Numbers then are never repeated
for
100
years! Serial numbers are issued almost daily for a variety of
Catalog
numbers with varying quantities.
Sample:
SN Product
07330001 12345
07330002 12345
07330003 67890
07330004 ZXCVB
07330005 ZXCVB
07330006 ZXCVB
07330007 ZXCVB
07340008 12345

At present the SerialNumber is an AutoNumber data type, but I plan to
change
that to incrementing, (Last record plus 1).
SerialNumber AutoNumber
DateCode Text
Product Text (ComboBox)
Description Text
Qty Number
SN Text
PL Text (ComboBox)
SN_Requestor Text (ComboBox)
PrintLabel Yes/No
PrintTestProc Yes/No

Thanks for your help!

Hey UpRider!
I have been wrestling with your solution for days and keep running into
roadblocks. I have added several fields to the subform as I need these for
tracking and for entering information later. They will need to be associated
with the created serial numbers, so the only way I knew to do that was to
place them in the subform. However, in order to not have to retype them with
each serial number, I have them being entered in frmMain in unbound fields
then being loaded into subfrmSerial by an After Update event. (I understand
the table, not form thing, I'm just explaining the visual!) I am not sure
quite how to explain it anymore, so can I ask you to take a look at it? I
have redone it from scratch several times and would pitch it if it wasn't
expected to be done. After a few dozen hours it is very small; a handful of
fields and the code you directed me to and my DateCode module. It'll be
short my lookup tables and such for simplicity. I think once you see it, it
will make sense. TIA

"UpRider" wrote:

Bryan, your original post stated:

but what I am trying to accomplish is to
have the user fill in a few fields, then Quantity. Once Qty is added I
need
to have a list of serial numbers displayed in my subform.

and that is what my solution does.

It was not intended to serve as the completed code for your project, but to
show how to accomplish that specific task.
"The perils of requirements creep lurk at every project meeting".

UpRider

"Bryan" wrote in message
...
Looks cool when I open the form, however it has some problems I don't know
how to overcome.
1. It is using 3 tables just to create the serial number and then it
displays it in two fields. The amount of steps and code it took just to
get
the Serial Number is like going around the block just to get next door! I
had already created the serial number and combined it into one eight digit
number without having to manually enter the DateCode or ever see it or the
four digit serial.
2. I don't know how to make use of the serial Numbers once I have them as
they are isolated from the rest of my data and don't seem to tie to
anything.
3. Because the SNs are isolated, they just keep on sequencing irrespective
of the Product into one long list and I cannot separate them so that I
might
print labels or documents based on the group. If I enter a product with a
five piece order, then that is what I need to print. If next I enter
another
product with a three piece order, then I want to print those three. Plus
there are several other fields associated with that serial number that
will
be used and related at a later time.
I hope I am making sense here as I know you are trying hard to answer my
question.

"UpRider" wrote:

Bryan, download http://www.dbtc.org/zipmdb/serialdemo.zip
It's a form/subform setup that demos what you want to see your db do.

UpRider

"UpRider" wrote in message
...
OK. If we go with a Mainform/subform design, how do you want it to
work?
How many products can you put on the main form at one time (One I
hope)?
Then the mainform would be based on the PRODUCTS table or perhaps an
ORDERS table.
Then the subform would be based on the table below. (Both forms cannot
be
based on the same table)
When you create a new ORDER for the PRODUCT, the subform is empty until
the QTY is filled in.
Then the subform populates with the same number of records in QTY. The
serial numbers start with the next higher number existing for this
week,
or if none this week, last week.

What is the name of the below table?

UpRider

"Bryan" wrote in message
...
Yes to the first paragraph.
The last four digits continually increment, after 9999, then back to
0000.
The first four digits are a four digit DateCode, ie, 0733, the first
two
the
year and the second two the week. This is generated by VBA in a
module
and
sits in an invisible field on the form along with the four digit
SerialNumber. I then combine them:
SN = Format([DateCode], "0000") & Format([SerialNumber], "0000")
That means that today it could be 07330001 and tomorrow, 07340002 as
Monday
is the start of the new week. Serial Numbers then are never repeated
for
100
years! Serial numbers are issued almost daily for a variety of
Catalog
numbers with varying quantities.
Sample:
SN Product
07330001 12345
07330002 12345
07330003 67890
07330004 ZXCVB
07330005 ZXCVB
07330006 ZXCVB
07330007 ZXCVB
07340008 12345

At present the SerialNumber is an AutoNumber data type, but I plan to
change
that to incrementing, (Last record plus 1).
SerialNumber AutoNumber
DateCode Text
Product Text (ComboBox)
Description Text
Qty Number
SN Text
PL Text (ComboBox)
SN_Requestor Text (ComboBox)
PrintLabel Yes/No
PrintTestProc Yes/No

Thanks for your help!